Open In App

VLOOKUP Function in Excel

Last Updated : 19 Mar, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

Excel is widely used for data handling and almost everyone nowadays uses Excel heavily. At the same time, it is very user-friendly. Excel has several functions and formulas to make tedious jobs very easy. Like the SUMIF() function, VLOOKUP() is another very useful function that is widely used to ‘look up’ any data from a long table or a range by row. It makes the work a lot easier and saves time.

In the below article, we will learn more about the VLOOKUP Function and how to use it.

vlookup excel

What is a VLOOKUP Function?

It is a widely used function in Excel and is used for looking up any required data from a list of large data sets. It supports approximate or exact matches based on one of its arguments being ‘True’ or ‘False’. This function also supports wildcards( ‘*’ and ‘?’) for partial matching.

VLOOKUP() function is the premade function that allows searches across columns. Generally, the VLOOKUP() function is used to find data from a table, a list, or a range. It has two features – Approximate match and Exact match. 

Note: User must set the LOOKUP -value in the first column of the selected table as VLOOKUP() always searches in the right of the value and returns the desired output.

What is VLOOKUP Formula

The Formula for Vlookup function is:

VLOOKUP(lookup_value, Table_array, col_index_num,[range lookup])

Arguments of Excel’s VLOOKUP Formula:

Arguments used are defined as:

  • lookup_value : This is the lookup value that should be provided by the user and the data you want to look up. This is a mandatory argument.
  • table_array : This is the table where the lookup_value and our desired output both exist. VLOOKUP() function uses this table_array argument to find the result. This is a mandatory argument.
  • col_index_num : This is the column number of the table_array where VLOOKUP() will search for its output. If it gets the result, it returns the specific value.This is also a mandatory argument.
  • [range_lookup] : The user may or may not provide this according to his or her needs. This argument’s value may be True or False. If
    • ‘True’, it means an approximate match
    • ‘False’, it searches for an exact match.

Note: If nothing is specified by the user, by default this value is set to be ‘True’ and it searches for an approximate match. This argument is completely optional.

Return Value of Excel’s VLOOKUP Formula

This function searches the corresponding value of the given argument and returns the desired matched value from the table or range provided as an argument.

Things to Remember: The column which holds the data used to lookup must always be to the left.

How to Use the VLOOKUP Function in Excel

The VLOOKUP Function is very easy to use in Excel by following the below mentioned steps:

Step 1: Select a cell

Step 2: Apply Vlookup

Type ‘=VLOOKUP‘ without quotes

Step 3: Double-click the VLOOKUP command 

Use the VLOOKUP Function to find the Student names based on their Roll no.

VLOOKUP Function

Step 4: Select the cell and Put a comma

Select the cell where the Search value will be entered. G4 is the cell where the result will be displayed. In this case, the student names based on their roll nos. and G3 is selected as the LOOKUP value. Enter a comma.

Step 5: Mark table Range and Put Comma

Now select the table range for the function. This means mark from where to where. The range of the table is marked at table_array (in this the table is selected A1:D9). Put a comma again(,).

Mark table Range and Put Comma

Step 6: Type the number of columns, counted from the left

The number 2 is entered as col_index_number. This is the Second column from the left and is the data that is being looked up.

Step 7: Type 1 or 0 and Press Enter

Type True or 1 for Approximate Match and False or 0 for Exact match.

Type 1 or 0 and Press Enter

Step 11: Preview Result

Now, you can preview the result. The function returns the #N/A value. This is because there have not been entered any value to the Roll_no.

Screenshot-(166)

If we give roll no value of 1015 then the result will be:

The VLOOKUP function has successfully found the Student name which has roll number 1015.

Screenshot-(167)

Excel’s VLOOKUP Function Examples

Let us understand the above steps better with the help of examples. There are a few examples of VLOOKUP discussed below.

1. How to Find VLOOKUP with Error in Excel

We have a dataset of students with their Roll Number, Student Name, Age, and Marks obtained. We will now see how to use the VLOOKUP function.

Dataset of students with their name, age, roll no, marks

We will use the VLOOKUP function to find the Student’s name on the basis of their Roll number.

1. Select a cell (G3)

2. Type ” =VLOOKUP ” and Double click the VLOOKUP command.

3. Select the cell where search value will be entered (G4) and Type (,)

4. Mark table range (A1:D10) and Type (,)

5. Type the number of the column, counted from the left (2)

6. Type True (1) or False (0) and Press Enter

