Use XLOOKUP in a Structured Table in Google Sheets (Single and Multiple Conditions)

This tutorial is for users who want to apply XLOOKUP inside a structured table in Google Sheets (created via Insert > Table or Convert range to table). You’ll learn how to use both single and multiple conditions with structured references across two tables:

  • A main (primary) table, where the formula is written.
  • A lookup (reference) table, where the data is retrieved from.

Using structured references can make formulas cleaner and easier to manage—especially inside tables.

What Is “XLOOKUP Inside a Structured Table Row”?

It means writing an XLOOKUP formula that refers to structured column names within a table row, instead of traditional range references like A2:A10. This approach:

  • Auto-expands to new rows (like Excel tables).
  • Keeps formulas dynamic and readable.
  • Supports both simple and multi-condition lookups.

Single Condition XLOOKUP in a Structured Table (Google Sheets Example)

Goal: Return the employee’s role from the lookup table based on the employee ID in the main table using structured references.

Table Structure:

Lookup Table (Table1):

Employee IDRole
EMP001Sales Associate
EMP002HR Manager
EMP003Software Engineer
EMP004Data Analyst
EMP005Marketing Lead

Main Table (Table2):

Employee IDRole
EMP001

Formula in Table2[Role]:

=XLOOKUP(SINGLE(Table2[Employee ID]), Table1[Employee ID], Table1[Role])
  • The SINGLE() function ensures only one value is passed to XLOOKUP.
  • The result auto-fills as new rows are added next to the last row.
Structured table showing Employee IDs and roles in Google Sheets using XLOOKUP

Note: If you use the + (plus) button to insert a new row, the formula won’t auto-copy. Instead, type directly next to the last value.

ARRAYFORMULA with XLOOKUP in a Structured Table

If you prefer one formula that covers the entire column:

=ARRAYFORMULA(XLOOKUP(Table2[Employee ID], Table1[Employee ID], Table1[Role]))
Using ARRAYFORMULA with XLOOKUP to apply lookup to entire structured column in Google Sheets

Multi-Condition XLOOKUP in Google Sheets Using Structured References

Goal: Return the product price by matching item, size, and color across two structured tables using multiple-condition XLOOKUP.

Table Structure:

Lookup Table (Table1):

Employee IDRole
EMP001Sales Associate
EMP002HR Manager
EMP003Software Engineer
EMP004Data Analyst
EMP005Marketing Lead

Main Table (Table2):

ItemSizeColorPrice
T-shirtMRed

Formula (for one row):

=XLOOKUP(1, ARRAYFORMULA((Table1[Item]=SINGLE(Table2[Item])) *  (Table1[Size]=SINGLE(Table2[Size])) *  (Table1[Color]=SINGLE(Table2[Color]))), Table1[Price])

Explanation:

  • Each condition returns TRUE (1) or FALSE (0).
  • All TRUEs multiply to 1.
  • XLOOKUP searches for 1 and returns the corresponding price.
Boolean logic in XLOOKUP with multiple conditions using structured table in Google Sheets

Array Formula for Multi-Condition XLOOKUP in Google Sheets

To make it work across all rows in Table2:

=MAP(Table2[Item], Table2[Size], Table2[Color], LAMBDA(x, y, z, XLOOKUP(1, ARRAYFORMULA((Table1[Item]=SINGLE(x)) *  (Table1[Size]=SINGLE(y)) *  (Table1[Color]=SINGLE(z))), Table1[Price])))
  • MAP iterates over each row.
  • LAMBDA creates dynamic row-based XLOOKUP logic.

Alternative Method for Multi-Criteria XLOOKUP (Using Text Join)

You could combine the lookup conditions using & and search a concatenated column:

=ARRAYFORMULA(XLOOKUP(Table2[Item]&Table2[Size]&Table2[Color], Table1[Item]&Table1[Size]&Table1[Color], Table1[Price]))

Summary

In this tutorial, you learned how to use XLOOKUP inside a structured table in Google Sheets, both for single-condition and multiple-condition lookups. With structured references and dynamic array formulas like ARRAYFORMULA and MAP, your lookups stay clean, scalable, and easy to manage.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.