Lookup with multiple occurances

[Loneoak2]

HI,

I am working with a workbook (2 worksheets - Data (WS1) and Consolidation (WS2)) and am trying to consolidate dome of the data based on 1 parameter from a specified cell that contains multiple parameters. When I use Vlookup, it always gives the first row.

I want to search column A in WS1 for the *Text* and have it return the adjacent cell (column B WS1) to my designated cell in WS2.

Problem I have - in WS2 there are only necessary 15 rows to be displayed yet there is ~200 rows to search, I only want to return , to WS2, the 15 occurrences from the 200 rows on WS1.

Does this make sense? I hope so...

Please help,

Kenn

 

[Sinon]

My understanding is that you have multiple matches and want to return all of them. You can do that, in consecutive cells in a column by applying any of the 2 formulas described in the links below:
How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP | eImagine Technology Group
Match two criteria and return multiple rows in excel | Get Digital Help - Microsoft Excel resource

Very probably, you will be able to find some more variations of this online.

 

[Loneoak2]

Hi and thank you for the reply, much appreciated.

the first link is the one that is closest to my issue, the second, I could not use...

so my question goes further:

Based on the table used in the example from eImagine, what if the name 'Mickey' was also associated to Dog and Duck and the Animal cells were formatted to contain multiple values as "Mouse; Dog; Duck" - I still want to be able to return the value for the single selection of Mouse.