Vlookup for multiple criteria using multiple table arrays

I have a Vlookup that works great - But I am trying to implement some changes to auto select or formula that knows which array to use, as opposed to using the same range since it changes with date.

Attached is a sample data file with the Headers: Pricing Date, Year, Benchmark, YLD, SPRD

The lookup is pulling the benchmark number from the "Selected Bench" Tab. Is there a way to adjust the formula to pull from a different array depending on whatever the pricing date may be?

Any help would be appreciated, Thanks

How about in C2:

=IF($B2="","",(VLOOKUP($B2,OFFSET('Selected Bench'!$B$4:$C$58,0,MATCH(INT($A2),'Selected Bench'!$A$2:$S$2,0)-1),2,0)))

Note OFFSET is a volatile function which recalculates every time a change is made in the worksheet, this can cause it to run slowly if you have a lot of them.

I guess it's probably fine if your example workbook is similar in size to what you will be using; it could be a problem if you have a lot of data.

If the "Selected Bench" sheet will always have the same years for each date, you could use:

=IF($B2="","",(VLOOKUP($B2,'Selected Bench'!$B$4:$S$58,MATCH(INT($A2),'Selected Bench'!$A$2:$S$2,0)+1,0)))

The Years will always be the same - The only change will be to the blue columns weekly to display the new numbers. So this formula should work then