Jan 30 2021 01:25 PM - edited Jan 30 2021 01:27 PM
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 6B, 7B, or 8B) to appear in cell B2. Can't I do that with a nested If statement?
Jan 30 2021 01:53 PM
SolutionThere 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
Jan 30 2021 02:05 PM
Except LOOKUP() won't work, at least correctly, on unsorted names.
Jan 30 2021 04:39 PM
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) .
Jan 31 2021 07:21 AM
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.
Jan 31 2021 07:49 PM
Sorry I entered the formulas in a different order in my sheet. XLOOKUP is really the best choice in my opinion, but not everybody has access to it. Nevertheless adding FALSE or 0 or add the comma with a blank after it to VLOOKUP in the 4th argument allows the data to be unsorted using exact match:
=VLOOKUP(A4,A2:B5,2,FALSE)
=VLOOKUP(A4,A2:B5,2,0)
=VLOOKUP(A4,A2:B5,2,)
Jan 31 2021 08:18 PM
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.
Jan 30 2021 01:53 PM
SolutionThere 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