Google Sheets data validation using hidden lookup value?

Question

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)

Answer 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.