VLOOKUP to return multiple cells of same lookup value

**gerardog92** · 04-26-2012, 12:16 PM

Hello,

I am new to this forum and hopefully you can help me.

I am trying to use some Vlookup formulas in the tab "called vlookup claims", the reference values are the member IDs. I need the vlook to look in the tab "called claims" and return the claim number in each one of the colums. I know I have no more than 5 claims per member id. so, I want each one of those claims in separate colums.

Thanks!!

**dilipandey** · 04-26-2012, 02:11 PM

Hi gerardog92,

You can use the below array formula to get the desired result:-

{=IF(ISERROR(INDEX(claims!$A$1:$C$1204,SMALL(IF(claims!$A$2:$A$1204=$A2,ROW(claims!$A$2:$A$1204),""),COLUMN(A$1)),2)),"",INDEX(claims!$A$1:$C$1204,SMALL(IF(claims!$A$2:$A$1204=$A2,ROW(claims!$A$2:$A$1204),""),COLUMN(A$1)),2))}

sample file BCS 2011 vlookup.zip BCS 2011 vlookup.zip

regards,
DILIPandey

<click on below 'star' if this helps>

**Cutter** · 04-26-2012, 02:17 PM

Non array alternative - assumes your list on the Claims sheet is sorted (as per your example)

In B2 and copied down: =INDEX(claims!$B:$B,MATCH($A2,claims!$A:$A,0))

and in C2 copied over and down: =IF(COUNTIF(claims!$A:$A,$A2)>COLUMN(A1),INDEX(claims!$B:$B,MATCH($A2,claims!$A:$A,0)+COLUMN(A1)),"")

**Marcol** · 04-26-2012, 02:23 PM

Try this workbook, data need not be sorted.

1/. Change your formula in Sheet "claims" C2 to ...

Please Login or Register  to view this content.

Drag/Fill down as required.

2/. Create a dynamic Named Range "ClaimsData"
Refers to:=

Please Login or Register  to view this content.

3/. Put this in Sheet"vlookup claims" A2

Please Login or Register  to view this content.

Drag Down until a blank cell is returned, then a bit more.
This will give you a list of Unique IDs

4/. In B2, this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter.
Drag Across and down as required

**gerardog92** · 04-26-2012, 03:01 PM

Thank you this is great!!

**gerardog92** · 04-26-2012, 03:13 PM

Hola!

Thank you this is awesome!! this realy works

**cmgilreath** · 01-24-2014, 03:35 AM

Nice.
I have a similar problem, but in reverse. To keep it simple, imagine I have your "vlookup claims" sheet as my source data. In other words, multiple columns of claims numbers and a single MENBID column at the left, exactly what you show in your spreadsheet example. I want to extract the equivalent of your "claims" sheet as my output. I don't need the "number of claims" column, just a single column of MEMBID and a single column of CLAIMNO exactly as shown in your example.
Any ideas?
Thanks.