VLOOKUP in Excel with example | LOOKUP Function

Dear friends, I know you will have a few questions in your mind for VLOOKUP like what is VLOOKUP? , How do we use VLOOKUP in Excel? and, How do we do VLOOKUP in excel from another sheet in Excel? etc..

Here you will find your answer from below Tutorial.

VLOOKUP: VLOOKUP is an Excel function to retrieve data from a specific column in the table. The “V” stands for “vertical”. The value for which we need to retrieve data from table must appear in the first column of the table, with lookup columns to the right.

For using VLOOKUP it is required to have a lookup table with lookup values in the leftmost column. The corresponding result values can appear in any column towards the right of lookup values.

VLOOKUP Formula Syntax:


Lookup_value: The value to look for in the first column of a table.

Table_array: The table from which to retrieve a value.

Col_index_num: The column in the table from which a value need to retrieve.

Range_lookup: It is optional,

TRUE=approximate match

FALSE= exact match

VLOOKUP in Excel with an example:

example of vlookup in excel


In the above tutorial, we need to retrieve a value of salesperson from column C. For that we use VLOOKUP in Cell G5. By using VLOOKUP we can find a value for the name entered in F5. It is a very useful formula through which we can find specific value from the large table.

Buddy, VLOOKUP only looks right!

excel vlookup example



If VLOOKUP cannot find a match it returns a #N/A error.

VLOOKUP NA in excel

Value of G5 is not available in the lookup column, the result is an error as #N/A.

Due to #N/A error in one cell Total sales value also results with error #N/A.

IFNA in conjunction with VLOOKUP

We can use IFNA in conjunction with VLOOKUP to replace the error #N/A with desired value or user-friendly message.


IFNA along with VLOOKUP is used to replace #N/A error with the specified value. This specified value can be treated as a Zero.

Multiple lookup tables

There can be multiple lookup tables instead of one. We can use IF Function to check whether a condition is met, and return a one lookup table if TRUE and another lookup if FALSE.

Step 1: Create two named ranges: 



Step 2: Use the VLOOKUP Function in cell E2.

Multiple Lookup table

The bonus of salesperson depends on the market (UK or USA) and Achieved sales target. IF B2=USA, VLOOKUP Function uses Table 1 named USA_MARKET, if B2=UK, VLOOKUP Function used Table 2 named UK_MARKET.We need to set forth argument of VLOOKUP function to TRUE as an approximate match.

Similarly to find value from the raw we can use HLOOKUP.


Leave a Reply

Your email address will not be published. Required fields are marked *