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...m%20lookup.xls

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Whnke" <[email protected]> wrote in message
news:[email protected]...
>
> 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?
>
>
> --
> Whnke
> ------------------------------------------------------------------------
> Whnke's Profile:
> http://www.excelforum.com/member.php...fo&userid=9677
> View this thread: http://www.excelforum.com/showthread...hreadid=521498
>

[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