LOOKUP Functions in Google Sheets and Excel – VLOOKUP, XLOOKUP, and More

Lookup functions are some of the most powerful and commonly used tools in spreadsheets. They allow you to search for specific data in one part of a sheet and pull corresponding information into another location. This is incredibly useful for analyzing large datasets, building reports and dashboards, and automating tedious tasks.

In this article, we‘ll take an in-depth look at the most important lookup functions in Excel and Google Sheets: VLOOKUP, HLOOKUP, XLOOKUP, and INDEX/MATCH. I‘ll explain how each one works, walk through examples with sample data, and share tips and best practices to help you master these essential functions. Let‘s get started!

Understanding Lookup Functions

At their core, lookup functions work by searching for a specific value in one column or row of data, and returning a related value from the same row in another column. They establish a relationship between two sets of data.

For example, let‘s say you have a table of employees with columns for name, ID number, department, and salary. You could use a lookup function to search for an employee by their ID number and automatically pull in their name, department or salary.

The key components of a lookup function are:

• Lookup value – The specific data you are searching for, like an ID number or name
• Lookup range – The group of cells you want to search within, typically a single row or column
• Return range – The group of cells that contains the corresponding information you want to retrieve

Different lookup functions handle these components in their own ways, as we‘ll see. But they all rely on this fundamental pattern of finding a match and retrieving associated data.

VLOOKUP Function

VLOOKUP, short for "vertical lookup", is probably the most widely used lookup function. As the name suggests, it searches for data vertically down a column.

The syntax for VLOOKUP is:

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

Here‘s what each part means:

• lookup_value: The value to search for.
• table_array: The range of cells that contains the data.
• col_index_num: The column number in the range that contains the return value. The first column is 1.
• [range_lookup]: Optional. TRUE = approximate match (default). FALSE = exact match.

Let‘s walk through an example to illustrate. Consider this sample employee data:

To look up an employee‘s department using their ID number, you would use a formula like:

=VLOOKUP(B2,A2:D7,3,FALSE)

This searches for the value in B2 (12345) in the first column of range A2:D7, and returns the value from the 3rd column in the same row. The FALSE argument specifies an exact match lookup.

Some important things to note about VLOOKUP:

• The lookup value must be in the first column of the table array.
• Columns are counted from left to right, starting at 1.
• If range_lookup is TRUE or omitted, it will match the nearest value less than the lookup value.

VLOOKUP Limitations

While VLOOKUP is very useful, it has some drawbacks:

• You can only retrieve values to the right of the lookup column. To get values to the left, you need to rearrange your data.
• If you insert or delete columns in the table array, you have to manually update the col_index_num argument.
• Approximate match lookups can cause unexpected results if data isn‘t sorted properly.

HLOOKUP Function

HLOOKUP is very similar to VLOOKUP, except it performs a horizontal lookup across columns instead of a vertical lookup down rows. It searches for the lookup value in the first row and retrieves the corresponding value relative to that position in the specified row below.

The HLOOKUP syntax is:

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

The arguments work the same as VLOOKUP, except:

• table_array: The range that contains the data, with lookup values now in the first row.
• row_index_num: The row number in the range that contains the return value. The first row is 1.

Here‘s the same employee data laid out horizontally:

And here‘s how you would use HLOOKUP to retrieve the department for employee ID 45678:

=HLOOKUP(F1,A1:G5,3,FALSE)

HLOOKUP has the same general limitations as VLOOKUP, just oriented horizontally instead of vertically. It‘s less commonly used than VLOOKUP overall.

XLOOKUP Function

XLOOKUP is the newest lookup function, introduced in Excel 2021 and Google Sheets in 2022. It‘s designed to be more flexible and user-friendly than VLOOKUP and HLOOKUP.

The syntax for XLOOKUP is:

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

• lookup_value: The value to search for.
• lookup_array: The range to search for the lookup value.
• return_array: The range that contains the values to return.
• match_mode: The type of match to perform. 0 = exact match (default), -1 = exact match or next smaller value, 1 = exact match or next larger value, 2 = wildcard match.
• search_mode: The search direction. 1 = first to last (default), -1 = last to first.

Using our original vertical employee data, here‘s how you would use XLOOKUP to find the department for employee 78901:

=XLOOKUP(B4,A2:A7,C2:C7)

The key advantages of XLOOKUP are:

• Lookup and return arrays don‘t have to be adjacent like with VLOOKUP/HLOOKUP. They can be in any location.
• You can search vertically or horizontally. Just provide ranges in the appropriate orientation.
• It supports approximate and exact matching without sorting, wildcard searches, and reverse searches.
• You can easily search from the end of a range by providing a negative search_mode.
• It has more helpful error handling, allowing you to specify your own message for no match found.

