Forum Discussion
Help with Excel formula
=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.
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.
=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,""))))))
That's amazing!!
Thanks you so much for this, i really appreciate it
Many thanks
Keith