Forum Discussion

Nate_04's avatar
Nate_04
Copper Contributor
Apr 16, 2019
Solved

Excel VLOOKUP with Named Ranges

This is my formula:   =VLOOKUP(A1,Group1,3,FALSE)   If I write it out like this it works prefectly. But I want a dropdown from another cell where I can choose a group (Group1, Group2, Group3 etc)...
excel
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    Apr 16, 2019
    You can include CHOOSE in your VLOOKUP formula this way:
    =VLOOKUP(A1,
    CHOOSE(MATCH(B1,{Group1,Group2,Group3},1),
    Group1,Group2,Group3),
    3,0)
    The foregoing formula is non-volatile. An alternative would be the volatile INDIRECT, as follows:
    =VLOOKUP(A1,
    INDIRECT(B1),
    3,0)
    The choice is yours.
Twifoo's avatar
Twifoo
Silver Contributor
Apr 16, 2019
You can include CHOOSE in your VLOOKUP formula this way:
=VLOOKUP(A1,
CHOOSE(MATCH(B1,{Group1,Group2,Group3},1),
Group1,Group2,Group3),
3,0)
The foregoing formula is non-volatile. An alternative would be the volatile INDIRECT, as follows:
=VLOOKUP(A1,
INDIRECT(B1),
3,0)
The choice is yours.

Resources