In the example below, in the table on the right, we want to substitute the results of the mathematics exam for the student Rob B. We know the data for all students from the table on the left. Using the formula, we can substitute the data we need.

Syntax

= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Function arguments

lookup_value (lookup_value) – is the value to search for, from the leftmost column of the table. It can be a value, a cell reference, or a text string. In the student example, these are their surnames;
table_array (table) is the data range that will be searched. It can be a reference to a range of cells or a named range. In the student table example, this would be the entire table that contains the grade and names of the students;
col_index (column_number) is the ordinal number of the column in the data range from which the desired value will be obtained;
[range lookup] ( [range lookup] ) – This argument specifies the precision of the search data match. Specify “0” if it is exact, “1” if it is an approximate match.

Additional Information

The match of the required data can be exact and approximate;
For Correct match we should have any unique field in data file/Excel sheet.
When matching for approximate data accuracy, ensure that the data in the tables is sorted in descending order (high to low). Otherwise, the result of the match will be incorrect;
When comparing data by approximate accuracy:
– If the function does not find the desired value, it will return the largest value, which will be less than the search value;
– If the function gives an error # N / A, when matching, then the desired value is less than the smallest value in the desired range;
– You can use wildcards for the values you are looking for.

Examples of using VLOOKUP in Excel

Example 1. Looking for an exam result for a student

In the example below, the list of students is shown in the left column of the table. On the right side of the table, the final exam scores of these students are indicated.

We are faced with the task of finding out what grade the student Rob B received in Math.

We can find out using the formula below:

= VLOOKUP (“Petrov”, $ A $ 3: $ E $ 10,0)

The formula above has four arguments:

“Rob B” – surname, data on which, we are looking for;
$ A $ 3: $ E $ 10 – data range with exam results;
“3” – ordinal number of the column of the data range with the value of the assessment in Math;
“0” – exact match of the required value.

Below, it is clearly shown how our function works by example.

First, it looks in the leftmost column of the data range for the name “Rob B”. The search takes place from top to bottom.

As soon as Excel finds an exact match “Rob B” in the data range, the system “steps” to the right, to the third column to display the desired data.

Using this approach, you can get any data for each student and subject.

For example, to find the results of the exam for student Rob B in Math, we need the formula:

= VLOOKUP (“Rob B”, $ A $ 20: $ E $ 28.4.0)

In the example above, the student’s last name is in double quotes. Also, you can use a cell reference with the desired value. A cell reference makes the formula dynamic.

For example, if you have a cell with a last name and you are looking for a math grade, the result will automatically update when the student’s name changes (as shown below):

If a student’s last name is entered as a search argument, which is not in the data range, the function will generate an error.

Example 2. Two-factor data search

In the example above, we manually specified the ordinal number of the data column as the “column_number” attribute.

But what if we want to make the search dynamic? What if we want to enter the student’s name and subject name and the function will automatically output the required data? To do this, we need to use a two-factor search.

Below is an example of a two factorial search:

To make a two-factor search formula, it is important to make the selection of data from columns dynamic. So, when the user changes the name of the subject: Physics, Mathematics, etc., the corresponding attribute of the data column changes in the formula.

To do this, we need the MATCH function as an argument responsible for the ordinal number of the column.

This is what the formula for a two-factor search would look like:

= VLOOKUP ($ G $ 5, $ A $ 4: $ E $ 12, MATCH ($ H $ 4, $ A $ 3: $ E $ 3.0), 0)

The formula above uses the MATCH function , which is responsible for the ordinal number of the data column. MATCH takes the subject name as the search value (in cell “H4”) and returns its position in the cell range “A2: E2”.

If you specify “Mathematics”, Excel will return “2” because “Mathematics” is in cell “B2” (the second in this array).

Example 3. Using a drop-down list for two-factor search

Using the method from example # 2, we need to do a lot of manual work. There is a high risk of making mistakes and wasting a lot of time, especially if you work with a large amount of data.

Here it is better to use drop-down lists as search values (in our example, these are the names of students and academic subjects).

Based on the data that you select from the drop-down list, the function will automatically display the necessary data, as shown in the example below:

This approach allows you to create a dashboard with which you can easily process large lists of data.

Below we will look at how to make such a table.

To do this, we need the formula from Example # 2:

