Forum Discussion

Copper Contributor

Jan 30, 2021

Solved

Help w Drop Down Formula

Trying to set up a drop down menu in A2 that populates B2 with a value. When the user chooses one of the drop-down options in cell A2, I want the value in the cell adjacent to their choice (either...

test1.xlsx16 KB

Formulas and Functions

Shane_Devenshire
Jan 30, 2021
There are innumerable options, here are three using your data:
=LOOKUP(A2,A6:B8)
=VLOOKUP(A2,A6:B8,2)
=XLOOKUP(A2,A6:A8,B6:B8)

You might also work with INDEX/MATCH or INDEX/XMATCH

Shane Devenshire

Shane_Devenshire

Copper Contributor

Jan 30, 2021

There are innumerable options, here are three using your data:

=LOOKUP(A2,A6:B8)

=VLOOKUP(A2,A6:B8,2)

=XLOOKUP(A2,A6:A8,B6:B8)

You might also work with INDEX/MATCH or INDEX/XMATCH

Shane Devenshire

SergeiBaklan

Diamond Contributor

Jan 30, 2021

Shane_Devenshire

Except LOOKUP() won't work, at least correctly, on unsorted names.

Shane_Devenshire
Copper Contributor
Jan 31, 2021
Actually, the first and third formulas require the data be sorted. If not the first formula needs to be =VLOOKUP(A2,A6:B8,2,0) or =VLOOKUP(A2,A6:B8,2,FALSE) .
- SergeiBaklan
  Diamond Contributor
  Jan 31, 2021
  Shane_Devenshire
  
  No, sorting is not required for third formula, only first two.
  
  By the way, three main reasons never use VLOOKUP() that's fixed column number, left to right and approximate search by default.
  - Shane_Devenshire
    Copper Contributor
    Feb 01, 2021
    
    Regarding "fixed column number, left to right and approximate search by default."
    1. Column numbers don't need to be fixed.
    2. You can do right to left lookups, even with VLOOKUP
    3. Default approximate just means changing the 4th argument.
    
    XLOOKUP solves these issues, but again not everyone is using a version that has this function.