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

No.

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

--
Biff
Microsoft Excel MVP


"dannyboy8" wrote in message
...
Is there a way to use vlookup to return the instance value beyond the 1st
instance in the array?

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.

"T. Valko" wrote:

No.

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

--
Biff
Microsoft Excel MVP


"dannyboy8" wrote in message
...
Is there a way to use vlookup to return the instance value beyond the 1st
instance in the array?

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?

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)

--
Biff
Microsoft Excel MVP


"dannyboy8" wrote in message
...
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.

"T. Valko" wrote:

No.

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

--
Biff
Microsoft Excel MVP


"dannyboy8" wrote in message
...
Is there a way to use vlookup to return the instance value beyond the
1st
instance in the array?

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?

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?

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?