vlookup Excel Function- Usage, Examples and Tutorial

Vlookup will follow the similar approach. Look for the given date in the first column of the table and then returns the respective value for the given column.
What if the gold rate is not captures for the given date. What you will tell, if a date is missing in the data, we will tell the nearby value right? Even vlookup follows the same approach, for that you have tell vlookup to return approximate value if not found. We will see this with example in the below topics.

Why vlookup is used? How to use it in Excel?

While dealing with the data, many cases we required to look for a data and see what are the values in different cases. And then we will tell the story based on the comparisons.

Suppose, you got the retail sales data for different cities and stores. If you want to tell which city or store is performing well? You need to look for the respective data and write the insights based on results. Vlookup is the wide and most useful functions in the Excel for any Analyst. We can’t imaging Excel Application without having Vlookup function.

Why Vlookup returns na (#N/A), #Value, #Ref,#Name, #Error type errors?

Vlookup returns the following errors in different situations. It is important for us to understand when we get these issues.

#N/A: Vlookup returns #N/A if it is not found the exact match
#VALUE : You will see #VALUE error value if you enter column index number which is less than 1.
#Ref: And you will see #Ref error value, if you enter column index number which is more than number of columns in the table array range in your data sheet. Or any wrong reference.
#Name: You will see this error very rarely if your lookup value itself an Error.
Zero: This not an error, if Vlookup founds blank, it will return 0.

And we will see the below examples to deal and handle the Errors while using Vlookup.

Excel VLOOKUP Function Syntax and Definition

Here is the Vlookup syntax:
VLOOKUP(lookup_value, table_array, col_index_num,

[range_lookup])
• lookup_value : Lookup value is a value which you wants to search in the first column of a range array table and wants the respective row values to be returned. If excel finds lookup value in the table array it will return the respective row value of the given column in the range. Otherwise it will return #N/A error.
• table_array: table array is a range in Excel worksheet which user wants to search the lookup value in the first column of this table array. Please note: Vlookup function is not a case sensitive.
• col_index_num: Column index number in the Vlookup formula to tell Excel vlookup from which column of the given array to be picked a row value of the lookup value.
• range lookup : Range lookup parameter is to specify whether user required an exact match values or an approximate match values. If you omit this parameter, Vlookup function treat it as TRUE as default.
Vlookup Syntax and Definition

Excel VLOOKUP Quick tutorial explained with examples

Here is the quick tutorial and complete guide to understand the Vlookup function and its usage. This step by step tutorial will help you to completely understand vlookup functions and how to use in Excel. Here you can see the simple examples to understand vlookup excel formula and Vlookup in VBA. We have provided screen-shots to show how to implement vlookup in spreadsheet in practical approach.

Excel VLOOKUP Quick tutorial explained with examples

Excel VLOOKUP Examples and Applications

Here are numerous examples on Vlookup function, this will show you how to use vlookup Excel formula in various situations.

Vlookup Simple and Sample Examples:

1 Simple Example on Vlookup: This a simple Example to quickly understand the usage of Vlookup formula in Excel.
2 Vlookup function using Named Ranges
3 Vlookup function and conditional formatting
4 Vlookup function and

Vlookup with different Combinations

1 If vlookup Example: In many cases we cross check a cell value or lookup value and we decide to print the values in a range. This possible with IF and Vlookup functions to check if a lookup value it satisfy a condition then get some value, otherwise something else.
2 If vlookup Example- Advanced Example: This will help you to understand the various applications of If and Vlookup functions.
3 IfError vlookup Example: This will help you for Error handling Excel Vlookup function. You can decide if Vlookup returns an Error Value based on your requirement.
4 If iserror vlookup Example: IsError and Vlookup function will help you to determine if the vlookup returned value is Error or Not.
5 If isna vlookup Example: Vlookup returns #N/A value if it is not found the match in the data for given lookup value. IF+ ISNA + Vlookup will help you to decide in such type of situations.
6 Reverse or Left Vlookup: Using Vlookup it is not possible to look for a value in left side. We can use combination of other function to achieve this.
7 Vlookup with multiple criteria: Vlookup directly not supporting for multiple criteria, we need to use combinations of other function to do this.

Nested Vlookup Function:

1 Nested vlookup: Some time we may want to use the returned value of vlookup as lookup value. In that case we can go for Nested vlookup function. For example, if you want the department of product and total sales of that department.
2 Nested Vlookup Examples: Here we will see the more examples and usage of Nested Vlookup function.

Dynamic Vlookup Function

1 Dynamic vlookup: You create dynamic vlookup function, if your lookup value or range changes or not fixed. You drop-down or Offset, Index and Match function and create dynamic vlookup function.
2 Dynamic vlookup function with Offset Function:
3 Dynamic vlookup function with Data Validation
4 Dynamic vlookup function with math and Index function
5 Dynamic Nested Vlookup Function
6 Dynamic vlookup function with Drop-down Controls
7 Dynamic vlookup function with List Box
8 Dynamic vlookup function with Option Radio Button
9 Dynamic vlookup function with Check Boxes

Vlookup between Two Columns, Sheets and Workbooks

1 Vlookup between Two Columns These examples will show how to use vlookup function between Columns.
2 Vlookup between Two Worksheets These examples will show how to use vlookup function between two Worksheets.
3 Vlookup between Two Workbooks: These examples will show how to use vlookup function between Workbooks.
4 Advanced vlookup Examples: Here some advanced Vlookup function examples and applications.
5 vlookup Sum: vlookup and Sum example will show you how to Sum the values based on the vlookup function returned value.
6 Conditional Vlookup: Conditional vlookup is possible with combination of function, here are the example on conditional Vlookup function.

Vlookup VBA usage and Examples

1 Vlookup VBA – Simple Example: You can use Vlookup function in VBA by using application object. This will reduce lot of coding efforts.
2 Vlookup VBA – Variables: You can use Vlookup function in VBA by using application object. This will reduce lot of coding efforts.
3 Vlookup VBA – Dynamic: You can use Vlookup function in VBA by using application object. This will reduce lot of coding efforts.

Vlookup Error Handling

1 Vlookup #N/A Error: This will show you a simple example situation of facing na error in vlookup formula.
2 Vlookup #N/A Error Handling: This example will show you how to handle the na error cases

vlookup vs hlookup

Vlookup helps us to look for the values from left to right. And Hlookup will help us to look from top to bottom. Here we will see what Vlook is and how is it different from Vlookup?
What is hlookup and comparison with Vlookup: Detailed explanation and comparison of Vlookup and Hlookup functions.
vlookup and hlookup examples: Fully explained examples to understand Vlookup and Hlookup.

Alternative to vlookup

What is we do not have Vlookup function in Excel, or you do not like to use it. You have the alternative functions to get the same kind of results.
Index match as vlookup: Here we will see how we can use Index + Match functions in place of Vlookup function.
Index match as vlookup – Examples: We will see detailed examples on using Index and Match functions as Vlookup function.

Offset and Vlookup: This another alternative for Vlookup function,
Offset and Vlookup- Examples: We will see detailed examples on using offset functions as Vlookup function.

Vlookup Rules, Limitations and Best Practices

Rules, Limitations : There are some limitations, Rules to follow while using Vlookup functions. We will see the vlookup function limitations here are.
Best Practices: And the best practice will help in mastering Vlookup Function.
Vlookup Text and Number Issues:
Use of Clean Trim Function with Vlookup:

Vlookup Demo Live and Interactive online Examples

And finally, here is the interactive spreadsheet to understand Vlookup function. Go, explore and practice vlookup formula with this interactive Excel Spreadsheet till you mastering it.