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

Question

To start, 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 would like 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 a VLOOKUP would mean I have to write a formula for every column I want filled. i'd like to avoid that.

Thanks

Solution

Based on the example you've given I don't see too many columns being a problem for a formula if you're matching all the serials from 1 column to another.

Assuming Table2 is in a tab called Table2. You could use this formula at the end column of Table1.

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

Paste the above formula down the whole column until the last line on the dataset.

Then filter on that edge column whichever way you see fit, I'm assuming covering the actual filtering will be overkill but here it is for any beginners. I would use an AutoFilter (Click anywhere in Table1 dataset, hold down alt, press "DFF" for Excel 2003 and prior or Ctrl + Shift + L for the later versions of Excel or go to Data -> Filter), select the arrow on the newly created filter column and filter out the "FILTER ME OUT" values.