Logo
×

Excel VLOOKUP() Function

Excel VLOOKUP() Function

The VLOOKUP() function in excel is used to lookup the value on the leftmost side of the table and then return the value in the corresponding row basis on the supplied column index number. 

Syntax

VLOOKUP(LookupValue, Table_Array, Column_Index_Num, [Range_Lookup])

Parameter

LookupValue(required)- This parameter represents the value which needs to be looked for in a particular table.

Table_Array (required)- This parameter refers to the table in which we need to check for this value to be present and from which we want to retrieve data. By pressing the F2 key when you have selected your table, you fix the range. So when you copy the formula from one cell to another, the range does not update automatically.

Index_Num(required)- If the value is present in the table, then which column consists of the value we want the function to return

Range_Lookup(optional)- This parameter takes a Boolean value - TRUE for approximate match and FALSE (which is the default value) for an exact match of the value we are searching for in a table.

Category

Lookup & Reference

Return

This function is used to lookup a particular value in a table and then return data from a specific column corresponding to that value.

Example 1

Objective: Below in the Lookup table, we have cost for different parts for different car makers. Use VLOOKUP() to get costs in front of items in the second table.

Lookup Table Mercedes Audi BMW
Head Light $2,700 $2,023 $3,000
Engine $5,000 $4,999 $5,200
Steering $1,250 $1,302 $1,100
Ignition $250 $301 $200
CYHead $3,010 $290 $310
Maker Spare Cost Formula
Mercedes Ignition £250  =VLOOKUP(B1,$A$24:$B$28,2,FALSE)
BMW Head Light £2,023  =VLOOKUP(B2,$A$24:$C$28,3,FALSE)
Audi Engine £5,200  =VLOOKUP(B3,$A$24:$D$28,4,FALSE)
Audi Steering £1,100  =VLOOKUP(B4,$A$24:$D$28,4,FALSE)
Mercedes Ignition £250  =VLOOKUP(B5,$A$24:$B$28,2,FALSE)
Mercedes CYHead £3,010  =VLOOKUP(B6,$A$24:$B$28,2,FALSE)
BMW Head Light £2,023  =VLOOKUP(B7,$A$24:$C$28,3,FALSE)
BMW Engine £4,999  =VLOOKUP(B8,$A$24:$C$28,3,FALSE)

Example 2

Objective: Find 'Email ID' & 'Salary' based on the lookup value by looking up in table.

ID Name Email ID Department Salary
651 Gautam gautam.partamy@edu.in Marketing $1,378
774 Pooja Rani poja@edu.in Sales Executive $920
650 Aashna Malhotra aasahna.malhotra@edu.in Business Analyst $936
624 Lakshman Maradapa lakshman.maradapa@edu.in Engineering $806
873 Ekta Gupta ekta.gupta@edu.in CSE $1,218
818 Nikit Mahiwal nikit.mahiwal@edu.in Engineer $1,206
828 Meenakshi Jaiswal meenakshi.jaiswal@edu.in CSE $1,007
925 Pragati Patel pragati.patel@edu.in Sales $1,479
721 Rohit Rawat rohit.rawat@edu.in CSE $866
Lookup Value Aashna Malhotra Formula Text
Email ID aasahna.malhotra@edu.in =VLOOKUP(C52,C56:F67,2,0)
Salary $936 =VLOOKUP(C52,C56:F67,4,0)

Related Topics

Goal Seek in Excel

Goal Seek in Excel The Goal Seek Excel function in Excel facilitates an automatic solving approach by adjusting a few pointers to reach a required output. Goal seeking is used when you have a value in...

3 minutes read.

Square Root Function in Excel

The concept of Mathematical calculations includes a large number of numbers and calculations are based on the numbers present in the data. Likewise, Square Root is a function used to...

3 minutes read.

Purpose of Randomize in Excel

Excel spreadsheets are used on a large scale for business and personal usage. It is used to store data, perform multiple calculations, make assumptions based on charts and graphs etc....

7 minutes read.

SUM Functions in Excel

The BODMAS formula plays a vital role in the concept of mathematics. BODMAS rule follows the Bracket of Division, Multiplication, Addition and Subtraction. This rule is followed for a correct...

6 minutes read.

Excel ROUND() Function

Excel ROUND() Function The ROUND() function in excel rounds the numbers to specified digits. Syntax ROUND(Number,num_digits) Parameter Number(required)- This parameter represents the number which you want to round up. num_digits(required)- This parameter represents the number of decimal places to which...

1 minute read.

How to apply Advanced Filter in Excel

It was known that the advanced filter in Microsoft Excel is termed to be the next level filter option that is available in the particular "Data Menu Tab", which is...

7 minutes read.

Frequency Distribution in Excel

The term Frequency Distribution in Microsoft Excel is mainly used to explain how the data will be spread out. And it could be achieved with the help of the Histogram,...

7 minutes read.

Check Marks in Excel

What is a Check Mark? Among various characters checkmark is one of the characters used to indicate that the item or product in the list is correct, chosen or selected. A...

5 minutes read.

Excel MIN() Function

Excel MIN() Function The MIN() function in excel returns the smallest or minimum value from the provided numbers, named range or array. It ignores logical values and text. Syntax MIN(Number1,Number2, ...) Parameter Number1(required)- This parameter represents the...

1 minute read.

Spell Check in Excel

Spell Check in Excel Microsoft Excel facilities the complete features for examining the work with text. It provides the basic properties for the proper functioning of text, including the spell-check property. The...

5 minutes read.

Excel COUNT() Function

Excel COUNT() Function The COUNT() function counts the number of cells in a range that contain numbers. Syntax COUNT([value1], [value [2], ...) Parameter value1(required)- This parameter represents the first cell in the range. value2, …(optional)- It represents the...

1 minute read.

Excel INT() Function

Excel INT() Function The INT() function in excel is used to round a number down to the nearest integer. It removes decimal points from any number value & return rounded down number value in...

1 minute read.

How to import Microsoft Access data into the Microsoft Excel

Microsoft Access is primarily considered vital and the primary tool for individuals who want to create and manage the respective type of Database efficiently. This tutorial will discuss how to import...

5 minutes read.

Unique Values in Excel

The Unique function in Excel filters the list of unique values from the given data like range or array. The data presented is either number, text, date, time etc. This...

3 minutes read.

Text to columns in Excel

Microsoft Excel is widely used in startups to large companies. The data are entered in required rows and columns, and sometimes the data entered in one cell needs to enter...

4 minutes read.

Pivots Table in Excel

What is a Pivot? A pivot table is a tool to create summary reports from data-sets irrespective of their sizes but is helpful even if the data is more than just...

5 minutes read.

What do you understand by Combination Chart in Microsoft Excel?

In Microsoft Excel, an individual has the Combo Chart option available, which can be effectively clubbed into two charts types, which are Column Clusters Chart, Line Chart to get the...

7 minutes read.

How to use TODAY function in Excel

What is TODAY Function in Excel? Today function is one of the types of Worksheet function (WS) in Excel.Today Function belongs to the Date and Time functions.It is used to calculate...

3 minutes read.

AutoFill in Excel

AutoFill in Excel Excel is known for its credibility to operate repeated and similar tasks automatically. For this purpose, it facilitates an inbuilt feature known as Autofill that recognizes the series of...

3 minutes read.

How to Delete Row in Microsoft Excel?

Individuals can quickly delete the respective row from the particular Microsoft Excel whenever they want, according to their needs and requirements. In this tutorial, we will discuss the following things in...

7 minutes read.