# Vlookup for multiple lines of a same criteria with multiple values

Occasional Visitor

# Vlookup for multiple lines of a same criteria with multiple values

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 Replies

# Re: Vlookup for multiple lines of a same criteria with multiple values

``=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.

# Re: Vlookup for multiple lines of a same criteria with multiple values

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