Excel VLOOKUP Function

What is VLOOKUP function in Excel?

The VLOOKUP function is one of the Lookup & reference functions of Excel.

It looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.

By default, the table must be sorted in ascending order.

We can find this function in Lookup & reference category of insert function Tab.

How to use VLOOKUP function in excel

  1. Click on an empty cell (like F5).
empty cell

2. Click on the fx icon (or press shift+F3).

fx icon

3. In the insert function tab you will see all functions.

insert function tab

4. Select Lookup & reference category.

5. Select VLOOKUP function

6. Then select ok.

excel VLOOKUP function

7. In function arguments Tab, you will see VLOOKUP function.

8. Lookup value is the value to be found in the first column of the table, and can be a value, a reference, or a text string.

9. Table_array is a table of text, numbers, or logical values, in which data is retrieved. Table_array can be a reference to a range or a range name.

10. Col_index_num is the column number in table array from which the matching value should be returned. The first column of values in the table is column 1.

11. Range lookup is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE.

How to use VLOOKUP function in excel

12. You will see the result in the formula result section.

Examples of VLOOKUP function in excel

Example 1:

Find the unique characteristics of a person in the table

We want to find the height, age, and weight of a person named “Elijah” from the following data

Examples of VLOOKUP function in excel
VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is  22
VLOOKUP(H4,A2:E10,3,FALSE)----->>>>answer is  198
VLOOKUP(H4,A2:E10,4,FALSE)----->>>>answer is  185
VLOOKUP(H4,A2:E10,5,FALSE)----->>>>answer is  175

Example 2:

Is vlookup vertical or horizontal in excel?

The vlookup function searches the data vertically in the table. We use the Hlookup function to search the data horizontally.

Examples of VLOOKUP function in excel
=VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is  22
=VLOOKUP(H4,A2:E10,3,FALSE)----->>>>answer is  198
=VLOOKUP(H4,A2:E10,4,FALSE)----->>>>answer is  185
=VLOOKUP(H4,A2:E10,5,FALSE)----->>>>answer is  175

Example 3:

VLOOKUP is based on column or row numbers?

This function finds the desired data in the table based on the number of columns.

Examples of VLOOKUP function in excel

This function counts the number of cells from the left side and when it reaches the desired number, it displays the result in the output.

=VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is  22
=VLOOKUP(H4,A2:E10,3,FALSE)----->>>>answer is  198
=VLOOKUP(H4,A2:E10,4,FALSE)----->>>>answer is  185
=VLOOKUP(H4,A2:E10,5,FALSE)----->>>>answer is  175

Example 4:

Does the VLOOKUP function search the values ​​on the right or the values ​​on the left?

This function only calculates the data on its right.

Examples of VLOOKUP function in excel
=VLOOKUP(H4,A2:E10,-1,FALSE)----->>>>answer is  #VALUE

Example 5:

What is the difference between an exact match and an approximate match in VLOOKUP?

Example 6:

Does the VLOOKUP function find all the desired items in the table or only the first value?

This function only finds the first case that meets the desired condition. In the example below, there are three nouns called “Noah”, and the Vlookup function only searches for the first one.

Examples of VLOOKUP function in excel
=VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is  25
=VLOOKUP(H4,A2:E10,3,FALSE)----->>>>answer is  205
=VLOOKUP(H4,A2:E10,4,FALSE)----->>>>answer is  235
=VLOOKUP(H4,A2:E10,5,FALSE)----->>>>answer is  200

Example 7:

Combination of two functions VLOOKUP and IFNA

If the output of the Vlookup function gives an error, the IFNA function can be used.

Examples of VLOOKUP function in excel
=IFNA(VLOOKUP(H4,A2:E10,2,FALSE),"not exist")--->>>>answer is  not exist

What is the purpose of VLOOKUP function?

It looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify

What is the Return value of VLOOKUP function?

It can return any type of data.

VLOOKUP(Lookup value,Table_array,Col_index_num,Rangelookup)=number,text

How many arguments does VLOOKUP function have?

VLOOKUP(Lookup value,Table_array,Col_index_num,Rangelookup)=number,text

This function has just 4 Arguments.

Lookup value is the value to be found in the first column of the table

Table_array is a table of text, numbers, or logical values, in which data is retrieved.

Col_index_num is the column number in the table array from which the matching value should

be returned.

Range lookup is a logical value: to find the closest match in the first column (sorted in

ascending order) = TRUE or omitted; find an exact match = FALSE

Range lookup argument of VLOOKUP function is optional.

Which version of Excel supports VLOOKUP function?

This function is available for all excel versions (2003-2019)

Errors in VLOOKUP function

If the VLOOKUP function does not find any items, it returns a not found (#N/A ) error.

Errors in VLOOKUP function
VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is  #N/A

If the input of the function is not typed correctly, it returns a not found (#N/A ) error.

Errors in VLOOKUP function
VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is  #N/A

If extra space is placed at the input, it returns a not found (#N/A ) error.

Errors in VLOOKUP function
VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is  #N/A

If the table range is not entered correctly, it returns a not found (#N/A ) error.

Errors in VLOOKUP function
VLOOKUP(H4,A2:E10,2,FALSE)----->>>>answer is  #N/A

If the Col_index_num is not entered correctly, it returns a not found (#VALUE) error.

Errors in VLOOKUP function
=VLOOKUP(H4,A2:E10,-1,FALSE)----->>>>answer is  #VALUE

Leave a Reply

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