VLOOK problem with multiple values in table array

Hi

i was wondering if you could help me with a problem i have.

I am trying to return multiple results against a value, when the table array has multiple entrys. if you look at the attached sheet you will see what im trying to explain.

Sheet 2 cell B6 holds the first formula.

You can see that the Lookup_value has THREE entrys in the Table_array. I am looking to return ALL THREE of the entry's in separate cells. But in the formulas in cells B7 & B8 in sheet 2, you can see that the first entry is always being returned. is there a way that i can alter the formulas so that they return the 2nd and 3rd entrys accordingly?

Any help greatly appreciated.

VLOOK example.xlsx

You need to give each record a unique reference. One way of doing this is to insert a new column C (which you can hide later on, so the sheet will look the same), and then you can use this formula in C2:

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

Copy this down beyond your data - the hyphens will help you see where you have copied it to. Then you can change your lookup formula in B6 of Sheet2 to this:

=VLOOKUP(A$2&"_"&ROWS($1:1),Sheet1!$C$2:$D$4,2,FALSE)

then copy down.

Hope this helps.

Pete

Many thanks Fotis

This has worked perfectly.