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) |