Vlookup w/multiple codes

[Whnke]

I have a list of medical procedure codes that need to be cross
referenced. The simple Vlookup function works, but some of the
descriptors have multiple outputs i.e. Brown, 2340 Brown, 2475

I want to create an output on different columns for different codes
(i.e. first code first column, etc.)

Brown, 2340, 2475

So far I have (second column)
=IF(COUNTIF(B:B,B51)=2,=VLOOKUP(B51,$B$1:$C$3740,2,FALSE),"")

But that does not seem to output correctly and it does not lookup the
correct value. I need the second value for the same name to output and
then I would change the countif number for the multiple number of codes
to output... I'm confused are you?

 

[Peo Sjoblom]

it's not that easy to do especially given your layout

1. You need to use a combination of functions to get this,VLOOKUP will not
work

2. Always use a separate list of values that you want to lookup, i.e.
instead of using B51 which is part of the lookup table it's easier if you
extract a list of unique values from column B and then refer to that list
separately.

3. A combination of multiple formulas is the best way if you want to use
formulas and even better would be to use filters. Having said that here's an
example using one formula where the lookup value is in another list
separated from the lookup table

=IF(ROWS(B$1:B1)<=COUNTIF(First!$A$1:$A$20,$A$1),INDEX(First!$B$1:$B$20,SMALL(IF(First!$A$1:$A$20=$A$1,ROW(First!$A$1:$A$20)-ROW(First!$A$1)+1),ROWS(B$1:B1))),"")

example can be downloaded here

http://nwexcelsolutions.com/Download/Return more than one value from lookup.xls

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[vezerid]

Assuming the column containing Brown is 'Data'!A2:A20 and 2340, 2475
are in 'Data'!B2:B20, then, in your target sheet you can use the
following:

in A2:

='Data'!A2

In A3 the *array* formula (enter with Shift+Ctrl+Enter):

=IF(ISNUMBER(MATCH(1,--(COUNTIF(A$2:A2,Data!A$2:A$20)=0),0)),INDEX(Data!A$2:A$20,MATCH(1,--(COUNTIF(A$2:A2,Data!A$2:A$20)=0),0)),"")

This column will be copied down far enough until you get spaces. It
will give you the collection of unique entries found in the input.

In B2 *array* formula:

=INDEX(Data!$B$2:$B$20,MATCH(1,--(Data!$A$2:$A$20=A2),0))

Copy down. It will give the first occurrence of each code.

In C2 *Array* formula:

=IF(ISNUMBER(MATCH(1,--(Data!$A$2:$A$20=$A2)*(COUNTIF($B2:B2,Data!$B$2:$B$20)=0),0)),INDEX(Data!$B$2:$B$20,MATCH(1,--(Data!$A$2:$A$20=$A2)*(COUNTIF($B2:B2,Data!$B$2:$B$20)=0),0)),"")

Copy down and across as necessary.

HTH
Kostis Vezerides