Formula used : =VLOOKUP(G3,A1:D10,2,1)

Using VLOOKUP formula to LOOKUP for G3 value.

This formula is showing an Error because the LOOKUP value G3 does not contain any value. 

Now we will look up for value after entering a value in cell G3.

Using LOOKUP function to find Exact function.

Now the result for the LOOKUP Value is Vineeta Tiwari.

2. How To Find an Exact Match Using VLOOKUP in Excel

Here, we will see an example of the exact match of the VLOOKUP() function.

For finding an exact match in the VLOOKUP Formula, in the place of last argument ” false ” will be typed.

VLOOKUP for Exact Match.

Here in the above image, we have used the VLOOKUP formula to search for the student name with the roll number 1014, and we have found the result Dinesh Shulka.

3. How To Find Approximate Match Using VLOOKUP in Excel

In this case, when an exact match is not found VLOOKUP() function will return the value of the next greater element. The following is an example.

The only difference in finding approximate match with that of an Exact match is instead of False in the argument, ” True” is written.

VLOOKUP for approximate match.

Here, the 1018 roll number does not exist. So it returns the student name of roll number 1017(i.e. the next greater element in the list).

4. VLOOKUP Example of First Match, Case Insensitive & N/A error in Excel

This function always returns the first matched element. It is at the same time case-insensitive. And if we are looking for an element’s exact match while the element is absent in the table this function will show #N/A.  All the three cases are shown in the below figure.

Note: This function always searches rightwards to the lookup_value.

First Match, Case Insensitive & N/A error

How to Use VLOOKUP for Multiple Criteria?

In order to use VLOOKUP Function for Multiple Criteria, follow these steps:

Step 1: Right- click on the column header and click “Insert”. This will insert a column left of the Students name.

MC1

Step 2: Name this column as “Student Name and Age” and enter the formula =C2&”-”&D2 and drag down to the rest of the column.

MC2

The concatenated column has been successfully obtained.

MC3

Step 3: Now, we enter the formula “=VLOOKUP(B15&”-”C15,B1:E9,4,FALSE)” and look for the marks obtained by Amod Yadav-21.

MC4

Result: We will get the following result.

MC5

How to Use VLOOKUP Across Multiple Sheets

To use VLOOKUP across multiple sheets in Excel simply follow these steps:

  • First, open both the worksheet that has the data you need and the one where you’ll display the search results. Look for a unique identifier that both worksheets share, like Student’s Name or Roll Number, to serve as your search key.
  • Next, in the worksheet intended for the results, type the VLOOKUP formula into the cell where you aim to bring in the data. Within this formula, you need to define
    • the search key
    • the cell range in the original worksheet where this key can be found,
    • the column number from which you wish to pull the data.
  • Also, you must indicate the full range of cells containing the data in various sheets, ensuring these elements are properly separated by commas.
  • Hit Enter after typing your formula to view the search result in the specified cell. This simplified explanation helps you utilize the VLOOKUP function across multiple worksheets effectively, by establishing a clear step-by-step process without diving into overly technical jargon.

Common Errors in VLOOKUP Function

  • #N/A! error – This means that the VLOOKUP function couldn’t find what you were looking for.
  • #REF! error – This happens when the formula refers to cells that aren’t there, or when it tries to use a column number that’s too big for the table.
  • #VALUE! error – This occurs if the column number isn’t a number or if the “range_lookup” value isn’t either “TRUE” or “FALSE”.

What are the Limitations of VLOOKUP in Excel

Some of the Limitations of VLOOKUP in Excel are:

  • VLOOKUP may give wrong answers if the search term’s case doesn’t match the data.
  • It only shows the first match and requires data to be sorted from smallest to largest.
  • Mistakes or empty cells in the data can also lead to incorrect results.

Also Read:

Conclusion

In the above article we learned what is a VLOOKUP Function in Excel and why it is needed in Excel. We also discussed on its formula, Syntax and how to use Vlookup Function along with its different cases. Using a VLOOKUP Function is quiet easy but makes the other tedious jobs easier.

VLOOKUP Function – FAQs

What is the VLOOKUP function in Excel?

The VLOOKUP function stands for Vertical Lookup.  VLOOKUP function is a Powerful feature in excel which allows you to search for  specific value in the leftmost column of a table and  gives a corresponding value from a  specific column. 

Can VLOOKUP search for values horizontally?

No, VLOOKUP is designed to search for values vertically in a column. To search for values horizontally, you can use the HLOOKUP function.

