vlookup

[dannyboy8]

Is there a way to use vlookup to return the instance value beyond the 1st
instance in the array?

 

[T. Valko]

No.

It can be done with other formulas but you need to give us the details.

 

[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.

 

[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

 

[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)

 

[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?

 

[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

 

[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))