Mastering Lookups in Excel: VLOOKUP, HLOOKUP, and XLOOKUP Explained

When working with large datasets in Excel, one of the most common challenges is searching for information quickly and accurately. That’s where lookups come in. Functions like VLOOKUP, HLOOKUP, and XLOOKUP save hours of manual searching and help you retrieve the exact data you need in seconds.

In this article, I’ll walk you through these powerful lookup functions using a realistic dataset and simple exercises, so you can start applying them in your own work right away.

---

Watch the video with explanation:

The Dataset Setup

I created two dummy data sheets for this demonstration:

1. Product Data Table – Includes fields such as:
   • Product ID
   • SKUs (Stock Keeping Units)
   • Product Name
   • Category & Subcategory
   • Unit Cost and Unit Price
   • Stock Quantity
   • Reorder Point
   • Supplier Name
   • Lead Time in Days
   • Location (storage facility)
   • Unit of Measurement
   • Last Received Date
   • Active/Inactive Status
   Not all fields will be used for the exercises, but the dataset is realistic so you can easily relate to real-world applications.
2. HLOOKUP Table – A simple matrix with rows (1, 2, 3, 4) and values for demonstrating the difference between vertical and horizontal lookups.

---

VLOOKUP – Vertical Lookup

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a table and returns a value from another column in the same row.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

• Let’s say you want to find the Unit Price for a specific Product ID.
• You enter the Product ID in one cell, and with VLOOKUP, Excel will pull the corresponding Unit Price from the Product Data table.

👉 The power of VLOOKUP is its simplicity, but remember—it can only search vertically (top to bottom).

---

HLOOKUP – Horizontal Lookup

HLOOKUP stands for Horizontal Lookup. Instead of searching vertically, it searches for a value in the first row of a table and returns a value from another row in the same column.

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Example:

• Suppose you have a table where months are in the first row, and below them are corresponding sales numbers.
• With HLOOKUP, you can pick a month and instantly get the sales figure for that column.

👉 Think of HLOOKUP as the horizontal cousin of VLOOKUP.

---

XLOOKUP – The Modern Game-Changer

XLOOKUP is the new and improved lookup function available in modern versions of Excel. Unlike VLOOKUP and HLOOKUP, it’s more flexible and overcomes their limitations.

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Why XLOOKUP is Better:

• Can search both vertically and horizontally.
• Allows exact match by default (no more confusion with TRUE/FALSE).
• Lets you return multiple values (entire rows or columns).
• More robust error handling with [if_not_found].

Example:

• Instead of being limited to the first row or column, you can directly specify the lookup array and the return array.
• For example, find a Product Name by Product ID, or return the entire product record in one formula.

👉 If you’re using a recent version of Excel, XLOOKUP should be your go-to lookup function.

---

Key Takeaways

• VLOOKUP: Use when your data is organized vertically and you want to fetch values by column.
• HLOOKUP: Use when your data is organized horizontally and you want to fetch values by row.
• XLOOKUP: Use when you need maximum flexibility—it can handle both vertical and horizontal searches, with better error handling.

By mastering these three functions, you’ll save time, avoid errors, and handle large datasets with ease.