VLOOKUP IF AND

[ExcelNooberino]

Hi there folks!

So I've been trying to come up with a file that could help me to verify some data quickly that I've been doing and it's something like this: I need to validate if the batch of a specific product that was shipped respects the FEFO rule, I mean, first expired, first out. For that I need to extract some data from our system and cross info with the shipped products. The thing is, I need a formula that allows me to lookup the date of a specific product code and a specific batch because, a batch can be used in more than one code with different expiration dates. I don't know if I'm making myself really clear but basically I want it to return the correspondig expiration date of a specific product code & batch.

 

[auto.pilot]

Assuming your headers are in columns A:G, try the Inputs and Result (formula) below.

Product	Description	Boxes	Batch	Exp Date	Status	SSCC
300398​	Example A	65​	794563​	24-Apr-2022​	OK	384245365626001​
301711​	Example B	65​	794563​	26-Oct-2021​	OK	384245365626111​
Inputs
Product	300398​
Batch	794563​
Result	=SUMPRODUCT(--($A$2:$A$3=$B$7),--($D$2:$D$3=$B$8),$E$2:$E$3)

 

[ExcelNooberino]

I actually made it by using a little trick with the CONCATENATE formula, putting together both the product and batch codes and using VLOOKUP through that extra cell

 

[auto.pilot]

I'm glad you found something that works for you.

Thanks

 

[ExcelNooberino]

And thank you for your reply!