What is the range limit for VLOOKUP?

The range limit for VLOOKUP in Excel is approximately 1,048,576 rows, which is the maximum number of rows allowed in an Excel worksheet.

What is the two-way LOOKUP function in Excel?

When you want to make changes in both LOOKUP value and Column index number then, you have to make both the values Dynamic.

And use MACTH formula with LOOKUP function to Extract the exact values.

Syntax : =VLOOKUP (lookup_value,Match(lookup_value, lookup_array, [Match_type]),[range_lookup) 

What is the double LOOKUP formula in Excel?

This can be also known as Nested VLOOKUP function . 

Syntax : VLOOKUP(VLOOKUP(lookup_value, table_array, Column_index_num,[range]),table_array, column_index, [range])



Similar Reads

Using CHOOSE Function along with VLOOKUP in Excel
In Excel, managing space is a very important point to consider. Now, let's suppose you are in a situation where you want to compare multiple columns in the Excel dataset, and you want to derive results, This is not possible using the VLOOKUP function alone. So, to make a function capable of doing something like this, we will introduce a special fun
2 min read
How to VLOOKUP to Return Multiple Values in One Cell in Excel?
Almost everyone works with lots of data ranging from medium to high volume datasets, and extracting information from such a sheet can become easier, with little to advanced knowledge of excel. Excel is widely used to perform simple to complex data analysis. It is a great tool for generating meaningful & insightful reports for quick decision-mak
3 min read
How to Find Duplicate Values in Excel Using VLOOKUP?
In this article, we will look into how we can use the VLOOKUP to find duplicate values in Excel. To do so follow the below steps: Let's make two columns of different section to check VLOOKUP formula on columns:[caption width="800"]Created Two Columns [/caption] Here is the formula we are going to use:=VLOOKUP(List1,List2,TRUE,FALSE)In this formula,
2 min read
How to Apply Conditional Formatting Based On VLookup in Excel?
VLOOKUP is an Excel function to lookup data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Conditional Formatting Based on Vlookup: 1. Using the Vlookup formula to compare values in 2 different tables and highlighting those values which is only present in table 1 using cond
3 min read
Different Ways to Perform a Case-Sensitive Vlookup in Excel
In Excel datasets, sometimes you must have noticed, that there are many values in a table that are case-sensitive in nature. And if we want a particular type of value from our dataset, but unfortunately that value also has some duplicates, but with different case sensitivity, so here we need to search on the basis of case sensitivity. To make condi
8 min read
How to Return Multiple Matching Rows and Columns Using VLOOKUP in Excel?
VLOOKUP function is a premade (already made by Ms-Excel) function in Excel by which we can search for any information in a given spreadsheet. We can use the VLOOKUP function in two ways, first is VLOOKUP with an exact match and VLOOKUP with an approximate match. VLOOKUP with exact match means that kind of data from which we can able to find same to
4 min read
Excel VLOOKUP with Dynamic Column Reference
Suppose anytime you work with colossal tables of data and you want to implant a VLOOKUP function that dynamically updates to the accompanying portion as you copy it across. In that case, the VLOOKUP with the COLUMNS capacity is what you truly care about. That is; the col_index_num a piece of the VLOOKUP capacity intensely revives as you copy it acr
4 min read
Why INDEX MATCH is Better Than VLOOKUP in Excel
While choosing which vertical query recipe to utilize, most Excel specialists concur that INDEX MATCH is a preferable equation over VLOOKUP. Be that as it may, many individuals actually resort to utilizing VLOOKUP in light of the fact that it's a less difficult recipe. One significant driver of this issue is that a great many people actually don't
14 min read
How to VLOOKUP to Return Blank or Specific Value Instead of 0 or N/A in Excel?
Excel is widely used for data management, and practically everyone utilizes it these days. At the same time, it is extremely user-friendly. Excel offers a number of functions and formulae that may make boring tasks much easier. VLOOKUP() is another extremely helpful method that is commonly used to 'look up' any data from a large table or a range by
7 min read
VLOOKUP On Multiple Criteria Columns Using Helper Method & CHOOSE Function
In this article, we will see how we can combine multiple values, and use them as lookup criteria for searching specific value(s) in the excel dataset. For this purpose, we will use the VLOOKUP formula, the most basic formula to perform a lookup for value(s) on excel. We will see a modified form of VLOOKUP to perform a lookup using multiple values.
4 min read
Article Tags :