Forum Discussion

HelpandImproveAP's avatar
HelpandImproveAP
Copper Contributor
Mar 23, 2022

Require assistance on an IF formula please

Hi,

 

Really hoping someone can help with my query -- I am looking to use a formula that is using one cell (A1) as data but cell A1 has a dropdown list with numerous other options... say Test 1, Test 2, Test 3

 

Test 1 = 10

Test 2 = 20 

Test 3 = 30 

 

I'm looking for a formula to produce results in say Cell A2 that will allow me to select (from the dropdown) in A1 either Test 1, Test 2, Test 3 and bring up a formula that if I choose "Test 1" then it will bring up the number 10, if i choose "Test 2" then it will bring up the number 20 and vice versa.

What is the formula for this please?

Formulas and Functions
Macros and VBA

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 23, 2022

    HelpandImproveAP 

    If you have just a few options, enter the following formula in A2:

     

    =LOOKUP(A1,{"Test 1","Test 2","Test 3"},{10,20,30})

     

    If you have a larger number of options, create a range with the values Test 1, Test 2 etc. in the first column, and the corresponding values 10, 20, etc. in the second column. Name this range List.

    You can then use

     

    =VLOOKUP(A1, List, 2, FALSE)

    • HelpandImproveAP's avatar
      HelpandImproveAP
      Copper Contributor
      Mar 23, 2022
      Hi,

      Thanks so much for your swift response.

      I've tried both but unfortunately it's not working...

      Can you breakdown what those formulas do please as I'm a bit confused as to the second formula? If I know this I could think of a workaround
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Mar 23, 2022

        HelpandImproveAP 

        Here is a picture. The formula =VLOOKUP(A1, List, 2, FALSE) takes the value of cell A1 (Test 12 in the screenshot), and looks it up in the first column of the List range. It then returns the corresponding value from the 2nd column of the List range. The 4th argument FALSE tells Excel to look for an exact match.