How to filter an Excel table based on the values ​​in a column with another table?

Question

To get started, I don't think VLOOKUP will work for me, because I have dozens of columns in each table.

Let's say I have table 1:

Serial# Value1 Value2 Value3 Value4 1 4 34 65 23 2 123 43 54 46 3 87 98 85 34 4 654 8 645 3

And table 2:

SF 1 3 4

I need a way to filter table 1 based on the serial numbers found in SF.

Serial# Value1 Value2 Value3 Value4 1 4 34 65 23 3 87 98 85 34 4 654 8 645 3

Using VLOOKUP means that I have to write a formula for each column that I want to fill. I would like to avoid this.

thanks

Answer 1

Based on your example, I don’t see too many columns that are a problem for the formula if you are matching all the series from one column to another.

Assuming table 2 is in a table called Table2. You can use this formula at the end of a table column.

= IF (ISERROR (VLOOKUP (A1, Table2! A: A, 1, FALSE)), "FILTER ME OUT", "KEEP ME")

Paste the above formula all over the column to the last row in the dataset.

Then filter out this extreme column depending on how you find it suitable, I assume closing the actual filtering will be redundant, but here it is intended for beginners. I would use AutoFilter (click anywhere in the Table1 dataset, hold down the alt key, press "DFF" for Excel 2003 and earlier or Ctrl + Shift + L for later versions of Excel or go to Data → Filter), select the arrow on newly created filter column and filter the "FILTER ME OUT" values.