Create a dropdown list of options from VLOOKUP

Question

What I'd like to do is get a dropdown list of options from a VLOOKUP type function where I can select the value for the next lookup.

Example

Here the data I'm using for reference. Notice "AAA" appears twice.

REF SHEET:
-------------------
ACC | UNQ# |    AMT
--- | ---- | ------
AAA | 1000 | 100.00
BBB | 1001 | 100.00
AAA | 1002 | 200.00
CCC | 1003 | 100.00

Now when I type "AAA" in the ACC column I want the NUM# column to get a dropdown list of the array data (1000, 1002) returned where for example a VLOOKUP would return only one value. Once the NUM# value is selected by the user I can use a standard VLOOKUP function to populate the final AMT column.

USER SHEET:
----------------------------
ACC | DATA | NUM#   |    AMT
--- | ---- | ------ | ------
AAA | wxyz | 1000 ▼ |
    |      | 1002   |

Answer 1

Why not have a column that concatenates ACC and UNQ#, like:-

AAA-1000
AAA-1002

Then when AAA was typed in you would get the two options, then you can use SPLIT to get the UNQ# to do the second vlookup.

Answer 2

Why not use data validation with a dropdown? Make the user select from a range, your UNQ# column. If you want it to display only if something is entered in the previous column, use an empty check.

Answer 3

It may not be the most elegant, but one approach could be to set a Data Validation sheet that will filter based on what you enter into that row. Once something is entered, the filter pulls related ID #s, and the data validation references that. This only works if you are entering this for a single cell reference though, as you might for a quick lookup.

Use validation from a list (probably on a separate sheet). That list is generated by this formula: =Filter(B2:B, A1:A=A1)

---

If it's over a broader range, you'll need a much larger set up which has been detailed here: https://infoinspired.com/google-docs/spreadsheet/dynamic-dependent-drop-down-list-in-google-sheets/