How to Use the XLOOKUP Function in Excel

The XLOOKUP Function

1. Set up the lookup examples used in the worksheet

• Start with an Equipment ID field that will be used to pull related details from an equipment database.
• Set up output columns for fields like:
  • Manufacturer
  • System Type
  • Labor Rate
  • Dispatch Fee
• For the rate example, transpose the labels so the lookup results can be returned into the correct layout.

2. Use XLOOKUP to return a value from the left side of the lookup column

• Use XLOOKUP to search for the Equipment ID in the equipment database.
• Set the lookup array to the Equipment ID column.
• Set the return array to the Manufacturer column.
• Add an optional if not found message so Excel returns custom text instead of an error.
• Use exact match for the lookup so only the correct equipment record is returned.
• Fill the formula down to return the manufacturer for all listed equipment IDs.

3. Use XLOOKUP to return a value from the right side of the lookup column

• Repeat the same process with XLOOKUP to return the System Type.
• Keep the same lookup value and lookup array.
• Change only the return array to the System Type column.
• This shows how XLOOKUP handles left-to-right lookups just as easily as right-to-left lookups.
• Fill the formula down to populate all results.

4. Use XLOOKUP to replace a horizontal lookup

• For the seasonal pricing section, use XLOOKUP again, but this time look up the Month instead of an Equipment ID.
• Set the lookup array to the month headers in the rate table.
• Set the return array to the row containing the Labor Rate values.
• Use F4 to lock the lookup and return ranges before copying the formula down.
• Leave the optional arguments simple by using a blank not-found value and exact match.

5. Copy the same XLOOKUP structure to return another row of values

• Copy the Labor Rate lookup and paste it into the next output area.
• Change the return array so it points to the Dispatch Fee row instead of the Labor Rate row.
• Keep the month lookup range the same.
• Format the results as currency and center-align the output if needed.

6. Use XLOOKUP to control what happens when no match is found

• In the Equipment ID example, add a custom text response for missing values.
• This replaces the default Excel error with something cleaner, such as a simple not-found message.
• This is helpful when building user-facing sheets or dashboards where errors would be distracting.

7. Use match mode options when your lookup needs flexibility

• In the video, the lookup uses exact match.
• XLOOKUP also allows other match behaviors, such as:
  • exact match or next smaller item
  • exact match or next larger item
  • wildcard matching
• For the examples shown, exact match is the correct option because the goal is to return the precise equipment or month.

8. Use search mode when duplicate lookup values exist

• XLOOKUP can search from first to last or last to first.
• In the video, the search runs from the first result to the last.
• This is useful if duplicate lookup values exist and you want to control which matching result Excel returns.

9. Return multiple fields at once with a dynamic array

• Set up another example where the Equipment ID should return more than one result.
• Use XLOOKUP with the same Equipment ID lookup value and lookup array.
• Instead of selecting one return column, select multiple adjacent return columns, such as:
  • System Type
  • Capacity in Tons
• XLOOKUP spills both values into the worksheet automatically as a dynamic array.
• This saves time because you do not need to write separate lookup formulas for each field.

10. Use XLOOKUP as a replacement for older lookup methods

• In the examples shown, XLOOKUP replaces:
  • INDEX and MATCH for returning values from the left
  • VLOOKUP for returning values from the right
  • HLOOKUP for horizontal month-based lookups
• This makes it a more flexible and streamlined option for pulling information from tables and databases.

11. Confirm XLOOKUP is available in your version of Excel

• Type the beginning of the function name into a cell and see whether Excel suggests it.
• If it appears in the formula list, your Excel version supports it.
• In the video, this is presented as a quick way to confirm access before building the examples.

If you want, I can also do this same section in the exact Elementor-ready HTML format you used on the previous pages.