LOOKUP Function in Excel – Formula Explained with Examples

Home/Excel Formulas/LOOKUP Function in Excel – Formula Explained with Examples

LOOKUP Function: Explained with Examples

LOOKUP Formula is basic LookUp function in Excel, it is provided for backward compatibility. LookUp function is helpful if you want to look up a value in either in one-row or one-column data range or any array.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


When we have array of values, and if you want to extract the result of a particular value from the data. We can use LookUp Formula to do this. Below is the pictorial representation of LOOKUP Formula.

Here we have List of Weekdays (Sunday to Saturday) and its IDs (1,2,…7). And we can specify the Lookup Value and Lookup Array in the Formula as show n below to lookup the respective value.

Here, A1:B7 is lookup-range and D4 is the lookup_value. And we will get the Wednesday as result for the lookup value(4).

What is the use of LOOKUP function?

LOOKUP Function in Excel returns a value from a table or from an array.

What is the syntax of LOOKUP function?

LOOKUP Function in Excel-syntax
It has 2 different syntax:

  • Syntax #1: LOOKUP(lookup_value, array): It is helpful when we have the single set of range of array with lookup vector.
  • Syntax #2: LOOKUP(lookup_value, lookup_vector, result_vector): It is helpful when we have two different arrays one for lookup vectors and the other one for lookup arrays or range

  • lookup_value: The value you want to look for
  • array: The array of values that contain the value you want to look for
  • lookup_vector: The range of cells that contains the value you want to look for
  • result_vector: The range of cells that contains the value you want to return

Examples on LOOKUP Function in Excel

Syntax #1

LOOKUP function searches for the value in the first row or column of an array and returns the corresponding value in the last row or column of the array.

Example 1:

=LOOKUP(3,{1,2,3,4;”One”,”Two”,”Three”,”Four”})

Looks for 3 in the array and returns its corresponding value i.e; “Three”.

Example 2:
=LOOKUP(3,A5:B9)

Looks for lookup value (4) in the array (first column of the lookup range. i.e; A5:A9) and returns its corresponding value (right or second column of the lookup range. i.e; B5:B9).

Syntax #2

LOOKUP function searches for value in the lookup_range and returns the corresponding value in the result_range.

LookupEx2

Example 3:
=LOOKUP(4,A15:A19,B15:B19)
Looks for 4 in the lookup range (first set of range in the formula i.e; A15:A19) and returns its corresponding value from the result range (second set of the range in the formula i.e; B15:B19)

Example 4:
=LOOKUP(4,A15:A19,C15:C19)

Looks for 4 in the lookup range (first set of range in the formula i.e; A15:A19)and returns its corresponding value from the result range (second set of the range in the formula i.e; C15:C19)

Reference:

Please refer the below article for more Lookup & Reference Excel functions.
Lookup & Reference Excel Formulas

Please refer the below article for more Excel Functions.
Excel Formulas | Home



Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |February 4th, 2013|Excel Formulas|2 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

2 Comments

  1. March 6, 2014 at 1:03 PM - Reply

    I have a given quantity for water consumed in kl eg 50kl per month. The tariff applicable is as follows:
    0 to 6 kl = R6.19 per kl; 7 to 12 kl = R8.84; 13 to 18 kl = R11.61; 19 to 24 kl = R13.43; 25 to 30 kl = R15.35
    31 to 42 kl = R16.59; 43 to 72 kl = R17.75. I need to create a formula for various households. Please help

  2. September 11, 2014 at 9:12 PM - Reply

    please provide

Leave A Comment


Related pages


visual basic tutorial torrentvba selection.copyhow to enable developer tab in excelpowerpoint vba tutorialms excel macrovba collection excelsql query excel worksheetspotfire basicsexcel labelingtask planner excel templateexcel macro introductionvba vbyesnotask management spreadsheet excelexcel vba selection objectsql interview questions and answers in pdfhow to write macros in excellistbox listindexexample of vlookup functionexcel vba programming for dummies torrentsaveas vbaexcel current worksheetsql aptitude questions and answerscluster chart excelmicrosoft excel macro tutorialexcel vba activate workbookexcel macro to merge cellsapplication.wait excel vbasample excel macrosvba buttonsfree excel userform templateshow do you hide a column in excelexcell if statementsinsert button excelcut and paste workbooksexcel vba collectionweekly report template pptvba set workbook namems excel developerwhat is vba macrosexcel protect with passwordexcel sql queriesexcel templates for construction project managementexcel if cell contains numberhide cells excelduplicate cell excelhow do i add the developer tab to the ribbonhyperlink excel vbavba excel programmingunlocking an excel spreadsheetstock chart excel 2010excel macro select sheethow to remove blank row in excelconditional formatting excel 2007 if statementselect case excel vbaexcel formula for alphabetical orderhow do you use vlookup in excel 2010checkbox vba excelvb 6.0 interview questions and answers pdfms excel shortcut keysexcel intermediate tutorialsql sas tutorialestimate worksheet templatesumif example excel 2010activecell.rowexcel vba displayunprotect password protected excel sheetexcel vba paste specialvlookup in excel 2003 with examplevba excel concatenateif vlookup examplefree download gantt chart excelvba word exampleshlookup function in excel 2007open a file in excel vbadynamic chart excelunlocking excel filehow to pivot table excel 2013how to use iferror function in excelexcel 3d surface chartsimple explanation of vlookuphow does the vlookup function workexcel macro templatestrim function in excel