We’re going to compare the results of using the EXACT, MATCH, and VLOOKUP functions on two different worksheets. We’ll use the web version of Microsoft Excel, but some methods won’t work in the desktop version.
What’s An Excel Function?
If you’ve never used functions before, read on. Functions are a way to make your code more organized and easier to read. They can also help you solve problems faster. A function is a set of instructions that a computer can use to solve a problem. Functions are written in the same language as the code that uses them, so you can easily see what the function does and how it works. To use a function, you first need to create a file called function.h in your project’s root directory. Then, you need to include the function in your code by adding it to the top of your file like this: #include “function.h” Now that you have included the function, you can use it by calling it like this: int main() { // do something }
There are many Excel functions that can be used to do common tasks, such as adding and subtracting numbers, changing values in a text field, and more. To find the function you need, look in the Formulas tab on the left-hand side of the Excel window.
AutoSumRecently Used Financial Logical Text Date & Time Lookup & Reference Math & TrigMore Functions.
The More Functions category contains the categories Statistical, Engineering, Cube, Information, Compatibility, and Web. ..
The Exact Function
The Exact function is designed to find the same values in two columns of data. It is case sensitive, so it won’t work on new york and new york as match.
In the example below, there are two columns of text – Tickets and Receipts. For only 10 sets of text, we could compare them by looking at them. Imagine if there were 1,000 rows or more though. That’s when you would use the Exact function. The tickets column has a total of 10 sets of data while the receipt column has a total of 100 sets of data. The tickets column has a smaller number of data items while the receipt column has a larger number of data items.
=A2+B2 The result is C2=14. ..
If we press Enter, Excel will compare the two values in each row and tell us if it’s a match (True) or not (False). If False, then the formula will spill over into the cells below it and evaluate all the 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 that? MATCH can help.
The MATCH Function
MATCH can be used to find a specific value in a range of cells. ..
We can use the SKU number to find out what stock is in that row.
Row AA003 is in the row that is 3rd from the bottom.
J1 refers to the cell with the value we want to match. E2:E9 refers to the range of values we’re searching through. The zero (0) at the end of the formula tells Excel to look for an exact match. If we were matching numbers, we could use 1 to find something less than our query or 2 to find something greater than our query.
What if we wanted to find the cost of air-quality in a city?
The VLOOKUP Function
VLOOKUP can search for a given value in a column, but it can also return a value on the same row as the found value. ..
If you have an Office 365 subscription in the Monthly channel, you can use the newer XLOOKUP. If you only have the semi-annual subscription, it will be available to you in July 2020. ..
Inventory data can be used to find the price of something. ..
=IF(A2>B2,A2,B2) This will give you the row that is above or below the current row. ..
The J1 cell has the value we’re matching. The E2:G9 range of values we’re working with is the first column of that range, but VLOOKUP will only look in the first column of that range for a match. The 3 refers to the 3rd column over from the start of the range.
When we type a SKU in J1, VLOOKUP will find the match and grab the value from the cell 3 columns over from it. If FALSE is set to TRUE, Excel will tell us that it must be an exact match where TRUE would tell us that it has to be a close match.
How Do I Find Matching Values in Two Different Sheets?
The EXACT function can be used to find the values in a worksheet that are exactly match between two sheets. This can be done with almost any function, as long as it has a matching input and output.
The Holders sheet is a table that shows the current state of the holders of a particular item. The table is sorted in descending order, with the most recent holders at the top.
D2:D10 is the range we’ve selected on the Holders sheet. Once we put a comma after that, we can click on the Tickets sheet and drag and select the second range. ..
The results of the search are all True because each row in the sheet matches.
How Else Can I Use These Functions?
With these new functions, you can start doing a lot of different things with them. You can also use the INDEX and MATCH functions together to do something similar to VLOOKUP.
Looking for ways to use Excel functions to help you find matching values? Check out our blog for some tips! ..
