index match use

[squirrelzan]

I have two spreadsheets where one has prod number, receiving date and quantity. Second one has prod number, inventory level date and inventory level quantity,
How do I use index/match where I will look at sheet #1 prod number and receiving date and bring back the inventory level date closest to the receiving date and inventory level quantity? Thank you

 

[jayalaxmi]

I have two spreadsheets where one has prod number, receiving date and quantity. Second one has prod number, inventory level date and inventory level quantity,
How do I use index/match where I will look at sheet #1 prod number and receiving date and bring back the inventory level date closest to the receiving date and inventory level quantity? Thank you

Hi

Is this something that you are looking for:

=SUMPRODUCT((A2=Sheet2!$A$2:$A$9)*(C2=Sheet2!$B$2:$B$9)*(Sheet2!$C$2:$C$9))

Note: try to keep the date formats same on both the sheets in order the formula to work.

Regards
Jaya

 

[squirrelzan]

Hi

Is this something that you are looking for:

=SUMPRODUCT((A2=Sheet2!$A$2:$A$9)*(C2=Sheet2!$B$2:$B$9)*(Sheet2!$C$2:$C$9))

Note: try to keep the date formats same on both the sheets in order the formula to work.

Regards
Jaya

Almost close. What if I need a date closest to the date in sheet 1? So in the example, anything closest to 05-30-2018.
Thank you