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 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • 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
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • 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
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • 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 Online
        • 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
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • 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
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • 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.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • 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

Similar Posts

QuickBooks 2020 – Section 2 Transcript

Bar Graph in Excel — All 4 Types Explained Easily (Excel Sheet Included)

How to Crop a Photo and Images in Adobe Photoshop Elements 2019

How to Randomize a List in Excel? 2 Different Methods

Manually Manage Stacks in Photoshop Elements 10

Understanding Access 2013 Relationships between Tables – Part 2

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
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)