Lookup data not in a Sample

4 Ways To Lookup Data Not in a List/Sample Using Excel

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 lookup items not matching a list.

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

Lookup using a Sample

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)))}

look up not in list

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 with 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

Lookup Unique Values

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

►Now that you have 2  Workbook queries, Click on 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 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 for 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 was in a SQL database.

Follow 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 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.  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.

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

2 thoughts on “4 Ways To Lookup Data Not in a List/Sample Using Excel”

  1. Thanks for the mention Crispo! Great article. If any readers would like to know more about QueryStorm then they can visit our website or send me an email at ben@querystorm.com

  2. Romeo says:

    Great staff to learn,Thank you

Leave a Comment:

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.