Help with Excel formula

[Vid142857]

Hi Folks

I'm trying to set up excel formula for an invoice. In column A i have a list of halls we rent (big, small, both) and each have their own rate during the week and a different rate for the weekend. 

What I need is…

If hall is “Big” and “Date” is weekday then “Rate” is 25

If hall is “Big” and “Date” is weekend then “Rate” is 30

If hall is “Small” and “Date” is weekday then “Rate” is 15

If hall is “Small” and “Date” is weekend then “Rate” is 20

If hall is “Both” and “Date” is weekday then “Rate” is 40

If hall is “Both"and “Date” is weekend then “Rate” is 75

…….in a single formula?!

Basically I want the info in column A to dictate the Rate in column E which depends on the Date (weekday/weekend) in column B. 

Any help would be greatly appreciated 

Many thanks

Keith

[#2]

@Vid142857 

=INDEX($I$3:$I$8,MATCH(A1&B1,$G$3:$G$8&$H$3:$H$8,0))

You can set up a reference table and refer to it with INDEX and MATCH. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

 

[#3]

@OliverScheurich 

Hi Quadruple

Thanks for getting back to me and taking the time to create that formula.

Unfortunately, it's not quite what i need. I didn't explain the issue properly, sorry. We use dates on the invoice, so i'd need something like... if column A is x, y or z AND the date showing in column B falls on a weekend or weekday then the rate in column E is 15 or 20.

The tables below have formula

 =IF(WEEKDAY(B2,2)>5,30,25)......=IF(WEEKDAY(B20,2)>5,20,15).....=IF(WEEKDAY(B36,2)>5,75,40)

and it works ok but it's a different formula for each hall and the hall column is not included in the formula either. 

So i want to combine these tables/formula into one formula. The Hall column and the Date column would dictate the price in the Rate column. 

 

[#4]

@Vid142857 

=INDEX($I$5:$O$7,MATCH(A2,$H$5:$H$7,0),MATCH(WEEKDAY(B2,2),$I$4:$O$4,0))

You can set up a reference table as shown in the screenshot and apply the above formula.

An alternative could be a nested IF formula which is in cell F2 and filled down but it's more complicated and more difficult to maintain.

=IF(AND(WEEKDAY(B2,2)>5,A2="Big"),30,IF(AND(WEEKDAY(B2,2)<=5,A2="Big"),25,IF(AND(WEEKDAY(B2,2)>5,A2="Small"),20,IF(AND(WEEKDAY(B2,2)<=5,A2="Small"),15,IF(AND(WEEKDAY(B2,2)>5,A2="Both"),75,IF(AND(WEEKDAY(B2,2)<=5,A2="Both"),40,""))))))
 

[#5]

@OliverScheurich 

That's amazing!!

Thanks you so much for this, i really appreciate it

Many thanks

Keith