How to use vlookup in excel to find matching data

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.                                  

Home ➜ Excel Formulas ➜ How to use VLOOKUP MATCH Combination in Excel

The Combo of VLOOKUP and MATCH is like a superpower. Well, as we all know VLOOKUP is one of the most popular functions.

Right?

It can help you to quickly lookup a value in a column. But when you use it more and more you will realize that there are some problems with it. And, the biggest problem is it’s not dynamic.

In VLOOKUP, col_index_no is a static value which is the reason VLOOKUP doesn’t work as a dynamic function. And this is where you need to combine VLOOKUP with MATCH. 

If you are working on multiple column data, it’s a pain to change its reference you have to do (change column number) this manually.

The best way to solve this problem is to use the MATCH function in VLOOKUP for col_index_number. Today in this post, I going to explain all the stuff you need to know to use this combo formula.

Problems With VLOOKUP

I have found the two biggest reasons to create a combination of these two functions.

1. Static Reference

Just look at the below data table where you have 12-month sales for four different employees.

Now, let’s say you want to look up the Feb month’s sale of “John”. The formula should be like this.

=VLOOKUP(“May”,A1:E13,2,0)

And in this formula, you have mentioned 2 as the col_index_num because John’s sale is in the second column. But what will you do if your boss tells you to get the sales value for “Peter”?

You need to change the value in col_index_num because it’s not dynamic.

2. Add or Delete Columns

Now think in a different way. You need to add a new column for a new employee just before John’s column.

And here, John’s column number is 3 and your formula result is incorrect.

Again here, because col_index_num is a static value you need to change it manually from 2 to 3 and again if you need something else.

At this point, you are clear about one thing that you need to make col_index_num dynamic. And for this, the best way is to replace it with the MATCH function.

Why Match Function

Before you combine VLOOKUP and MATCH, you need to understand the match function and its work.

The basic use of MATCH is to find the cell number of the lookup value from a range.

Syntax: MATCH(lookup_value,lookup_array,[match_type])

It has mainly three arguments, lookup value, a range to lookup for the value, and the match type to specify an exact match or an approximate match.

For example, in the below data I am lookup for the name “John” with the match function from a heading row.

And, it has returned 2 in the result because the name is in the 2nd cell of the row.

VLOOKUP and MATCH Together

Now, it’s time to put VLOOKUP and MATCH together. So, let’s continue with our previous example.

First of all, let’s create a formula by using both of the functions, and then we’ll understand how these two work together.

Steps to create this combo formula:

• First of all, in a cell enter the month’s name, and in another cell enter the employee’s name.
• After that, enter the below formula in the third cell.

=VLOOKUP(C15,A1:E13,MATCH(C16,A1:E1,0),0)

In the above formula, you have used VLOOKUP to lookup for the MAY month, and for the col_index_num argument, you have used the match function instead of a static value.

And in the match function, you have used “John” (employee name) for the lookup value.

Here match function has returned the cell number for the “John” from the above row. After that, VLOOKUP used that cell number to return the value.

In simple words, the MATCH function tells VLOOKUP the column number to get the value from.

Problems Solved?

Above you have learned about two different problems which are because of static col_index_num.

And, for this, you have combined VLOOKUP and MATCH. Now, we need to check whether those problems are solved or not.

1. Static Reference

You have referred employee name in the match function to get the column number for VLOOKUP.

So now, when you change the employee name in the cell, the match function will change the column number. And when you need to get the value for a different employee you just have to change the employee name in the cell.

This way, you have a dynamic col_index_number.

Finally, you don’t have to edit the formula again and again.

2. Add or Delete Columns

Before adding a new column John’s data was is in the 2nd column and the match function returned 2. And after you have inserted a new column john’s data is in the 3rd column and the match returned 3.

When you add a new column for a new employee the value in the formula is not changed because the match function updates its value.

In this way, you’ll always get the correct column number even when you insert/delete any column. The MATCH function will return the right column number.

• Sample-File