0

Let's say we have a Google Sheet used as a data validation "table"

********************************
ID     | Name
********************************
1      | Mike
2      | Sue
3      | Bob
4      | Mike

Is there a way to use this table as a data validation aid in other sheets so that the data validation dropdown will show the names, while actually storing the ID?

I am enclosing a spreadsheet for testing purposes:

  • Data Validation sheet, holds lookup values
  • Generic sheets is where the data validations should occur. Column A (hidden) should hold numeric IDs, depending on the value selected from the lookup table, while column B should hold the selected name....

Should handle duplicates in column B (names)

1

The drop-down list cell will always show the value you selected, but you can look up the ID in a nearby cell with something like this:

=iferror( vlookup(D2, { DropdownLists!B2:B, DropdownLists!A2:A }, 2, false) )

...where D2 is the drop-down list cell and DropdownLists!A2:B is the range where you have the IDs and names.

Alternatively, use an array formula like this:

=arrayformula( iferror( vlookup(B2:B, { 'Data Validation table'!B2:B, 'Data Validation table'!A2:A }, 2, false) ) )

This formula will fill the whole column automatically.

See your sample spreadsheet.

6
  • Thanks for helping. I have edited the OP and attached an editable sheet
    – Riccardo
    Jun 14 at 13:30
  • I think I got how your formula works, but what if you have duplicate names?
    – Riccardo
    Jun 14 at 13:41
  • If you the same name twice in the list, it will only appear once in the drop-down list. vlookup() will return the first ID where the name matches, starting from the top. Jun 14 at 14:09
  • The sample spreadsheet gives "Access denied." See The correct way to publicly share a Google test sheet. Jun 14 at 14:10
  • Done................
    – Riccardo
    Jun 14 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.