Forum Discussion
BIG BIG HELP!
Please note that this is not where I am stuck as I can build on the formula.
Where I need help is:
- If the hotel picked up from my lookup list if "Hotel 1", the data relating to my formula (which I can solve) should only be picked up from the line of "Hotel 1 " (or ROW 2)
Okay, without the nights, just rate will be
=INDEX($B$1:$C$5,MATCH($D8,$A$1:$A$5,0),2-($C8>1))
In you real file ranges are to be
- instead of $A1:$A$5 you entire hotels list, better with gap in rows if you add hotels (or just $A:$A but that affects the performance);
- instead of $B$1:$C$5 related range for prices.
So please find attached the actual data sheet.
I have built on the formula, however I am stuck with a conditional format that the data for the formula should only take data from the row corresponding to the hotel in question
That was for the first file, will update in a while for the second one
Updated on is
=(INDEX($B$1:$E$5,MATCH($F8,$A$1:$A$5,0),2-($C8>1))+INDEX($B$1:$E$5,MATCH($F8,$A$1:$A$5,0),4)*$D8)*$E8
as attached