HLOOKUP Function in Excel – Formula Explained with Examples

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

What is the use of HLOOKUP function?

HLOOKUP is helpful to get the values from the lookup range, it search from top to bottom in Horizontal approach. VLOOKUP is useful for getting the data for lookup values by searching vertically. whare as HLOOKUP searches in Horizontally.

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


H in HLOOKUP stands for Horizoantal and HLOOKUP Function in Excel searches horizontally for lookup value and returns the value in a given row ( as per row index mentioned in the formula) that matches a value in the top most row of a table.

What is the syntax of HLOOKUP function?

=HLOOKUP(lookup_value, table_array, row_index_num

[,range_lookup])

  • lookup_value: The value to be searched in the top most row of the array.
  • table_array: The range or a range name containing the table of data.
  • row_index_num: The row number in table_array from which you return corresponding matching value
    [range_lookup]: Whether to find an exact match.
    True or 1 = Closest match is returned
    False or 0 = Exact matches are returned

Examples on HLOOKUP Function in Excel

Let us see some examples to understand HLOOKUP formula.

The data shows Square and Cube values in the rows for the columns 1 to 5.

Example 1:

=HLOOKUP(4,B4:F6,2,FALSE)

HLOOKUP Looks for the lookup value (4) in the top most row of the lookup range (first row, ie; B4:F4) and returns its corresponding value in the second row (as we mentioned row index as 2 in the HLOOKUP formula). So the result will be 16 (value found at 4th column, 2nd row)

Example 2:

=HLOOKUP(4,B4:F6,3,FALSE)

HLOOKUP Looks for the lookup value (4) in the top most row of the lookup range (first row, ie; B4:F4) and returns its corresponding value in the third row (as we mentioned row index as 3 in the HLOOKUP formula). So the result will be 64 (value found at 4th column, 3rd row)

Example 3:

=HLOOKUP(6,B4:F6,3,FALSE)

HLOOKUP Looks for the lookup value (6) in the top most row of the lookup range (first row, ie; B4:F4) and it should return its corresponding value in the third row (as we mentioned row index as 3 in the HLOOKUP formula).

This formula returns #NA as it does not found any match for lookup value in the lookup range (6 not found in in the table array or range B4:F4 ).

Example 4:

=HLOOKUP(6,B4:F6,3,TRUE)

HLOOKUP Looks for the lookup value (6) in the top most row of the lookup range (first row, ie; B4:F4) and it should return its corresponding value in the third row (as we mentioned row index as 3 in the HLOOKUP formula).

HLOOKUP returns 125 as 6 is not found in the lookup array (first row, ie; B4:F4) and we mentioned TRUE as match range type in the formula, So HLOOKUP wil search for nearest lookup value. 5 is closer to 6 and it returns its corresponding value in the third row i.e; 125.

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 14th, 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. stella November 15, 2013 at 9:59 PM - Reply

    sorry, i thought the table array is B4:F6 not B5:F7? I tried with table array B5:F7, the results are different.

    • PNRao November 16, 2013 at 12:22 AM - Reply

      Yes – you are correct, I corrected it.

      Thank you very much -PNRao!

Leave A Comment


Related pages


project costing template excelconvert excel column width to inchesmatch function in excellock excel workbookplanner template excelif iserror functionpowerpoint macro examplesvba examexcel password protected file forgot passwordhow do you delete extra pages in excelmacro programing in excelhow to delete duplicate values in excelunprotecthow to create a sales dashboard in excelhyperlink in excel 2007msgbox excel vbascheduling template for excelcommand button in excel 2007outlook attachment vbanested if excelvb script interview questionsunlock password protected excel workbookmultiple if then statements in excellistbox with columnsvba macro examplesdynamic pivot tablesddl dml dcl tcl commandsdata analysis tab in excel 2007ms excel formula with exampleexcel vba softwareexcel delete rangehow to remove hyperlink from excelsum if in excelunprotect excel passwordmsgbox codevba copy sheet to new workbookdelete rows in excel using vbawrite text file vbavba application.vlookupdelete column vbaoutlook meeting minutes templatesort excel spreadsheetloops in vba excelhow to do the vlookup in excel 2010how to amalgamate excel spreadsheetsexcel vba loop through rowshide duplicates excelaccess 2007 vba tutorialexcel vba is nothingcall worksheet function from vbavba tutorialsvisual basic excel 2007 tutorialmsgbox new linehr interview questions for freshers with answers pdfactivecell.rowmultiple if statements in excelprotect a worksheet in excelvba connect to oraclems sql interview questions and answers pdfhow to unlock excel passwordhow to unhide excel columnssql interview questions and answers for experienced pdfcell width in exceldelete all blank rows in excelexcel insert tick boxexcel sql query syntaxexcel macro rowshow to convert xls file to xmlhow to find duplicate rows in excelfinding duplicate cells in excelvb6 message boxtrim command in excelvba excel range