Bully beat down help, double drop down list, = price?

I have created a double drop down list and a working sheet, as per attachment.

The first column lists the “type of beat down” (confrontation fee, punch, kick, misc etch.)
The second column, depending on what I choose from the list in the first column will show the respective “style”, for example if I choose "Kick: from the first column the second cell would give me the option for “kick, low kick and high, or a kick knee combo” only.

The problem is that I would like the corresponding price to appear automatically based on the selections I make on both the drop down cells.ie. "Punch + single = $10.00"

What would be the best way for me to show the price and how would I go about doing it to get the output on the 3rd column? I do not want to write an “If” statement because there will be more types and styles I will add later to the list and I am concerned it will populate the equations with too much text.

Would a vlookup or hlookup would be easier? if so, will my current list in the first image suffice, or will I have to retype it for the h or v lookup to work? Does the v or h lookup work if the cells are merged?

Or is there a simpler solution to my problem?, other than outsourcing it to someone that actually knows what they are doing?


Is it possible to give a cell a numerical value but only show text on the drop down list? so that way i can just give a numerical value to the “text” punch and a another numerical value to the “single”, if i know where the numerical values are, i can just make an equation to add the cells together? would it work? if so, how?

Edit, removed images and uploaded calc file.attachment not working,.

VLOOKUP() is suitable for your purpose. You could use the more “general” MATCH() function to locate row. Note that VLOOKUP key is “atomic” (single field, no composite key), so you need unique “beatdown styles” across all types. (You would then match on the Style column, avoiding merged cells).

You can also use OFFSET() in the range specifier for validity to limit the second dropdown to only the applicable “styles” for a selected type of beat down, as well as for limiting the range for VLOOKUP(). If you do that, you can use the same “style” (e.g. Double) several places. In this case you will need to MATCH() in the column with merged cells. Merged cells are empty, except for the first one. This is OK with lookup functions when you specify “unsorted”.

Attach your file (instead of just pictures of it) if you want more detailed suggestions. Don’t expect helpers to recreate your dataset first, which currently is required for testing/fine tuning our suggestions.

i cant attach the ods file. but thats for the comment, im learning through it now.