Nesting Multiple IF AND INDEX functions

=IF(AND(B2="Budget",C2=1),INDEX(Sheet1!E15:L16,2,2,),OR(IF(AND(B2="BUDGET",C2=2),INDEX(Sheet1!E15:L16,2,3,),FALSE)))

I realize this is probably a terrible attempt at this formula but I'm creating a spread sheet that needs to take into account multiple conditions before providing the value.

Here is a table of some of the conditions:

BUDGET 1 2 3 4 5 6 7
57.00 76.00 114.00 152.00 152.00 190.00 228.00
LUXURY 114.00 171.00 228.00 285.00 285.00 342.00 399.00

So what I need the formula to essentially do, is if I select Budget or Luxury from a dropdown Menu, and then Select the number of beds, let's say 1 bed for this example, then I need the formula to return the correct number.

So if I select Budget, 1 bed, it will give me the number 57. If I select Luxury and 1 bed, it gives me the number 114. The problem is, I need the formula to include ALL options. So whether I select Luxury and 5 beds, the formula will choose the number 285.

I'm open to other formula suggestions, but not to VBA.

Thanks for your help!

Hi,

variation of Glenn's solution:

=HLOOKUP(B8,A1:H3, IF(A8="Budget", 2, 3), 0) or in other words - it takes the column based on the size of the room and the row based on Budget/Luxury

Cheers,

Vitali