= VLOOKUP ($ G $ 5, $ A $ 4: $ E $ 12, MATCH ($ H $ 4, $ A $ 3: $ E $ 3.0), 0)

The search data has been converted to drop-down lists.

To make a drop-down list, you need:

Select the cell where you want to create the dropdown list. In our example, in cell “G4”, we indicated the names of the students.
Go to Data tab -> Data Validation.
In the Data Validation dialog box, Settings tab, select List and from the drop-down list, select Allow.
Select $ A $ 3 in the source: $ A $ 10
Click “OK”.

You will now have a drop-down list in cell “G4”. Likewise, you can create it in “H3” for items.

Example 4. Three-factor search for VLOOKUP data

What is three factor search?

In Example 2, we used one spreadsheet with grades for students in different subjects. This is an example of a two-factor search because we are using two variables to get the grade (student name and subject).

Now suppose that by the end of the year, the student has passed three levels of exams: Entrance, Semi-Annual, and Final Exam.

Three-way search is the ability to get a student’s grades in a subject with a specific exam level.

Here’s an example of a 3-way search:

In the example above, the function looks for data in three different data tables (Entrance, Semi-Annual, and Final Exam) and returns the student’s grades for a specific subject at a specific exam level.

For such calculations, the formula will help us:

= VLOOKUP (G5, CHOOSE (IF (H3 = ”Opening”, 1, IF (H3 = ”Semi-Annual”, 2,3)), $ A $ 4: $ E $ 12, $ A $ 16: $ E $ 24, $ A $ 28 : $ E $ 36), MATCH (H4, $ A $ 3: $ E $ 3.0), 0)

This formula uses the CHOOSE function to ensure that the data is selected from the correct table. Let’s analyze the CHOOSE part of the formula:

CHOOSE (IF (H3 = ”Opening”, 1, IF (H3 = ”Semi-Annual”, 2,3)), $ A $ 4: $ E $ 12, $ A $ 16: $ E $ 24, $ A $ 28: $ E $ 36) , MATCH (H4, $ A $ 3: $ E $ 3.0), 0)

