Round, Roundup, Rounddown or something else????

[PERE]

Hi,

Refer attached. My goal here is to have C7 return the Occupied Room Rate in column J based on the % occupancy determined in C5 which currently matches to a % in column E. The formula currently works if C5 is equal to the the relevant % in E however if it does not match, it returns #N/A. How can i make it 'closest to' the percentages in column E?

Thanks in advance

PERE

[Jonmo1]

It depends on which answer you want...

Say C5 = 70%

Your table doesn't have70% exactly.
It has 68.8 and 75
Which one should be considered the match, 68.8 or 75 ?


Removing the 0 in the Match function will make it always return the value that is below the lookup value.
Basically, it finds the largest # that is less than or equal to the lookup value.

=INDEX($E$3:$J$11,MATCH($C$5,E3:E11),6)

[PERE]

Thanks! I will run with your suggestion and increase the increments of vacancy in column E if I need to refine

[Jonmo1]

Great, glad to help.