I have a drop down menu with 10 different text options. These options represent different statuses on a project. (i.e.: Not started, Information Gathering, Speaking with Contacts, Development...)

I have, in another sheet, numerical percentages associated with these options. For example, Not Started would be 0%, Information Gathering would be 10%, etc. I would then take the numerical value and pass all them to another sheet where I represent this data in a graph.

How would I be able to query a specific cell against a set of option and populate another cell with a value based off the option selected. I believe it would be using multiple if statements, but I'm not sure.

Here's a diagram for what I'm looking to do:


  • This would be a lot easier to follow if you could provide sample data and expected output – cybernetic.nomad May 17 '19 at 21:24
  • @cybernetic.nomad You are correct. I've just added a diagram with sample data and expected output. – user1037812 May 17 '19 at 21:31
  • Looks like you need a VLOOKUP, but your sample result doesn't seem to match the reference table (ex: stage A has 0% result vs 25% in the reference table) – cybernetic.nomad May 17 '19 at 21:36
  • @cybernetic.nomad That's an error on my part. I'll look into VLOOKUP – user1037812 May 17 '19 at 21:37


VLOOKUP function is your friend in this case. Alternative do exist that will perform the same job such as LOOKUP, and a combination of INDEX and MATCH. Your datatable setup is ideally setup for VLOOKUP.

VLOOKUP takes a reference value and then proceeds to search for it in the left most column of a defined range. When it find its match or nearest match depending on search type, it will return the value from the corresponding row a specified number of columns to the right. The Syntax for VLOOKUP is:

=VLOOKUP(What to search for, Range to look in,
         what range column number to return, search type)

So based on the layout of the information in the picture below, you could use the following formula. Adjust the references to suit your data. Note the absolute reference for the range to look in. This is so that it will not change as the formula is copied.


Now the formula could be tweaked to display "" when no value has been picked from the drop down box.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.