VLOOKUP/INDEX/MATCH to return all values that match

Hi,
I have two sheets of data , sheet A and Sheet B.

Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.

I have filled out the first two rows of results that should be returned as an example.


One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.


Thanks in advance

Hi lijia00,
try it

Please Login or Register  to view this content.

Thank you that worked! I want to know, what does the offset in .end(xltoleft)(1,2) do? It looks like its offsetting 1 row and 2 columns?

.End(xlToLeft)(1, 2) is the same as .End(xlToLeft).Offset(0, 1)