Is Vlookup and INDEX MATCH the best solution

[Tim_D]

Hi all. I have a data table where I need to pull the product cost based on the closest "cost date" to the actual "Order Date".
The cost data spreadsheet is set up like the image below and I cannot find a formula that works with the data set up like this.

I am only able to get this work if I convert all the data into columns. The formula works as expected with the data in this column format, but it will be too labor intensive to have to update the converted data every time the cost data is updated by someone else. I cannot figure out how to make this formula work for the data setup in the top image.

{=VLOOKUP(F2,INDEX(B:C,MATCH(E2,A:A,0),0):INDEX(B:C,MATCH(E2,A:A,0)+COUNTIF(A:A,E2)-1,0),2,TRUE)}

Any help would be greatly appreciated.

Timothy

 

[Fluff]

How about

=INDEX(B29:H30,XMATCH(E2,A29:A30),XMATCH(E2,B28:H28,-1))

 

[Tim_D]

How about

=INDEX(B29:H30,XMATCH(E2,A29:A30),XMATCH(E2,B28:H28,-1))

Awesome, worked like a charm!!!! Thank you so much for the quick reply.

I only had to change the on small thing from (E2,B28:H28,-1) to (F2,B28:H28,-1) to reference the date cell, but other than that it worked perfectly.

 

[Fluff]

You're welcome & thanks for the feedback.