Return value from another cell when a cell has text from a dropdown

Question

I have a list of items in a text dropdown lists and when selected I would like it to return a number from another cell (esstentially rates for a service) I've managed to do this for 1 item in the dropdown, but I need to add all of them together from different cells e.g. F78 contains 6 text options, G78 contains 3, H78 contains 4, I 78 contains 2. Each one of these text options refers back to a rates cell to pull the number from. I'm trying to total all of these to get a number. Thoughts anyone I used =if(isnumber(search("Small 30 seconds",F78)),R77,"")

I used =if(isnumber(search("Small 30 seconds",F78)),R77,"")

How do I combine this with "Small 60 seconds refering to S77, Small 90 seconds refering to R77, Large 30 seconds R78 And then the second part is adding additional output numbers from the 2nd, 3rd and 4th dropdown lists

Answer 1

I am not sure whether I understood your question clearly, but I think you could try this formulae:

=INDIRECT(ADDRESS(MATCH(VLOOKUP(D2,A1:B,2,0),B1:B,0),2,4))

Explanation:

• D2 is the cell with your dropdown
• A1:B - is range with the list of values and their "names"
• B1:B is the list of result values

It returns a value according to the dropdown like this:

Just put your references into the formulae so it works fine.

Also, feel free to ask questions if there is something confusing in the formulae I suggested! :)