Forum Discussion

Flynt Moreland's avatar
Flynt Moreland
Copper Contributor
Jul 26, 2018
Solved

Multi Column Drop Down List

I'm sure I'm over complicating this.  For simplicity, say I have a 2 Col x 5 Row Table with 1-5 in one column and A-E in the other.  I want to create a drop down list where the user selects an item in Column 2 (A-E) but the function returns the corresponding item in Column 1 (1-5). Easy right?

excel
Formulas and Functions

4 Replies

  • Fabio Baldini's avatar
    Fabio Baldini
    Iron Contributor
    Jul 26, 2018

    Hi Flint I created one post in Portuguese about Data Validation triple, 

     

    https://www.linkedin.com/pulse/valida%C3%A7%C3%A3o-de-dados-tripla-fabio-baldini/ 

     

    I hope I've helped

  • Ed Hansberry's avatar
    Ed Hansberry
    Bronze Contributor
    Jul 26, 2018

    I think the approach would be:

    1. User select dropdown on column 1 (1-5) using the Data|Data Validation tool forcing it to be a whole number between 1 and 5.
    2. Column 2 in this very simplistic example would be =choose(a1,"A","B","C","D","E") where a1 is the first cell in column 1, Then copy that down.

    Now, if your data is more complex or has more than 5 values, column 2 would be a table of some sort that would have 1-n in the first column and the second column would be the corresponding values, then use a VLOOKUP(A1,TableRange,2,false) formula. That could easly handle hundreds/thousands of options in column 2. https://support.office.com/en-us/article/video-vlookup-when-and-how-to-use-it-9a86157a-5542-4148-a536-724823014785.