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

Anne_Fastre_0-1658259756020.png

Worksheet 2

Anne_Fastre_1-1658259795683.png

 

 

4 Replies

@Anne_Fastre 

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

 

=FILTER(Sheet1!I2:J1000,Sheet1!A2:A1000=C1)

@Patrick2788 

This is the result I get

Anne_Fastre_0-1658263544217.png

 

@Anne_Fastre 

For English speakers: #OVERLOOP! means #SPILL!

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