How can I lookup when match has more than one value?
 

Hi,

I'm doing a Vlookup match function and it works, but what I want to do is
get the second match of a value.

eg:
..............1st.....2nd......3rd........4th..... ..
meat....good.....bad.....ok..........good....
chicken.OK......good......bad.......ok.....
fish......good....bad......bad.......ok......
meat....OK....good...good.......good....

B12=meat
B13= 2nd

my formula looks something like this:
VLOOKUP(B12,A1:AF10, MATCH(B13,B1:AF1,0)+1 ,FALSE)

what it does is get me the first row meat, 2nd column (bad) but what I want
is second row meat, 2nd column (good). how do I ammend the formula
accordingly.

Thanks!

How can I lookup when match has more than one value?
 

If the lookup value appears more than once in the table how do you determine
whether you want the first instance or the nth instance?

Are there duplicates for all of the lookup values? Are there only 2
instances of the lookup value?

--
Biff
Microsoft Excel MVP


"Paulynn" wrote in message
...
Hi,

I'm doing a Vlookup match function and it works, but what I want to do is
get the second match of a value.

eg:
.............1st.....2nd......3rd........4th...... .
meat....good.....bad.....ok..........good....
chicken.OK......good......bad.......ok.....
fish......good....bad......bad.......ok......
meat....OK....good...good.......good....

B12=meat
B13= 2nd

my formula looks something like this:
VLOOKUP(B12,A1:AF10, MATCH(B13,B1:AF1,0)+1 ,FALSE)

what it does is get me the first row meat, 2nd column (bad) but what I
want
is second row meat, 2nd column (good). how do I ammend the formula
accordingly.

Thanks!

How can I lookup when match has more than one value?
 

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

B12=meat
B13= 2nd

=INDEX(B1:E10,SMALL(IF(A1:A10=B12,ROW(A1:A10)),2), MATCH(B13,B1:AF1,0))

Or with 2 in a cell reference say B14

=INDEX(B1:E10,SMALL(IF(A1:A10=B12,ROW(A1:A10)),B14 ),MATCH(B13,B1:AF1,0))

--
Jacob


"Paulynn" wrote:

Hi,

I'm doing a Vlookup match function and it works, but what I want to do is
get the second match of a value.

eg:
.............1st.....2nd......3rd........4th...... .
meat....good.....bad.....ok..........good....
chicken.OK......good......bad.......ok.....
fish......good....bad......bad.......ok......
meat....OK....good...good.......good....

B12=meat
B13= 2nd

my formula looks something like this:
VLOOKUP(B12,A1:AF10, MATCH(B13,B1:AF1,0)+1 ,FALSE)

what it does is get me the first row meat, 2nd column (bad) but what I want
is second row meat, 2nd column (good). how do I ammend the formula
accordingly.

Thanks!