VLookup Query

**senan_g** · 10-17-2011, 11:52 AM

Afternoon all,

I am trying to use vlookup to return two rows in the attached example. Basically when I select Area A and Team 1 in the Selection I want to return the two rows answers (Alice, Chris), in two separate cells (D11 & D12). At the moment I am only able to return the first row answer (Alice)

This is a simplified example of something bigger I want to use it for. If anyone can tell me how to do this or suggest another way of returning these two values, in two separate cells that would be great

Thanks

senan_g

**Andrew-R** · 10-17-2011, 11:55 AM

VLOOKUP only returns the first match in a range. There are ways to look up multiple values, but I can tell you now that they're going to be made hard by you using merged cells. Is there any possibility of getting rid of them?

**senan_g** · 10-17-2011, 12:06 PM

Hi Andrew

Yes there is some flexibility with the merged cells. I have laid it out in two different options in the updated attachment - would either of these ways work in terms of returning multiple rows.

Tnx

S

**Andrew-R** · 10-17-2011, 12:30 PM

Option 2 works the best, and in your example I've put the following formula in cell D11 downwards ... =IF((ROW()-10)>COUNTIF($J$27:$J$32,$D$8),"",INDEX($K$27:$M$32,SMALL(IF($J$27:$J$32=$D$8,ROW($J$27:$J$32)-26),ROW()-10),MATCH("Team "&$D$9,$K$26:$M$26,0)))

This must be entered as an array formula, so instead of simply pressing Enter to put the formula into the cell you have to use Ctrl-Shift-Enter

Given the rather long nature of the formula you might be best of giving me an example which mirrors the final layout of your sheet so that I can adapt it for you.

**senan_g** · 10-18-2011, 11:48 AM

Thanks Andrew

Actually I took your first advice and went with eliminating the merged rows. So instead of Area A I know have Area A Member 1 and Area Member 2, which allows me use the vlookup function.

Thanks for your advice - your approach was useful from a learning perspective anyway.

Cheers

senan_g