Step-by-step Guide to VLOOKUP vs HLOOKUP & XLOOKUP for Excel and Google Sheets

Spreadsheets are powerful for organizing information, but finding and pulling the right data can take time without the right tools. That’s where VLOOKUP, HLOOKUP, and XLOOKUP come in. These lookup functions in Excel and Google Sheets make it simple to search through rows and columns and return exactly what you need. In this guide, you’ll see how each one works, learn practical examples, and pick up a few tips to use them more confidently.

Before you dive in: the formulas below don’t have to stay in a spreadsheet. Molnify turns the same Excel file into a web app your team can use, and your VLOOKUPs and HLOOKUPs keep working exactly as you wrote them, because the file becomes the app’s build instruction rather than something rewritten in code.

Forty seconds on how that works, or see a live VLOOKUP-powered quote app.

Quick Comparison: VLOOKUP vs HLOOKUP vs XLOOKUP

Each function is explained in detail below, with syntax breakdowns and step-by-step examples.

Understanding VLOOKUP

What is VLOOKUP?

VLOOKUP (or Vertical Lookup) is one of the most widely used functions in Excel and Google Sheets. It helps you find a value in a table by looking it up vertically. It searches down the first column to find a match, then returning a value from the same row in another column.

How VLOOKUP Works (Syntax explained)

The VLOOKUP function follows this syntax:

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

• lookup_value: The value that you want to match or find. VLOOKUP is case-insensitive
• table_array: The range of cells that contains your data
• column_index_num: The index of the column to return the value of. The leftmost column of your table_array is index 1.
• range_lookup: Optional. Enter FALSE for an exact match, or TRUE for an approximate match. This parameter may be omitted, in which case approximate matching is used
  • Use FALSE in most situations where you require exact matches (such as looking up emails or IDs). TRUE is suitable mainly for numerical ranges (such as tax brackets or grades)

For more detailed information, visit Microsoft’s official VLOOKUP documentation.

Step-by-step VLOOKUP

Here’s how to use VLOOKUP, step-by-step.

1. Prepare your data

First, you should prepare a rectangular area in your spreadsheet with your data. Each row in the area should represent a single row of data.

• Make sure your data has clear column headers
• Ensure that the value you want to look up is in the first column of the range
• If using an approximate match, sort the first column in ascending order

Example: You prepare an area where the first column is an email address, the second column a name, the third column a role, and the fourth column a location.

2. Identify what you want to find

Decide what you are searching for and which column has the result that you need.

• Remember that the first column, that contains your lookup value, has index 1

Example: You are looking for the role, which is the column with index 3.

3. Write the VLOOKUP formula