The first argument of the formula (IF (H3 = “Opening“, 1, IF (H3 = “Semi-annual”, 2,3) or (IF (H3 = “Introductory”; 1; IF (H3 = “Semi-annual”; 2; 3) checks cell “H3” and determines which exam level is being referenced If it is “Opening”, the function returns data from the range $ A $ 4: $ E $ 12, which contains the grades for the entrance exam.

If the Semi-Annual level is selected, the system returns data from the range $ A $ 16: $ E $ 24, otherwise it returns data from the range $ A $ 28: $ E $ 36.

This approach makes the array of tables dynamic and three-factorial.

Example 5. Get the last column value using the VLOOKUP function

With VLOOKUP, you can calculate the last number from the list.

The largest positive number you can use in Excel is 9.99999999999999E + 307 . This also means that the highest lookup number in the VLOOKUP number is also 9.99999999999999E + 307 .

I don’t think you will ever need some sort of calculation involving such a large number. But with it we can get the last number in the list.

Suppose you have a dataset (in the range of cells A1: A14) as shown below and you want to get the number from the last cell in the list.

For this, the following formula is suitable for us:

= VLOOKUP (9.99999999999999E + 307, $ A $ 1: $ A $ 14, TRUE)

Note that the formula above uses the approximate precision of the data match (although there is no need to reorder the list).

This is how the function works with the approximate precision of the data match. She scans the left column from top to bottom.

If it finds an exact match, it returns that value;
If it finds a number that is greater than the search value => returns the number in the cell above it;
If the search value is greater than all the numbers in the list, the function returns the last number in the list.

In our example, the third scenario works.

Number 9.99999999999999E + 307 is the largest number that you can use to Excel, where it is used as search criteria, then the function VLOOKUP (CDF) returns the last number from the list.

In the same way, you can use this principle to return the last text item from a list. Here’s a formula that can do it:

= VLOOKUP (“yayay”, $ A $ 1: $ A $ 14,1, TRUE)

The same logic applies here. The system looks at all the names from the list. Since “yayaya” is the largest text value => the result of the calculation will be the most extreme value from the data list.

Example 6 Partial Search Using Wildcards and VLOOKUP

Wildcards in Excel can be useful in many situations.

Partial search is necessary when you need to search for a value in a list that does not exactly match the data you are looking for.

For example, you have a dataset as shown below and you want to find a company “ABC” in the list, but the list only has “ABC Ltd” instead of “ABC”.

You cannot use “ABC” as the search value because there is no exact match in the “A” column. An approximate match often leads to erroneous results and requires sorting the list in ascending order.

However, you can use the VLOOKUP wildcard to match the data more closely.

Enter the following formula in cell “D2” and drag it to other cells:

= VLOOKUP (“*” & C2 & ”*”, $ A $ 2: $ A $ 8,1, FALSE)

How it works?

In the above formula, we have added asterisks to the search value on both sides. By specifying such “asterisks” you let Excel know that you are ready to carry out a search, in which any other words, letters or numbers can be found in the desired data both at the beginning and at the end of the value.

Thus, when performing a search, Excel will understand that it is necessary to search for a value that contains the word from cell “C2” (in our example).

For example, cell “C2” contains the company name “ABC”, so the function looks at the values in the data range “A2: A8” and looks for “ABC”. It matches cell “A2” since it contains the word “ABC” in “ABC Ltd”. It doesn’t matter if there are any characters to the left or right of “ABC”.

Note: VLOOKUP always returns the first matching value and stops looking further. Therefore, if you have a company name “ABC Ltd”. and “ABC Corporation” in the list, it will return the first in order and ignore the rest.

Example 7. The VLOOKUP function returns an error despite a match in the search value

It can drive you crazy when you see that there is a matching lookup value and the function returns an error.

For example, you have an obvious match (Karl W) in your table, but the system still returns an error.

As you look deeper in the values in the table, you may notice that in the data range, the value “Karl W” is written with a space. If there are spaces before values, after or between words in the range of data by which you are searching, the function will not match them.

There is a solution, and its name is the TRIM function. It removes any extra spaces in the values.

Below is a formula to help you match your data despite extra gaps in your data range.

= VLOOKUP (“Karl W”, TRIM ($ A $ 2: $ A $ 15), 1.0)

In order for the TRIM function to work when using VLOOKUP , you must enter the formula after typing, not using the Enter key on the keyboard, but using the Ctrl + Shift + Enter key combination.

Example 8. VLOOKUP function in Excel with several conditions

A function, in its basic form, can search for a single lookup value and return matching data from a specified data range.

But often we need to use VLOOKUP with multiple search criteria.

Let’s imagine that you have a table with the results of students’ exams for three levels of the exam: “Entrance”, “Semi-Annual” and “Final Exam”:

Comparing data on a specific student, subject and exam level using the VLOOKUP function is not an easy task, since comparing data on a student and subject, it will give the result of the first match, but it is far from the fact that this result will be correct and correspond to the required exam level.

You can search by student + subject name + exam level by creating a helper column, as shown in the example below:

Now, we need to create a unique ID for each student and exam level using the formula in the helper column: = A2 & ”|” & B2 .

Stretch this formula into each cell of the column of the given table. Thus, we will create a unique ID by which we can compare the data.

Thanks to the auxiliary column, we have avoided the fact that when comparing data, the function will return data for an unnecessary exam level.

You can now use the helper column values as lookup values.

Below is a formula to help you compare data from your spreadsheet to a list of students by exam level.

= VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)

With this formula, we have combined the student’s name and exam level to get the lookup value that we check in the helper column and get the data.

Example 9. Handling errors using the VLOOKUP function

The function returns an error every time it cannot match a search value in the given data range.

Excel has the ability to replace error messages with a text or numeric value you specify, such as “No”, “Not Available”, “No Data”, etc.

In the example below, we are trying to match student Ivan’s exam results from a table. But since there is no data on Ivan in the table, the function generates an error.

In order to replace the error message with some more formal meaning, the IFERROR function will help us :

= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0), ”No data”)

The IFERROR function checks if an error is generated as a result of calculating the formula, then it outputs the value that you specified in the IFERROR function , for example “No data”.

PM Yojana

Graduation Course

Sarkari Yojana

India Top Exam

Excel Tutorial

Spread the love

VLOOKUP function in Excel | Excel VLookup Function uses | VLOOKUP function 2024

Download Example file