Excel - extracting data based on another list

excel extract
Mark picture Mark · Jun 19, 2009 · Viewed 123.8k times · Source

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

l0b0 picture l0b0 · Jun 19, 2009

New Excel versions

=IF(ISNA(VLOOKUP(A1,B,B,1,FALSE)),"",A1)

Older Excel versions

=IF(ISNA(VLOOKUP(A1;B:B;1;FALSE));"";A1)

That is: "If the value of A1 exists in the B column, display it here. If it doesn't exist, leave it empty."

Related questions

In Excel, how do I extract last four letters of a ten letter string?

For example, if I have ABS YUR YUAO HFH IWO OQNX YQO PQM QUCC How do I extract the last four letters in another column?

Read More
Replacing one character with another in a string

I have a data like below: A:B:C:D and I want to replace the C with data (say, Z) so that it may look like A:B:Z:D How can I do it?

Read More
Extract number between brackets in Excel?

I've been searching up and down for an answer to this, but haven't found anything I can get to work. It may well be my lack of knowledge in the more "codey" aspects of Excel. Anyway, this is my situation: …

Read More