Excel Lookup Functions Explained with Examples

Searching for a single piece of data in a spreadsheet with hundreds of rows can take a long time if it is done manually. This is exactly the kind of problem that Excel lookup functions were built to solve. Instead of scrolling through rows and columns looking for a match, a lookup function finds the value in seconds and pulls back the exact information needed. This guide explains VLOOKUP, HLOOKUP, and XLOOKUP with clear examples, so anyone working with spreadsheets can pick the right function for the task at hand.

What Are Lookup Functions in Excel?

A lookup function searches for a value in one part of a table and returns a related value from another part of the same table. For example, if a spreadsheet has a list of employee IDs in one column and their names in another, a lookup function can find the name that matches a given ID without any manual searching.

These functions are widely used in reporting, data cleaning, and cross-referencing large datasets. Anyone learning how to work with spreadsheets, especially those exploring excel lookup functions for the first time, usually starts with VLOOKUP before moving on to more flexible options like XLOOKUP.

VLOOKUP Function Explained with Example

VLOOKUP, short for Vertical Lookup, searches for a value in the first column of a table and returns a value from a specified column in the same row.

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

lookup_value: the value being searched for

table_array: the range of cells containing the data

col_index_num: the column number to return the value from

range_lookup: FALSE for an exact match, TRUE for an approximate match

Example: Suppose a table lists Employee ID in column A and Employee Name in column B. To find the name for Employee ID 105, the formula would look like this:

=VLOOKUP(105, A2:B10, 2, FALSE)

This searches column A for 105, then returns the matching value from column B. VLOOKUP is a strong starting point for anyone new to spreadsheets, but it comes with a limitation: it can only search to the right of the lookup column, and it breaks if a column is inserted or deleted within the range.

HLOOKUP Function Explained with Example

HLOOKUP, short for horizontal lookup, works the same way as VLOOKUP but searches across a row instead of down a column.

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

Example: If quarter names (Q1, Q2, Q3, Q4) are listed across row 1 and sales figures are in the rows below, this formula finds the sales figure for Q3:

=HLOOKUP(“Q3", A1:D5, 3, FALSE)

HLOOKUP is less commonly used than VLOOKUP simply because most datasets are organized in columns rather than rows, but it becomes useful for horizontally structured reports, such as monthly or quarterly summaries.

XLOOKUP Function Explained with Example

XLOOKUP is a newer function available in Excel 365 and Excel 2021 onward. It was built to remove the limitations of VLOOKUP and HLOOKUP by allowing a search in any direction, left, right, up, or down.

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Example: To find an employee’s name based on their ID, even if the ID column is to the right of the name column, the formula looks like this:

=XLOOKUP(105, A2:A10, B2:B10)

XLOOKUP also allows a custom message to appear instead of the standard #N/A error when no match is found, which makes reports easier to read and troubleshoot. Unlike VLOOKUP, it is not affected by inserted or deleted columns, since it references the lookup and return ranges separately rather than relying on a fixed column number.

VLOOKUP vs HLOOKUP vs XLOOKUP: Which One Should You Use?

Choosing between these functions depends on how the data is arranged and which version of Excel is being used.

A simple way to decide: if the spreadsheet uses an older version of Excel, VLOOKUP or HLOOKUP will be the practical choice depending on the data layout. If XLOOKUP is available, it is generally the more reliable option because of its flexibility and better error handling.

Common VLOOKUP Errors and How to Fix Them

VLOOKUP is reliable, but a few errors show up often, especially for those still getting comfortable with the formula.

#N/A Error This usually means the lookup value was not found. Common causes include typos, extra spaces before or after the text, or searching in the wrong column. Wrapping the formula in IFERROR can display a custom message instead of the default error.

#REF! Error This happens when the column index number points outside the selected range. Double-checking the table_array and col_index_num usually resolves it.

Wrong or outdated data returned This often happens when range_lookup is left blank or set to TRUE by mistake, which triggers an approximate match instead of an exact one. Setting it to FALSE ensures the formula only returns exact matches.

How to Use a Lookup Function Excel Multiple Criteria Searches

A single VLOOKUP or XLOOKUP formula normally matches on one value at a time, but many real spreadsheets need to match on two or more conditions together, such as finding a sales figure for a specific product and a specific region. This is where a lookup function with an excel multiple criteria search comes in.

The most common way to handle this with VLOOKUP is to create a helper column that joins the values being searched for. For example, if Product is in column A and Region is in column B, a helper column in column C could combine them using this formula:

=A2&B2

The VLOOKUP formula can then search this combined column using the same joined logic:

=VLOOKUP(F1&F2, C2:D10, 2, FALSE)

XLOOKUP makes this easier without needing a helper column, since it supports combining lookup values directly within the formula using the same “&" operator:

=XLOOKUP(F1&F2, A2:A10&B2:B10, C2:C10)

For Excel 365 users, this XLOOKUP version needs to be entered as an array formula in older builds, though newer versions handle it automatically through dynamic arrays. Multi-criteria lookups are especially useful in sales reporting, inventory tracking, and any dataset where a single column cannot uniquely identify a row on its own.

A Quick Note on INDEX MATCH

Some Excel users prefer combining INDEX and MATCH instead of VLOOKUP, since it can search in either direction and is not affected by inserted or deleted columns. It requires a longer formula, though, which is why many people now prefer XLOOKUP when it is available, since it offers similar flexibility with simpler syntax.

Which Excel Course Can Help You Master These Functions?

Lookup functions are only one piece of working confidently in Excel, and practicing them alongside other formulas tends to make them stick faster. Those who already know the basics and want to go straight into advanced formulas, multi-criteria lookups, and automation with macros will find that an advanced Excel Course covers exactly this ground in a single hands-on session. For those starting from the fundamentals and wanting a complete path through Basic, Intermediate, and Advanced Excel, including lookup functions along the way, Master Excel Training walks through all three levels in sequence.

Conclusion

There is no single best lookup function for every situation. VLOOKUP remains useful for simple, column-based searches, HLOOKUP fits row-based data, and XLOOKUP offers the most flexibility for those using a recent version of Excel. Understanding excel lookup functions for beginners is the first step toward more confident spreadsheet work, and as datasets grow larger, these same skills become essential for excel lookup functions for data analysis, reporting, and everyday decision-making at work.