VLOOKUP returning multiple answers from table

Attached shows two ways you can do this (besides just using a filter to show results).

They are colour coded blue for method 1 and green for method two. Anything else (ie. data and lookup value) are used in both.

Blue Method:
This finds all results and displays each one in a new table row by row.

Green Method:
This finds all results and puts them together in on cell.


Wasn't sure which way you were after.
Let me know if you need anything explaining, no point having a solution without the understanding or you won't be able to apply to your own work.

Hope this helps

Don't know what you are refering to with J6 as this is an empty cell on my sheet.

Use the same method on each sheet you have for every name. But you will need to have columns created on the main sheet to do the search for each sheet.

So for example the 1st method in my worksheet (which is in blue) has one column (A) which checks for matching results and then shows these in a new table.
If you have 3 sheets with three different searches (i.e 3 different lookup values) you will need to have 3 columns for each one so that you can get three sets of results. Then you create a table on each sheet and tailor the vlookup formula so that it looks at the correct column for the search.

Same goes for the green solution, creat a new set of formulas for each individual search required.


If you need more help to get the basic set up can you upload a sample workbook (replace sensitive data with false as long as it is in the same format) and your results typed out so I can see where you want things to be.

Just needed a new column on sheet1 which performs a new 'search' for the second sheet.
For every sheet you want with a different search create a new column in sheet1. Note inserting a column messes up the vlookups a bit on the other sheets so these needed modifying.

If you are going to set up several search columns in sheet1 do this first then set up the vlookups on each sheet after or you could end up leaving yourself more work to do overall.

I have also named the column headings on Sheet1 to Bill and Joe, when you work more on yours name these with something sensible so you can easily identify which column is which at a later date.

Hi Cyclewench

Use this for your example in post #1 and the data is in A1:B6.
In D1 & copy down:
=IFERROR(INDEX($B$1:$B$6,AGGREGATE(15,6,ROW($1:$6)/($A$1:$A$6=$C$7),ROW($A1))),"")

Adapt it for your other sheets.IE:
=IFERROR(INDEX(Sheet1!$B$1:$B$6,AGGREGATE(15,6,ROW(Sheet1!$1:$6)/(Sheet1!$A$1:$A$6=$C$7),ROW($A1))),"")

Hi Cyclewench

Thank you for the feed back.