You have an Excel workbook with thousands of numbers and words. There are certain multiples of the same number or word in it. You may need to find them. So we’re going to look at a few ways you can find the right values in Excel 365.
We’ll cover finding the same words or numbers in two different worksheets and in two different columns. We will look at using the EXACT, MATCH, and VLOOKUP functions. Some of the methods we will be using may not work in the web version of Microsoft Excel, but they will all work in the desktop version.
What is an Excel function?
If you have used the functions before, skip it.
An Excel function is like a small application. It applies a series of steps to perform a single task. The most commonly used Excel functions can be found in Formula navigation. Here we see them categorized by the nature of the function –
- Recently used
- Date and time
- Lookup & Reference
- Math & Trig
- More functions.
The Other functions directory contains categories Statistics, Engineering, Shapes, Information, Compatibility, and Web.
The job of the Exact function is to go through the rows of two columns and find the matching values in the Excel cells. Exact means exact. Only the Exact function is case sensitive. It won’t see New York and New York like a match.
In the example below, there are two columns of text – Tickets and Receipts. With only 10 sets of texts, we can compare them by looking at them. Imagine if there were 1,000 rows or more. That’s when you use the Precise function.
Place the cursor in cell C2. In the formula bar, enter the formula
E2: E10 refers to the first value column and F2: F10 refers to the column right next to it. Once we press enterExcel will compare two values in each row and tell us if it matches (THAT’S RIGHT) or not (Wrong). Since we used ranges of cells instead of just two cells, the formula will overflow into the cells below it and evaluate all other rows.
This method is limited though. It will only compare two cells that are on the same row. It won’t compare what’s in A2 with B3 for example. how do we do it now? MATCH can help.
MATCH can be used to tell us the match position for a specific value in a range of cells.
Let’s say we want to find out which row a particular SKU (Inventory Holder) is in, in the example below.
If we want to find which row AA003 is in, we will use the formula:
J1 refers to the cell whose value we want to match. E2: E9 refers to the range of values we are looking for. Zero (0) at the end of the formula tells Excel to look for an exact match. If we match the numbers we can use first to find something less than our query or 2 to find something bigger than our query.
But what if we want to find the price of AA003?
VLOOKUP . function
The DRAW in the function VLOOKUP stands for the word vertical. Meaning it can search for a certain value in a column. What it can also do is return a value on the same row as the one found.
If you already have an Office 365 subscription in the Monthly channel, you can use the newer version XLOOKUP. If you only have a semi-annual subscription, it will be available to you in July 2020.
Let’s use the same inventory data and try to find the price of something.
Where we searched for a row before, enter the formula:
J1 refers to the cell whose value we are matching. E2: G9 is the range of values we are working with. But the VLOOKUP function will only look for a match in the first column of that range. The 3 refers to the 3rd column from the beginning of the range.
So when we enter the SKU in J1, the VLOOKUP function will find a match and get the value from the cell 3 columns above it. WRONG tells Excel what match type we’re looking for. FALSE means it should be an exact match which TRUE will tell it to be an approximate match.
How to find matching values in two different sheets?
Each of the functions above can work on two different worksheets to find matching values in Excel. We’ll use the EXACT function to show you how. This can be done with almost any function. Not just the ones we mentioned here. There are also other ways to link cells between different worksheets and workbooks.
Work on Owner sheet, we enter the formula
D2: D10 is the range we selected on the Holders page. When we put a comma afterwards, we can click on the Ticket and drag and select the second range.
See how it refers to sheets and ranges like Ticket! E2: E10? In this case, each row matches, so all results are True.
How else can I use these functions?
Once you master these functions to match and find things, you can start doing various things with them. Also take a look use INDEX and MATCH functions together to do something similar to VLOOKUP.
Do you have some good tips on using Excel functions to find matching values in Excel? Maybe a question of how to do more? Drop us a note in the comments section below.