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 |
Best Answer
Why not have a column that concatenates ACC and UNQ#, like:-
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.