Simple Calculation Based on Text Value picked from a drop down list

New Contributor

Hi there, I hope someone can help me out with this as I'm fairly new to Excel. Ill try to explain as best I can considering I'm doing this from a mobile device; 


  • I have one column that has 20 specific names. 
  • I have one list that has 20 abbreviations (AAA,BBB,CCC etc....) of the names.
  • I have one column that has a numerical values relative to the above list and in the same row as with their associated name.
  • I have another column that also has numerical values in the same row.

I am trying to figure out the formula(s) that does the following: 

  • When a name is picked, the abbreviation appears in the second column.
  • The abbreviation triggers a calculation in another column completely based on the multiplication between the two numbers mentioned the section above. E.g. if Mr. Awkward Andrew was picked from a drop down list, AAA would automatically appear and a multiplication happens, J6*H6.
  • I would need this to happen no matter which of the 20 names are picked to perform different calculations on the list. 

I have spent hours trying to figure out how to use the IF(OR( commands with no luck. I wonder if anyone can help me with this formula for this please. Remember there are 20 different calculations that would need to happen depending on what the selection is. I'm pretty sure it would be a similar formula for both, the name lookup and the calculation. 

Thanks in advance. 

2 Replies
best response confirmed by Sergei Baklan (MVP)


See the simplified demo workbook attached to this reply.

Hans, Thank you so much for this. Really appreciate it!! That's exactly what I was trying to do.