You have an Excel workbook with thousands of numbers and words. There must be numbers that are multiples of the same number or word. You may need to find them. So, we’ll look at several ways to find matching values ??in Excel 365
We’re going to find the same words or numbers on two different sheets and two different columns. We will look at using the EXACT, MATCH and VLOOKUP functions. Some of the methods we’ll be using may not work in the web version of Microsoft Excel, but they will all work in the desktop version.
What is the Excel function?
If you’ve used features before, please skip.
The Excel function is like a widget. It uses a series of steps to complete a single task. The most commonly used Excel functions can be found on the Formulas tab. Here we can see that they are grouped by the nature of the function –
- AutoSum
- Recently Used
- Financial
- Boolean
- Text
- Date and time
- Search and reference information
- Math and triggers
- Additional functions.
The Advanced Features category contains the Statistics, Development, Cube, Information, Compatibility, and Internet categories.
Exact function
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. The Exact function itself is case sensitive. He does not believe that New York and New York correspond to each other.
In the example below, there are two columns of text – tickets and receipts. For a total of 10 sets of text, we could compare them by looking at them. However, imagine there were 1000 lines or more. That’s when the Exact function can be used.
Place the cursor in cell C2. In the formula bar, enter the formula
= EXACT (E2: E10, F2: F10)
E2: E10 refers to the first column of values, while F2: F10 refers to the column next to it. After we hit the Enter key, Excel will compare the two values ??in each row and tell us if they are the same (True) or not (False). Since we used ranges instead of two cells, the formula will jump to the cells below it and evaluate all other rows.
However, this method is limited. It will only compare two cells that are in the same row. For example, it won’t compare what’s in A2 to B3. How do we do it? MATCH can help.
The MATCH function
MATCH can be used to tell us where the match for a specific value is in a range of cells.
Suppose we want to know which row a particular SKU (stock holding unit) is on in the example below.
If we want to know which line AA003 is on, we have to use the formula:
= MATCH (J1, E2: E9,0)
J1 refers to the cell with the value we want to match. E2: E9 refers to the range of values ??we are looking for. The zero (0) at the end of the formula tells Excel to find an exact match. If we were to match numbers, we could use 1 to find something smaller than our query, or 2 to find something larger than our query.
But what if we want to know the price of AA003?
VLOOKUP
The V in VLOOKUP stands for vertical position. This means that it can search for a given value in a column. What it can also do is return a value on the same line as the found value.
If you have an Office 365 subscription in a monthly channel, you can use the new XLOOKUP version. If you only have a semi-annual subscription, it will be available in July 2020.
Let’s use the same inventory data and try to find out the price of something.
Where we were looking for a string earlier, enter the formula:
= VLOOKUP (J1, E2: G9,3, FALSE)
J1 refers to the cell with the 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. Number 3 refers to the third column from the start of the range.
Therefore, when we enter the SKU in J1, the VLOOKUP function finds a match and takes the value from a cell 3 columns from it. FALSE tells Excel which match we are looking for. FALSE means it must be an exact match, while TRUE means it must be close.
How do I find matching values ??on two different datasheets?
Each of the above functions can work on two different worksheets to find matching values ??in Excel. We’re going to use the EXACT feature to show you how to do this. This can be done with almost any function. Not just the ones we’ve covered here. There are other ways to link cells between different sheets and workbooks
Working on the “Holders” sheet, enter the formula
= EXACT (D2: D10, Tickets! E2: E10)
D2: D10 is the range we selected on the “Holders” sheet. After we put a comma after that, we can click on the ticket sheet, drag and drop and select the second range.
See how he refers to the sheet and range as tickets! E2: E10? In this case, all rows match, so all results are correct.
How can I also use these functions?
Once you have mastered these functions for matching and finding things, you can start doing a lot of different things with them. Also, note the combination of the INDEX and SEARCH functions to do something similar to VLOOKUP.
Are there some cool tips for using Excel functions to find matching values ??in Excel? Maybe the question is how else to do it? Drop us a line in the comments below.
–