Microsoft Excel Tip of the Week

Microsoft Excel Tips
Play Video
Close

Interesting Microsoft Excel tip and problem solving.

Presented below is our Microsoft Excel tip that will save you time and effort in your everyday Excel activities. Come back and check this page weekly as we do not archive its contents.

Product Questions? 1.805.498.7162   Customers >

Excel Help Topics:

Checking a Worksheet Table to See If an Item Exists

One of the many tasks facing individuals in the workplace today is checking to see if an item in a worksheet table is found in another worksheet table. There are a lot of different ways to do this, however a simple way is to use the COUNTIF worksheet function. In the world of logic, when you want to test for the existence of something, say in a program or in a formula, the most effective way to do it is to simply find some way to count what you are after. The COUNTIF function does this very thing by searching for the existence of a criterion you give it in a worksheet range, a name in this instance, then counting how many times it occurs in that range. So for example, you have a master list in one table and you would like to see if the property ID dropped off in a weekly report table that just arrived. Then you would build the COUNTIF worksheet function to look down the range of the weekly table that has the property ID names in it using the name from your master table, if it returns >0 you know it exists. This simple concept is illustrated below in the table.

To use the COUNTIF function in a formula, select the table column range to look down, in this case D2:D7, as its first argument as seen below. Since you will be dragging the formula down the table, make sure the range has dollar signs around the column headers (i.e. absolute reference) or the range will move when dragged. Next if you are searching for text, simply put a cell reference referencing the text to look for from the master list as the second argument, in this case A2, as seen below. Do not put dollar signs around this one as you want it to move when dragged. When done typing press Enter. The formula will either return 1 or greater  if it detects the name or return 0 if it does not. Simple logic test.

Typing the COUNTIF formula

The COUNTIF worksheet function is typed in a formula referencing the column to look down and what you are looking for.

To finish the problem and see the results, drag and fill the cell with the formula down the worksheet the length of the master table. This is done by left-mouse clicking on the dot at the bottom right-hand side of the formula cell (select the cell first), holding the button down and dragging as seen in the illustration below. You can also double click on the dot and send it down the worksheet (be careful because sometimes the formula will stop at a blank cell in the table).

Dragging and filling COUNTIF formula

Drag and fill the COUNTIF worksheet function down the length of the table to see the results.

When looking for text be very careful because spaces and unwanted characters in the text can skew the result. It is highly suggested that before running the comparison that you build an auxiliary column next to the column you want to look down or the one you are using as the lookup value and run the CLEAN and TRIM functions. These functions get rid of leading and trailing edge spaces in the data and also clean non-printable ASCII characters in the text ( Google ASCII Table and read). Do not underestimate this problem as it occurs in about 70% of the data that EMAGENIT builds software to analyze. The formula would look like this =TRIM(CLEAN(D2)) for the data in column D. If you have other rogue characters in the data, it is also suggested that you use a function like SUBSTITUTE to remove those unwanted characters as well. The idea here is the text has to match exactly or the comparison will fail (no extra spaces or characters). Fortunately COUNTIF is not case sensitive so upper and lower case fluctuations will not matter.


To learn more about how to build useful formulas, you can take our Intermediate Excel for Business and Excel Data Analysis for Engineers and Scientists. View all of our classes here.

Excel Online Advert
10/11
Back <
Next >
Need Help? Please call us at 1.805.498.7162

Microsoft Excel Products

- Excel Self-Study Manuals

Copyright © 2002-2021

EMAGENIT All Rights Reserved