Excel 2010 - how to Select or display rows if column C has specific values? No VB

I have a huge spreadsheet and column C contains IDs such as 1, 99, 732387, 28. I have a separate list of IDs such as 23, 87, 902 and I only want to display the rows whose column C value belongs to my list. What is the simplest way to do that?

Example:
My spreadsheet:
A B 1 C D
E F 3 G H
I J 8 K L
M N 9 O P
My list: 3, 9.
I want to see 
E F 3 G H 
M N 9 O P
and hide other rows.

Solution 1:

I think, filtering is the way to go.

As described in earnie's tutorial and in Grietom's answer, you can select filtering criteria by enabling/disabling checkboxes.

Advanced filtering in Excel

If your list of lines to show is longer (and maybe changes from time to time), you could also use the approach of advanced filtering as described here in detail:

https://support.office.com/en-us/article/Filter-by-using-advanced-criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b

It lets you

• use a range of cells for definition of filtering criteria
• define more complex combinations of criteria with AND/OR, ...
• you can filter the original table directly or let Excel write the results into another table

taken from the above mentioned Microsoft tutorial:

The following sections provide examples of complex criteria.

• Multiple criteria in one column
• Multiple criteria in multiple columns where all criteria must be true
• Multiple criteria in multiple columns where any criteria can be true
• Multiple sets of criteria where each set includes criteria for multiple columns
• Multiple sets of criteria where each set includes criteria for one column
• Criteria to find text values that share some characters but not others
• Criteria created as the result of a formula
  • Filtering for values greater than the average of all values in the data range > * Filtering for text by using a case-sensitive search

Step by step tutorial

(sorry, I don't have Excel 2010, but an older version, so maybe the menus are a little different, but in general it should work like that)

1. Create List with filtering criteria:
   Column name on top, all following lines are combined with "OR" operator (Cells A11:A13in my example

2. Select cell range which shall be filtered (A2:E6 in the example)

3. Go to menu Data / Filters / Advanced Filters...
   

4. Define filtering criteria:
   results shall be shown in new cell range
   criteria in range A11:A13
   results will be in range A20

5. After confirming with ok, you should have the filtered rows in Cells A20:E22 like that:
   

Additional ressources

You'll also find a tutorial video for Excel 2010 on Youtube

Solution 2:

Depending on how big the data set is, I can think of two approaches.

The first would be to use filters. There's a decent tutorial on this here

The second one be to use VLOOKUP. You can do this with VLOOKUP with something like:

=IF(ISNA(VLOOKUP(C1;F:F;1;FALSE));"";C1)

Breaking this formula down, the inner most statement is the VLOOKUP, where you look for an exact match for the the value in C1 in column F. If no match is found, an #N/A will be returned, and ISNA() will return True. The True clause for the IF statement says make the cell "", otherwise, keep it.

The full implementation would be something like putting that formula in column A on a new worksheet, and columns B through F would be of the form:

=IF(ISBLANK(A1),"",B1)

And repeat that for the other columns, and fill down.

Once that's done, you could copy and paste special, values only, and sort the data to get rid of the empty rows.