Excel - extracting data based on another list

Question

Hello all,

I have an Excel worksheet with two columns (name/ID) and then another list that is a subset of the names only from the larger aforementioned list. I want to go through the subset list and then pull the data from the larger list (name/ID) and put it somewhere else...essentially only grabbing the data from the larger list if the name is on the subset.

I tried using Filters, but couldn't get it work. Thoughts?

Thanks.

Answer 1

Try =IF(ISNA(VLOOKUP(A1;B:B;1;FALSE));"";A1), that is, "if the value of A1 exists in the B column, display it here."

Answer 2

Have you tried Advanced Filter? Using your short list as the 'Criteria' and long list as the 'List Range'. Use the options: 'Filter in Place' and 'Unique Values'.

You should be presented with the list of unique values that only appear in your short list.

Alternatively, you can paste your Unique list to another location (on the same sheet), if you prefer. Choose the option 'Copy to another Location' and in the 'Copy to' box enter the cell reference (say F1) where you want the Unique list.

Note: this will work with the two columns (name/ID) too, if you select the two columns as both 'Criteria' and 'List Range'.