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

"Mike H" wrote:

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" wrote:

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" wrote:

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),COUNTI F(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
"dannyboy8" wrote:

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