Return value based on two criteria

[sasalele]

Desired Outcome: return specified value (Product Name) to 'Table1', from 'Table2' based on two criteria in 'Table1'; [@ASIN], [@Date] .

Note: Table1 contains Purchase Order History with fields such as: Date, Brand, Product, ASIN [Key Identifier], Purchase Order #, Qty, Cost, etc.

Note: Table2 Contains a master list of Product Data with fields such as: Brand, Product, ASIN [Key Identifier], UPC [Key Identifier], Version, Version Introduction Date, Version Retirement Date, Cost, dimensions, item specification, etc.

Criteria1: Match Table1[@ASIN] to Table2[ASIN]; exact match

Criteria2: Match Table1[@Date] to Table2[Version Introduction Date] if Table1[@Date] >=Table2[Version Introduction Date]

Note: Criteria2 is to ensure the correct version of the product's data is being retrieved based on the Purchase Order Date.

My current approach has been through the following formula:
=INDEX(Table2[Product Name],MATCH(Table1[@ASIN]&Table1[@Date],Table2[ASIN]&Table2[Version Introduction Date],0))

This formula works if Table1[@Date] = Table2[Version Introduction Date], but I need it to work for if Table1[@Date] is greater than Table2[Version Introduction Date]. For example, the Version introduction date for ProductA is 12/16/2019, but the Purchase Order date is 12/24/2019.

 

[sasalele]

Figured it out...

In case someone encounters a similar situation, here is what I did:

{=INDEX(Table2[Product Name],MATCH(1,((Table2[ASIN]=Table1[@ASIN])*(Table2[Version Introduction Date]<=Table[@Date])*(Table2[Version Retirement Date]>=Table[@Date])),0))}