Lookup until...

New Contributor

I have a lookup value in a cell on worksheet 2 and would like to lookup all the records on worksheet 1 that match with the lookup value. The values from 2 columns found on worksheet 1 should be listed on worksheet 2. VLOOKUP only gives the first record found, but I would like to have a list of all the records with that certain value, so a kind of "loop lookup until key-value changes and return the requested values ". 

Is there a certain formula that does this?


Worksheet 1


Worksheet 2




4 Replies


This might work. Please update the sheet names and ranges to accommodate your workbook.




This is the result I get




For English speakers: #OVERLOOP! means #SPILL!

A spill error? Try clearing the neighboring cells so FILTER may return the results.