Horizontal XLOOKUP in Google Sheets: A Practical Guide

In this guide, we’ll dive into horizontal XLOOKUP in Google Sheets, exploring how this function can streamline your data lookup processes by combing through rows of data and returning only what you need.

Previously, spreadsheet users needed to choose between VLOOKUP for vertical data tables or HLOOKUP for horizontal data tables. XLOOKUP eliminates this limitation, allowing lookups across different data arrangements. This enhanced capability is a key reason why XLOOKUP is rapidly becoming the preferred lookup function for data lookups.

Since XLOOKUP is a great fit for horizontal lookups, let’s demonstrate how it’s used with several examples. Use this linked Google Sheet to get copies of the examples in your Google Drive.

Horizontal Layout

We will rotate the typical spreadsheet layout for the examples so the headers are on the left and the data extends to the right. Here is an example of the change where we take an example from our vertical (regular) XLOOKUP documentation and turn it on its side.

Each table contains the same data, but they look different. We’ll be using the data in the horizontal format shown on the right in the image.

The Basic Formula Structure

For horizontal XLOOKUP, we use the same function as vertical lookups.

=XLOOKUP (search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

The key difference is that your lookup_range and return_range reference rows instead of columns.

Example 1: Simple Horizontal Lookup

Let’s say you have Sales Rep information spread across row 3, with Regions in row 4. We’ll use the following formula in cell B9 to search for the Sales Rep across row 3 and return the corresponding value from row 4.
=XLOOKUP(B3, B8:E8, B9:E9)

This horizontal XLOOKUP looks at row 8, finds Richard in cell D8, and returns the corresponding South value from row 9.

Example 2: Returning Multiple Rows

Sometimes, you need to pull data from several rows below your search range:
=XLOOKUP(B3,B10:E10,B11:E12)

This formula looks for Richard in row 10 and returns the corresponding values from rows 11 and 12, which are South for the Region and Aluminum for the Specialty.

Pro Tips

• Always ensure your lookup range references a single row
• Use fixed references ($) when copying formulas
• Consider using IFERROR() to handle potential errors gracefully

Video Tutorial

Conclusion

With these examples and tips, you can confidently implement horizontal XLOOKUPs in your spreadsheets, making your data analysis more efficient and flexible.

Related Articles