Your row match criteria got a little out of control.
{=INDEX(Rates2!$C$2:$F$37,MATCH($D10&$A10 this has no matches ,Rates2!$C$2:$C$37&IF($A10>=Rates2!$E$2:$E$37,IF($A10<=Rates2!$F$2:$F$37,missing IF true,"")),1),MATCH("Timeframe",Rates2!$C$2:$F$2,))}
You should only looking for a row between the date ranges, and if True pulling back those rows.
From there, you're matching those rows to only the city.
Here you go:
=INDEX(Rates2!$C$2:$F$37,
MATCH($D10,IF($A10>=Rates2!$E$2:$E$37,IF($A10<=Rates2!$F$2:$F$37,Rates2!$C$2:$C$37,"")),0),
MATCH("Timeframe",Rates2!$C$2:$F$2,))
Bookmarks