Help with Identifying Most Recent Date In Another Sheet Based on Specific Criteria

[ktran2024]

Hello There,



Hoping I can get some help on this issue. I have some data in two different sheets and want to do a lookup to pull in the invoice date from sheet 2 into sheet 1 (with certain criteria applied).

I want to pull in the invoice date from sheet 2 into sheet 1. However, there are multiple invoice dats for the same PO Name so I want to pull in the most recent invoice date that is less than the current order date.

I tried to do something using =MAX(INDEX((F1=A2:A11)*B2:B11,)), but not sure how to do it using a lookup to a different sheet. Is this possible? Any insight you can share with be much appreciated. Thank you!

Sheet 1:

PO Name	Order Date	Amount
JJ Order	1/15/24	$100
EJ Order	2/15/24	$200

Sheet 2:

PO Name	Invoice Date	Amount
JJ Order	1/1/24	$100
EJ Order	2/1/24	$200
JJ Order	12/1/23	$250
EJ Order	1/20/24	$400

 

[SunnyAlv]

Try this

You can get the latest date of each PO number that you are going to check

=INDEX($G$3:$G$6,MATCH(A3,$F$3:$F$6,1))

 

[awoohaw]

try this:

=MAX(FILTER(Sheet2!B10:B13,(A4=Sheet2!A10:A13)*(B4>Sheet2!B10:B13),""))

 

[ktran2024]

Thank you for your assistance!