There are times when you want to look up and retrieve values based on a list. Normal filtering can only take up a maximum of two items and limited criteria conditions. In this article, you shall learn 4 ways to look up items not matching a list.

For example, below we have a list of mechanics and assigned cars to service. If only 3 cars were serviced, how do you retrieve the lazy mechanics?

Method 1: Using Formula to Lookup Values, Not in a List

Here is the formula;

{=INDEX(
Table13[Mechanics],
SMALL(
IF(
COUNTIF(Car_Serviced,Table13[Car to Service])=0,
ROW(Table13[Mechanics])-1
)
,ROW(C1)))}

How it Works:

►COUNTIF(Car_Serviced, Table13[Car to Service]) returns a count of 1 for the serviced car and 0 for unserviced cars

{0;0;1;0;1;1;0;0;1;0;1;1;1;0;0;1;0;1;1;0;1;0}

►Since we are interested in the unserviced car, Car_Serviced, Table13[Car to Service])=0, returns an array of TRUE & FALSE

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;
FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;
TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}

►IF function returns the Rows (ROW(Table13[Mechanics])-1) for the mechanics where the condition is TRUE. NB: We deduct 1 to cater for the heading row

SMALL(
{1;2;FALSE;4;FALSE;FALSE;7;8;FALSE;10;FALSE;FALSE;FALSE;14;15;FALSE;17;FALSE;FALSE;20;FALSE;22}
,ROW(C1))

►SMALL function, using the k Value supplied by (ROW(C1)) returns the row numbers from smallest to largest which INDEX function uses to look up the mechanic’s name

PS:

• This is an array formula, so remember to Ctrl + Shift + Enter
• If you do not want to see the error, modify the formula to look like below

{=IFERROR(
INDEX(Table13[Mechanics],
SMALL(
IF(COUNTIF(Car_Serviced,Table13[Car to Service])=0,ROW(Table13[Mechanics])-1)
,ROW(C1)
)),"")}

• The method is made simpler by using the Excel table, If you are not conversant with excel tables Read this 7 Secrets of Excel Table

Method 2: Using Power Query to Lookup Items Not in List

Power Query is here to make the life of a Data Analyst easy.  This method assumes you have the basics of power query skills, if not, Check this course on Power Query Fundamentals to Expert

Steps:

►Load the tables to power query; Click on a table→Go to Power query→Click “From Table/Range”. Repeat process for car serviced table

►From Power Query,  close & load the table as a connection. Repeat process for car serviced table

►Now that you have 2  Workbook connections, Right click on one on the and then click Merge.

►Select the two tables→Click on the two matching columns→Select the Left Anti-Join kind

►On the Query editor, delete the unnecessary columns then Close & load to

►Load the query as a table→to the existing worksheet→select a cell to load the table

That’s all you need!

If this looks complicated for you, Check this course on Power Query Fundamentals to Expert

What if you need to look up the Mechanics who serviced their cars i.e. Whose cars were on the list?

Easy!! 😊

Just Change the Join type to ” Inner Join” as shown below.

Method 3: Using Excel add-in Query storm to Lookup Items Not in List

Query Storm is a powerful Excel add-in for anyone who wants to utilize their T-SQL skills in excel.

It makes it easy to query and modify data in Excel as if the data were in a SQL database.

Follow the below steps after installing & taking a simple introduction of the  Query Storm add-in

► Go to Query Storm→Connect (SQLite)

► On the new query window, write below the anti-join query→Run query. NB: Ensure your tables are well labeled.

SELECT Mechanics
FROM Mechanic_Cars mc
WHERE NOT EXISTS (SELECT *
                   FROM Serviced s 
                   WHERE mc.[Car Serviced]=s.[Car Serviced])

► Write back the results in Excel.  Ensure you give the table a name. That’s All.

Method 4: Using Advanced Filter to Look-up items not matching items in a list

Excel advanced filter is one helpful tool if you know how to use it.  The below method assumes you are conversant with Using formulas within the advanced filter criteria range.

Here are the steps:

►Set up a criteria area and type this formula, =COUNTIF($D$2:$D$4,B2)=0. The formula finds all the cars to be serviced that was actually serviced and returns 1. Since we are interested in unserviced cars we make it equal 0

►Go to DATA, Advance Filter, select the list of mechanics & cars, select criteria, and finally area to copy.

NB: Select the only column to copy, type the column header on the section as shown above.

Method 5: Using Office365 FILTER Function

As suggested in the comments below by Peter Bartholomew, you can use a simpler dynamic function–FILTER

=FILTER(All_Cars[Mechanics],COUNTIF(Car_Serviced,All_Cars[Car to Service])=0)

To get the list of Mechanics to reward, Wrap the COUNTIF function with NOT.

=FILTER(All_Cars[Mechanics],NOT(COUNTIF(Car_Serviced,All_Cars[Car to Service])=0))

Conclusion:

There is no one way of doing anything!

Always strive to look for and learn alternatives, you never know when they may come in handy.

If I have missed any method, let me know in the comments.

Download the WORKSHEET