Click into the cell where you want the result. Type =VLOOKUP( and enter:

• the lookup value (e.g. a cell reference or text in quotes)
• the table range (e.g. an area or a named range)
• the column index number
• FALSE for an exact match or TRUE for an approximate match

VLOOKUP in practice

One of the many use-cases for VLOOKUP is item information lookup: the user chooses an item in a dropdown, and VLOOKUP finds the item’s price, weight, favorite color, or anything else.

Many Molnify users make use of VLOOKUP for data lookup. For an example of VLOOKUP fetching item information, see an example sales quote application.

Understanding HLOOKUP

What is HLOOKUP?

When your data is arranged in rows instead of columns, VLOOKUP isn’t always the best tool for the job. That’s where HLOOKUP, or Horizontal Lookup, comes in. It works just like VLOOKUP, but searches across the top row and returns values from rows below.

How HLOOKUP Works (Syntax explained)

The HLOOKUP function follows a syntax very similar to VLOOKUP:

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

• lookup_value: The value that you want to match or find. HLOOKUP ignores case when comparing values
• table_array: The range of cells that contains your data
• row_index_num: The index of the row to return the value of. The topmost row of your table_array is index 1.
• range_lookup: Optional. Enter FALSE for an exact match, or TRUE for an approximate match. This parameter may be omitted, in which case approximate matching is used
  • Use FALSE in most situations where you require exact matches (such as looking up emails or IDs). TRUE is suitable mainly for numerical ranges (such as tax brackets or grades).

Step-by-step HLOOKUP

Here’s how to set up HLOOKUP, step by step.

1. Organize your data

Prepare an area for your data, so that each column represents a row of data

• Make sure your data has clear row headers
• Ensure that the value you want to look up is in the first row of the range
• If using an approximate match, sort the first row in ascending order

Example: You have financial data per year. Each column corresponds to a year, and the rows are, in order: Revenue, Profit, Expenses, Employees, and Units sold.

2. Identify what you want to find

Decide what you are searching for and which row has the result that you need.

• Remember that the first row, that contains your lookup value, has index 1

Example: To find the number of employees, we look up the row with index 4.

3. Write the HLOOKUP formula

Click into the cell where you want the result. Type =HLOOKUP( and enter:

• the lookup value (e.g. a cell reference or text in quotes)
• the table range (e.g. an area or a named range)
• the row index number
• FALSE for an exact match or TRUE for an approximate match

Example: In the cell F1, we type =HLOOKUP(2025, B1:D6, 4, FALSE)

Understanding XLOOKUP

What is XLOOKUP?

XLOOKUP, or Cross Lookup, is a newer, more flexible lookup function available in modern versions of Excel and in Google Sheets. Unlike VLOOKUP and HLOOKUP, which require you to choose between looking horizontally or vertically, XLOOKUP works in both directions. It searches a range for a match and returns a value from a corresponding range.

Backwards compatibility

Remember that older Excel versions (pre-2019) don’t have XLOOKUP. So if backwards compatibility is an issue, then restructure your data and use VLOOKUP or HLOOKUP, or use INDEX and MATCH instead.

How XLOOKUP Works (Syntax explained)

The syntax of XLOOKUP is:

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

• lookup_value: The value that you want to match or find. XLOOKUP is case-insensitive
• lookup_array: The range of cells to search for a match in
• return_array: The range or array that has the value you want returned
• if_not_found: Optional. If provided, this value will be returned if no match was found (instead of #N/A)
• match_mode: Optional. Provide a number to indicate what kind of matching should be used:
  • 0 (default): exact matching. If none found, return #N/A or the if_not_found value
  • -1: exact matching. If none found, return the next smaller item
  • 1: exact matching. If none found, return the next larger item
  • 2: wildcard matching. The symbols *, ? and ~ have special meanings
• search_mode: Optional. Define how search will be done. This is useful for performance, when searching over big ranges
  • 1 (default): Search starting at the first item
  • -1: Search starting at the last item
  • 2: Do a binary search, works only if the lookup range is sorted in ascending order
  • -2: Do a binary search, but assume that the range is sorted in descending order

For deeper details, visit Microsoft’s official XLOOKUP documentation.

Wildcard matching

When using the wildcard matching mode with XLOOKUP, you can search for text patterns and not just exact text. The special wildcard symbols are as follows:

• * → matches any number of characters
• ? → matches exactly one character
• ~ → escapes a wildcard (so that you can search for *, ? or ~)

Step-by-step XLOOKUP

Here’s how to set up XLOOKUP, step by step.

1. Set up your data

Make sure that you have two clear ranges of data; one that contains the values to search, and one that contains the values to return.

• Make sure that the ranges line up, they must have the same number of rows or columns
• If using an approximate match, sort the lookup range in ascending or descending order

Example: We prepare a range with columns Price, Product, Stock and Category.

2. Decide what you want to find

Identify your lookup value and where you want to return the result.

Example: We will be searching the Product column, and returning the Price.

3. Write the XLOOKUP formula

Click into the cell where you want the result. Type =XLOOKUP( and enter:

• the lookup value (e.g. a cell reference or text in quotes)
• the lookup range (e.g. an area or a named range)
• the return range, of the same size as the lookup range
• the value to return if no match was found
• the match mode
• the search mode

Example: The formula to find the price of the product “Bananas” is =XLOOKUP(“Bananas”, B2:B4, A2:A4, “–“). We use the default values for the match mode and the search mode.

Common errors with LOOKUP

1. I get an #N/A error?

An #N/A error means that Excel or Google Sheets cannot find the value you are searching for. To avoid this, you can either use inexact matching, or wrap your VLOOKUP/HLOOKUP/XLOOKUP formula in an IFERROR or an IFNA function. The XLOOKUP function also has an option to provide a value to return instead of #N/A if no value is found.

Common reasons why this can happen are typos or trailing/leading spaces in your data. Data validation and the TRIM function can help avoid this.

2. There’s an #REF! error?

If you encounter a #REF! error, then that means that you are using VLOOKUP or HLOOKUP, and the column- or row-index is outside the range that you specified. For example, if you are returning column 6 from the range A1:D4, then you will see this error. Ensure that your index is within the range you lookup.

3. I get a #VALUE error?

A #VALUE error happens when you use XLOOKUP and the array to search is not compatible with the range of names to return. For example, B2:B5 is not compatible with C2:C4 because XLOOKUP will search the rows of B2:B5 and C2:C4 does not have the same number of rows. To solve this error, ensure that the search and return arrays are of the same shape.

Practical Tips

1. Copy or drag

Copying and dragging formulas in Excel or Google Sheets allows you to look up multiple values easily. However, you should in most cases lock the ranges in your formula with $-signs to keep the ranges fixed.

2. Returning multiple columns with XLOOKUP

You can use XLOOKUP to return several columns or rows at once, by providing a return_range that is several columns (if lookup_array is a column) or several rows (if lookup_array is a row).

Example: The formula =XLOOKUP(“Bananas”, B2:B4, C2:D4, “–“) returns both the C and D column values for the matching row.