D
dannyboy8
Is there a way to use vlookup to return the instance value beyond the 1st
instance in the array?
instance in the array?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
T. Valko
No.
It can be done with other formulas but you need to give us the details.
It can be done with other formulas but you need to give us the details.
D
dannyboy8
OK, in column A there is 1 particular identifier that is used 2 times (%,VGP
is the identifier) for different sources of income in column B, and I need to
return only the second instance of "%,VGP". There are no other unique
identifiers to work with in this case.
is the identifier) for different sources of income in column B, and I need to
return only the second instance of "%,VGP". There are no other unique
identifiers to work with in this case.
M
Mike H
Hi,
In this sample formula you are looking for a value (E1) in column A and
returning the corresponding value in column B. F1 should hold the occurrence
so a 3 in F1 returns the 3rd occurrence
=INDEX(B1:B13,LARGE((A1:A13=E1)*ROW(A1:A13),COUNTIF(A1:A13,E1)+1-F1))
This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
Mike
In this sample formula you are looking for a value (E1) in column A and
returning the corresponding value in column B. F1 should hold the occurrence
so a 3 in F1 returns the 3rd occurrence
=INDEX(B1:B13,LARGE((A1:A13=E1)*ROW(A1:A13),COUNTIF(A1:A13,E1)+1-F1))
This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
Mike
T
T. Valko
Ok, so you want to return the value from column B that corresponds to the
2nd instance of "%,VGP" in column A *and* there are only 2 instances of
"%,VGP" in column A...
=LOOKUP(2,1/(A2:A100="%,VGP"),B2:B100)
2nd instance of "%,VGP" in column A *and* there are only 2 instances of
"%,VGP" in column A...
=LOOKUP(2,1/(A2:A100="%,VGP"),B2:B100)
D
dannyboy8
Sorry Mike, I am just a bit confused on the F1, can you use any cell
reference here? Do you literally enter in the #3 in F1 in this case to return
the 3rd occurrence?
reference here? Do you literally enter in the #3 in F1 in this case to return
the 3rd occurrence?
M
Mike H
Hi,
E1 & f1 are just 2 cell references I chose. Array enter the formula in
whatever cell you want and change E1 (2 instances) & F1 in the formula to
which ever cell references you want. Then E1 or its equivalent are the lookup
value and F1 or equivalent are the occurence so:
%,VGP in E1
2 in F1
Finds the second occurrence of %,VGP in column A and returns the
corresponding value from column B
Mike
E1 & f1 are just 2 cell references I chose. Array enter the formula in
whatever cell you want and change E1 (2 instances) & F1 in the formula to
which ever cell references you want. Then E1 or its equivalent are the lookup
value and F1 or equivalent are the occurence so:
%,VGP in E1
2 in F1
Finds the second occurrence of %,VGP in column A and returns the
corresponding value from column B
Mike
D
dannyboy8
Mike, thought I had this, but I am gettnig a #NUM return in the cell for the
below formula, if I am puling the info from a completely different worksheet
not located
in the same file, does that make a difference?
=INDEX('[G W Pd 12 P&L.xls]P&L'!$P:$P,LARGE(('[G W Pd 12
P&L.xls]P&L'!$B1:$B1000="rental of equipment")*ROW('[G W Pd 12
P&L.xls]P&L'!$B1:$B1000),COUNTIF('[G W Pd 12 P&L.xls]P&L'!$B1:$B1000,"rental
of equipment")+1-$E$47))
below formula, if I am puling the info from a completely different worksheet
not located
in the same file, does that make a difference?
=INDEX('[G W Pd 12 P&L.xls]P&L'!$P:$P,LARGE(('[G W Pd 12
P&L.xls]P&L'!$B1:$B1000="rental of equipment")*ROW('[G W Pd 12
P&L.xls]P&L'!$B1:$B1000),COUNTIF('[G W Pd 12 P&L.xls]P&L'!$B1:$B1000,"rental
of equipment")+1-$E$47))