Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Adobe Photoshop Elements 11 – 12 Hours Video Training Course
        • Adobe Photoshop Elements 10
        • Adobe Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • PowerPoint
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010 – 9 Hours Video Training
      • Windows
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Pro 2021
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Introduction to Python
      • Java
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
    • Data Analysis
      • Introduction to Alteryx
      • Power BI
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > HLOOKUP in Excel

HLOOKUP in Excel

The HLOOKUP function is the not as cool younger sibling of VLOOKUP. Learning how to do a HLOOKUP in Excel is the same as a VLOOKUP, the data is instead looked up horizontally rather than vertically.

There is a LOOKUP() but this is only hanging around as it’s compatible with old versions of Excel. The functions you really need to know are VLOOKUP and HLOOKUP and soon, you’ll want to learn all about XLOOKUP (yes, it’s as cool as it sounds).

So what do HLOOKUP and VLOOKUP actually do?

You’d use either to look for a particular value in a table or array of data and then return a corresponding value. In this tutorial, we’ll look at how to do a HLOOKUP as there are loads of VLOOKUP articles and videos available but not as many for HLOOKUP.

For example, in the table below there is a set of data that lists sales figures, company, city, and salesperson. In this example, I want to know where the salesperson Hugo Matthews is located as highlighted:

Two things about this:

  1. With a small dataset, HLOOKUP or VLOOKUP is less useful. I can see the information I need without too much trouble here. But imagine there are thousands of salespeople and hundreds of rows of data. It could get pretty useful.
  2. In the real world, I have this data the other way round and I’d be using a VLOOKUP. Data arrays or tables work a lot better vertically than horizontally.

So, how would I return the value “Edinburgh” using a HLOOKUP? It would go something like this:

=HLOOKUP([select the thing I am looking up],[select the table or array of data],[select how many rows down I want Excel to look], [select exact or approximate match]

Here’s what that looks like using this example and I imagine at the moment you’re wondering what on earth is going on. I’ll explain…

1. Select the thing I am looking up

Excel needs to know what it is looking for. In the example, I’ve written out the name of the person I am looking for (Hugo Matthews) in cell D10. So I’m pointing Excel towards D10 as the place it needs to look for.

2. Select the table or array of data

Next, I need to tell Excel where to look. In this example, I’ve selected the array of data A2:K5. I’ve done that by dragging the cursor from A2, to K5. A different (and arguably better) way to do it is to make your data array a table and just reference that table.

3. Select how many rows down I want Excel to look

The next argument in the HLOOKUP function is telling Excel how many rows down to look. In a HLOOKUP Excel will always look for the item you are looking up in the first row of data. Likewise, for a VLOOKUP, it will always look in the first column. With this next part of the puzzle, you are telling Excel how many rows down from that first row you want it to look. In the example we’re using, we want to look 3 rows down to return the city (the first row counts as one!).

4. Select exact or approximate match

For me, this last argument in the HLOOKUP function is used the least. This is the exact or approximate match function. 99% of the time you want an exact match, i.e. you want Excel to exactly match what you are looking up in your table. To choose that you choose False or type 0.

Hopefully that all makes sense. Test it out on a smaller dataset before you start on larger data.

There are a few things to keep in mind about VLOOKUP and HLOOKUP, specifically a few pitfalls to avoid and consider.

  • Multiple entries. If you have multiple entries for the item you are looking up you may not get the answer you are expecting. For example, if Hugo Matthews appeared multiple times in my data then the HLOOKUP would return the value from the first Hugo Matthews it came across (working from left to right). Likewise, with a VLOOKUP it works top to bottom.
  • It can break. In my experience, it’s not the most robust of functions, especially when used in conjunction with other functions and on large datasets. You may want to consider PivotTables, XLOOKUP, or INDEX MATCH as an alternative.

On INDEX MATCH. It’s superior to both VLOOKUP and HLOOKUP in a few ways, so try to add that one to your toolkit as well. Below is a detailed look at our top 10 Excel formulas and functions and INDEX MATCH features alongside VLOOKUP and HLOOKUP


If you want a shorter tutorial, here’s a rundown from one of our Excel courses on HLOOKUP and VLOOKUP:

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses.

Most Popular Posts

  • How To Use Microsoft To Do: Microsoft To Do Tutorial
  • Microsoft Teams Tutorial – Getting Started.
  • HLOOKUP in Excel
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

Join our list to receive 4 Free Courses!

Simon Sez Business Banner

Similar Posts

Setting up Payroll Deductions in Quickbooks Pro 2012

How to Mail Merge in Word

The Most Used Powerpoint Shortcuts – Download

How to Receive Payments in QuickBooks 2019

Navigating the Workspace in Microsoft Project 2016

Manual Mail Merge Setup in Word 2010

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2021 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)