=XLOOKUP(B4,A2:A7,C2:C7,"Not found")

While XLOOKUP can do everything VLOOKUP can and more, it‘s currently only available in the latest versions of Excel and Sheets. So for maximum compatibility, you may still need to use VLOOKUP in some cases.

INDEX and MATCH Functions

INDEX and MATCH are two separate functions that are often used together to perform lookups. This approach is more flexible than VLOOKUP and HLOOKUP, allowing you to search and retrieve values in any direction.

The INDEX function returns a value at a given position in a range. Its syntax is:

=INDEX(array, row_num, [column_num])

• array: The range of cells to retrieve a value from.
• row_num: The row position in the range to return.
• column_num: Optional. The column position in the range to return.

The MATCH function searches for a value in a range and returns its relative position. Its syntax is:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value to search for.
• lookup_array: The range to search.
• match_type: The type of match. 1 = less than, 0 = exact match, -1 = greater than.

By nesting MATCH inside of INDEX, you can replicate VLOOKUP like this:

=INDEX(C2:C7,MATCH(B2,A2:A7,0))

This searches for the lookup value (B2) in the lookup array (A2:A7) using MATCH, and returns the value at the corresponding row position in the return array (C2:C7) using INDEX.

The main benefit is that the lookup and return ranges can be anywhere, even on different sheets. And you avoid having to count and update column numbers like in VLOOKUP and HLOOKUP.

You can also use INDEX and MATCH to perform two-way lookups, searching both vertically and horizontally to retrieve a value based on matching row and column headers.

Other Lookup Functions and Alternatives

In addition to the main lookup functions covered above, there are a few other related functions worth knowing:

• FILTER – Extracts matching rows from a range based on one or more criteria. Useful for retrieving multiple matches.
• QUERY – Searches and manipulates data using SQL-like syntax. Good for filtering, sorting and summarizing large datasets.
• SUMIF/SUMIFS – Sums values in a range that meet specified criteria. Allows aggregating data based on matching conditions.
• VLOOKUP with wildcard – You can use the * wildcard with VLOOKUP to match partial values.

In some cases, you may not need a dedicated lookup function at all. If your data is well-structured, you can use functions like FILTER or QUERY to retrieve matching rows. Or you could use Excel slicers and pivot tables to interactively filter data.

Tips for Using Lookup Functions

To get the most out of lookup functions, keep these best practices in mind:

• Organize your data in a consistent tabular format with clear headers. Avoid merged cells, blank rows/columns, and split data.
• Use absolute/relative cell referencing and named ranges appropriately to make your formulas more readable and easier to copy.
• Be careful with approximate match lookups. Make sure data is sorted and watch out for unexpected matches.
• Double-check the actual cell values your formulas are referencing, not just what you expect the values to be. Extra spaces, numbers formatted as text, etc. can cause lookups to fail.
• Test your lookup formulas with different inputs and edge cases to make sure they work as intended. Check for error values like #N/A or #REF!.
• Use the Formula Evaluation tool in Excel or the Step into feature in Sheets to debug complex or nested lookup functions.
• Consider using data validation and indexed match for lookups on user input to avoid errors and improve performance.

Resources to Learn More

This article covers a lot of ground, but there‘s always more to learn. Here are some helpful resources to continue your lookup journey:

• XLOOKUP function reference | Microsoft Support
• How to use the VLOOKUP function | Exceljet
• Lookup and Reference Functions in Excel | Vertex42
• INDEX MATCH: The Ultimate Guide | Deskbright
• XLOOKUP in Google Sheets | Ben Collins

I also highly recommend the book Excel Lookup Functions Explained by Bill Jelen for an even deeper dive on this topic.

Conclusion

We‘ve explored the core lookup functions in Excel and Google Sheets, walking through how to use VLOOKUP, HLOOKUP, XLOOKUP and INDEX/MATCH with practical examples.

As you can see, each function has its own strengths and weaknesses. VLOOKUP and HLOOKUP are straightforward but less flexible. XLOOKUP is powerful and intuitive but only available in newer versions. And INDEX/MATCH requires combining two functions but allows for more sophisticated lookups.

Ultimately, the best function to use will depend on your specific data structure, version requirements, and lookup needs. The most important thing is to understand the fundamental concepts and patterns behind lookups.

Lookup functions are an essential part of any spreadsheet user‘s toolkit. By mastering them, you‘ll be able to analyze and report on data faster, automate tedious workflows, and unlock valuable insights. Put them into practice and see what they can do for you!