Vlookup for multiple lines of a same criteria with multiple values

[hasthigayan]

Hello everyone,

Please is there anyone that can help me,

I want to know ho to write a vlookup function to get multiple results for a single criteria.

Assume that , I have stock sheet with a Item code and a expiry date,

Other sheet has a movement of the same item code with different expiry dates,

So i want to know how to write a vlookup or any other way to check that , the movement has happened with expiry dates that are after the expiries to the stock available expiry or before the available expiary date.

THanks.

Hasthi

[#2]

@hasthigayan 

=SMALL(IF($D$4:$D$21=G4,$E$4:$E$21),1)>VLOOKUP(G4,$A$4:$B$8,2,FALSE)

You can try this formula in order to check if the earliest date from table2 is after the expiry date from table1. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

=LARGE(IF($D$4:$D$21=G12,$E$4:$E$21),1)<VLOOKUP(G12,$A$4:$B$8,2,FALSE)

You can try this formula in order to check if the latest date from table2 is before the expiry date from table1. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

Both formulas return TRUE of FALSE (WAHR or FALSCH) in the screenshot.

[#3]

I see you tagged Excel for web. I think you'll want to use FILTER instead of VLOOKUP for this task.