Excel Tutorial

Excel Tutorial Shortcut Keys in Excel Formatting in Excel Notes in Excel Formats in Excel Cells and Ranges in Excel Excel Function and Formulas Conditional Formatting in Excel Data Validation in Excel Charts in Excel Excel Ribbon Toolbar Basics of Excel Spell Check in Excel Data Analysis in Excel AutoFill in Excel Goal Seek in Excel Solver in Excel Pivots Table in Excel Go-To Special function in Excel Blank cells in Excel Count Cells with Text in Excel Date and Time in Excel-VBA Dependent Drop-down List in Excel Operators in Excel Dependent Combo box in Excel VBA Error Bar in Microsoft Excel Excel Axes Excel File using Password Excel Unique Values Frequency Distribution in Excel Gauge Chart in Excel Histogram in Excel Sum Every Nth Row in Microsoft Excel SumIF Formula in Microsoft Excel Multiplication in Excel Unique Values in Excel Trendline in Excel Excel Themes Copying formula in Excel Check Marks in Excel Calculating the Last Day of the Month in Excel Calculating Age in Excel Insert Row in Excel

Functions

Excel MAX() Function Excel INT() Function Excel MOD() Function Excel ROUND() Function Excel ROUNDUP() Function Excel AVERAGE() Function Excel COUNT() Function Excel COUNTA() Function Excel COUNTBLANK() Function Excel MIN() Function Excel EDATE() Function Excel EOMONTH() Function Excel HOUR() Function Excel MINUTE() Function Excel SECOND() Function Excel TIME() Function Excel WORKDAY() Function Excel WORKDAY.INTL() Function Excel DAYS() Function Excel WEEKNUM() Function Excel WEEKDAY() Function Excel SMALL() Function Excel LARGE() Function Excel LEFT() Function Excel RIGHT() Function Excel MID() Function Excel FIND() Function Excel SEARCH() Function Excel EXACT() Function Excel SUBSTITUTE() Function Excel TEXT() Function Excel VALUE() Function Excel AND() Function Excel OR() Function Excel IFERROR() Function Excel IF() Function Excel Nested IF’s Function Excel IFNA() Function Excel COUNTIFS() Function Excel VLOOKUP() Function Excel HLOOKUP() Function Excel INDEX() Function Excel MATCH() Function Excel OFFSET () Function Averageif Function in Excel

How To

How to import Microsoft Access data into the Microsoft Excel How to use TODAY function in Excel How to Alphabetize in Excel How to remove duplicate values from excel How to lock cells in Excel How to create drop down in excel How to Delete Row in Microsoft Excel How to Highlight Duplicates Words in the Microsoft Excel How to print titles in Excel How to make use of the Wildcard in Excel How to Make Use of the F-Test in Excel How to make use of the Excel Autofit in Excel How to generate random numbers in Excel How to apply Advanced Filter in Excel How to use Index and Match in Excel

Misc

Absolute Value in Excel Adding Column in Excel Converting Units in Excel Count Characters in Excel Custom Sort Order in Excel Decimals in Excel Division in Excel Locate Maximum Values in Excel Nearest Multiple in Excel Paste Options in Excel Quarter Dates in Excel Row Difference in Excel Separate Strings in Excel Reverse List in Excel Array Formula in Excel What if Analysis Data Table in Excel Excel Shortcut Keys What is a spreadsheet in Excel?

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 [email protected] Marketing $1,378
774 Pooja Rani [email protected] Sales Executive $920
650 Aashna Malhotra [email protected] Business Analyst $936
624 Lakshman Maradapa [email protected] Engineering $806
873 Ekta Gupta [email protected] CSE $1,218
818 Nikit Mahiwal [email protected] Engineer $1,206
828 Meenakshi Jaiswal [email protected] CSE $1,007
925 Pragati Patel [email protected] Sales $1,479
721 Rohit Rawat [email protected] CSE $866
Lookup Value Aashna Malhotra Formula Text
Email ID [email protected] =VLOOKUP(C52,C56:F67,2,0)
Salary $936 =VLOOKUP(C52,C56:F67,4,0)