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:

=VLOOKUP(Lookup_value,table_array,col_index_num,[range_lookup])
Argument:

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

 

#N/A

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 with VLOOKUP

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: 

Table1: USA_MARKET

Table 2: UAE_MARKET

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.

 

Recommended For You

About the Author: Mehul Vaghela

Howdy Math fan. I'm Mehul Vaghela - B.E.Mechanical. Enthusiastic and holds the passion of reading, writing, and learning. In 10 years of experience on the field, I've faced so much problem with managing data and learned to make that happen. ExcelElixir is one of the parts of passing it on (knowledge).

Leave a Reply

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