Forum Discussion
Excel Formula for showing correct price
Hi,
I'm new into excel and doing some digging I cant find an appropriate solution. I am currently building a registry worksheet where I will log everyone that booked.
These are my three columns;
1) Early-Bird Discount Applies?
2) Price Level
3) Days booked
4th Column ''Price'' - this is where I want to correct price to show based on the value of the previous three cells.
All three columns have a drop down list so the options are fixed. When customer books before a certain date then ''Early-Bird Discount Applies = Yes. Because they are a member, the price level cell would be chosen as ''Member'' and depending on how many days booked out of two, ''Days Booked'' Column will have a value of ''Friday Only''.
Based on those 3 values, I want the excel to display a correct value in price in the ''Total Price'' cell. Obviously, if values change, so will the Total Price.
Would someone be willing to help me to get this working, please? Is there a possibility?
I look very forward to your responses and help.
- Man Fai ChanIron Contributor
I have an idea. But I think it is not good.
Since the first three columns have fixed choices. So I will first concatenate the value in the first three columns. For short, I choose the first column (Y/N), second column (A/B/C) and third column (1/2/3/4/5). Then I can have a so-called "code".
I prepared a lookup table listing all possible "codes" and the corresponding price. In the price column, using VLOOKUP function to find the corresponding price.
The example here has only 30 choices (2*3*5). However, if the number of choices is getting larger, this method may not work.
Hope that it is helpful.