Lookup Result Based on Tabulated Criteria

I am trying to to create a look up formula that takes the number of beds for a hotel and then looks up the relevant hotel licence cost as indicated in the attached spreadsheet.

For the banding, assume that as soon as the banding threshold is reach e.g. 100 then that next banding shall apply.

Can anyone help?
davidascottAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
In cell B5 put the following formula:

=VLOOKUP($D$5,$F$5:$G$14,2,1)

The VLOOKUP has four parameters:

Lookup Value
Lookup Range
Offset
Lookup Type

Setting LookupType to 1 gives an approximate match. This means that it returns the last value at which the lookup value is less than or equal to the value in the lookup column, assuming the lookup column is sorted in ascending order.

Hope that makes sense.

Thanks
Rob H
0
 
davidascottAuthor Commented:
Sorry - see workbook attached.
Workbook.xlsx
0
 
Rob HensonFinance AnalystCommented:
In your example, what happens for less than 100 beds?

If this could occur, you will need an extra row at the top of your table for the range 0 to 99.

Thanks
Rob H
0
 
davidascottAuthor Commented:
thanks rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.