vlookup with 2 results the same

[wildecoyote]

I have a list of values where some are the same. How can I use vlookup and
have it give me the result instead of coming up with error?

eg:
i want to lookup 4788000 and have give me the results from list 3 ie 21,31
4332000 39 21
4788000 76 45
3990001 65 45
3420001 74 45
4560000 74 40
3192001 87 49
7410001 59 31
5472001 56 32
5700001 69 36
4788000 54 31

Can I also have it lookup as above but give me the result according the
number in column 2? ie give the result 21,31 or 31,21?

 

[Mike H]

Hi,

VLOOKUP doesn't return an error if there are 2 matches so to lookup and
return from column 2 use

=VLOOKUP(D1,A1:C10,2,FALSE)

or to return column 3

=VLOOKUP(D1,A1:C10,3,FALSE)

Where the value you are looking up is in D1

If there are 2 (or more) matches and you want them all try this ARRAY
formula. see below on how to enter an array formula. The formula returns the
first match from column2 drag down for the second match.

To return the value from column 3, change $B$1:$B$20 to $C$1:$C$20

=INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$D$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1)))

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

 

[ryguy7272]

Wait, aren't you looking for results of 45 and 31? With your data in 3
columns (A, B, and C), and the number 4788000 in cell E1, enter this formula
in cell F1:
=IF(ROWS(C$1:C1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($C$1:$C$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(C$1:C1))),"")

Enter it with Ctrl+Shift+Enter (all three together; not just Enter). Fill
down... Does that do what you want?

HTH,
Ryan---

 

[Ashish Mathur]

Hi,

You may refer to my article here
http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com