Search code examples
excelvlookup

Using VLOOKUP() to compare and filter two columns in different worksheets

When I filter a column using VLOOKUP() between two worksheets, how can I also filter all of its respective row elements too? Maybe this will help illustrate my question.

I have two worksheets:

Sample0.xlsx

enter image description here

Sample1.xlsx

enter image description here

I want to filter the items that are in the New list column of Sample1.xlsx but are not in the Old List column of Sample0.xlsx. And have its respective matching Type row elements also filtered along with it.

I'm using the following VLOOKUP() formula:

=FILTER(A2:A15,ISNA(VLOOKUP(A2:A15,[Sample0.xlsx]Sheet1!$A$2:$A$15,1,FALSE)))

And I get these results:

enter image description here

How can I also filter the Type column that goes along with the New List column that I just filtered out? I hope it makes sense. Thanks

Solution

  • The first argument of FILTER() describes what column(s) you want to return, irrespective of what columns you want to match. Your formula =FILTER(A2:A15,… specifically means “just give me the column A values for whatever values match.”

    I believe you want the first argument to be =FILTER(A1:B15,…