How to use vlookup function in excel to find matching data
A free Office suite fully compatible with Microsoft Office
One of the most used Excel functions is the VLOOKUP function. There are a lot of simple VLOOKUP examples in this post.
How to use VLOOKUP in excel online, 2016 and 2019:
When you use the VLOOKUP function in Excel, you often want an exact match. Let's look at what the VLOOKUP function's arguments are.
The VLOOKUP function below searches for the number 53 (first parameter) in the table's leftmost column (second argument).
The VLOOKUP function is instructed to return the value in the same row from the fourth column of the red table by the number 4 (third argument).
The fourth argument, the Boolean FALSE, instructs the VLOOKUP function to yield an exact match. The VLOOKUP function will produce a #N/A error if it is unable to locate the value 53 in the first column.Here's another illustration. The VLOOKUP code below retrieves the last name of ID 79 instead of returning the salary (third parameter is set to 3).
Approximate match mode in VLOOKUP function in excel:Here is an illustration of the VLOOKUP function in the approximate match mode (fourth argument set to TRUE).The first input of the VLOOKUP function below searches the leftmost column of the red table for the number 85. (second argument). There is only one issue. In the first column, there is no value for 85.
Fortunately, the VLOOKUP method is instructed to return a close match via the Boolean TRUE (fourth argument). The greatest value smaller than 85 will be returned if the VLOOKUP function is unable to locate the value 85 in the first column. This example will use the number 80 as the value.
The VLOOKUP function is instructed to return the value in the same row from the second column of the red table by the value 2 (third argument).
If you use the VLOOKUP function in approximate match mode, always sort the red table's leftmost column in ascending order (fourth argument set to TRUE).Vlookup Looks right in excel:A table's leftmost column is always searched for a value using the VLOOKUP function, which then returns the appropriate value from the rightmost column.The VLOOKUP function, for instance, searches for the first name and returns the last name.
The salary is returned by the VLOOKUP function when the third argument, the column index number, is changed to 3.
Notably, the VLOOKUP function is unable to look up the first name and return the ID in this scenario. When using VLOOKUP, just the right is searched. No worries, Excel's INDEX and MATCH functions can be used to carry out a left lookup.Note: This above written article is an attempt to show you how to use VLOOKUP in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.
Was this helpful?
- 1. How to compare two excel sheets and highlight differences
- 2. How to expand cells to fit text in Excel
- 3. How to copy file names in Excel from a folder?
- 4. How to import external data in WPS Spreadsheet
- 5. How to use the VLOOKUP function across multiple sheets in WPS Office Excel?
- 6. Top 10 simple family tree template Excel free download