Function Problems

How to Use XLOOKUP Formula to Match a Size With Its Temperature in Excel

Huda Qurayshi Sep 25, 2026 871 views

Question details

The user needs a formula to look up a size entered in one column and return the corresponding temperature from another column.

How to Use XLOOKUP Formula to Match a Size With Its Temperature in Excel

Product: Excel
Device & OS: not provided
Scenario: Looking up matching data pairs, specifically a temperature based on a provided size, across different columns using an automated formula.
Observed behavior: The user wants to automatically populate a destination column with the correct temperature from the source data based on the lookup size entered.

Before you start

Ensure your source data for sizes and temperatures does not contain trailing spaces or mismatched formatting (like text vs. numbers), as this can cause lookup functions to fail.

Solution 1Recommended

Use the XLOOKUP Function to Find Matching Data

The XLOOKUP function is the most efficient and modern way to search for a value in one array and return a corresponding value from another array.

XLOOKUP replaces older functions like VLOOKUP and HLOOKUP. It defaults to an exact match and can search both vertically and horizontally without requiring the lookup column to be on the far left.

In this scenario, we will use XLOOKUP to check column A for the size, and return the corresponding temperature from column B.

Identify the source and destination cells

Confirm that your source sizes are in range A2:A100 and temperatures are in B2:B100. Assume the lookup size you want to search for is entered in cell C2, and you want the result in cell D2.

Select the destination cell

Click on cell D2, which is where you want the matched temperature to appear.

Enter the XLOOKUP formula

Type the formula: =XLOOKUP(C2, $A$2:$A$100, $B$2:$B$100, "") into the formula bar or directly into cell D2, then press Enter.

Fill the formula down

If you have multiple sizes to look up in column C, click cell D2, hover over the small square at the bottom-right corner of the cell until it turns into a plus sign, and drag it down to fill the formula to the remaining rows.

Use the XLOOKUP Function to Find Matching Data

Understanding Absolute References: The dollar signs ($) in the formula lock the range references ($A$2:$A$100). This ensures the search area does not shift downwards when you drag the formula to other cells.

Use WPS Spreadsheet for Advanced Formulas

Easily Match Data with XLOOKUP in WPS Office

WPS Spreadsheet fully supports modern advanced functions like XLOOKUP. You can quickly look up, match, and organize your datasets without needing a premium software subscription.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your size and temperature data.
2. Select the output cell: Click on the cell (e.g., D2) where the matched temperature should be displayed.
3. Input the lookup formula: Type =XLOOKUP(C2, $A$2:$A$100, $B$2:$B$100, "") and press Enter to execute.
4. Drag to apply: Use the fill handle at the bottom-right of the cell to drag the formula down for any additional lookup values.

Native support for advanced functions like XLOOKUP, VLOOKUP, and MATCHSeamless compatibility with Microsoft Excel (.xlsx, .xls) filesFree and lightweight spreadsheet alternativeFamiliar user interface with no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

What if the XLOOKUP formula returns an #N/A error?

An #N/A error means the lookup value does not exist in the lookup array. Ensure that the size entered in column C exactly matches a size in column A, without extra trailing spaces or different data types (e.g., a number stored as text). By adding the fourth argument "" in the formula, XLOOKUP will return a blank cell instead of an #N/A error if no match is found.

Can I use VLOOKUP instead of XLOOKUP for this task?

Yes, you can use VLOOKUP if your source data has the lookup column (sizes) on the left of the return column (temperatures). The formula would be =VLOOKUP(C2, $A$2:$B$100, 2, FALSE). However, XLOOKUP is generally preferred because it can look in any direction and defaults to exact matches automatically.

How do I handle empty cells returning a zero in XLOOKUP?

If the return array (column B) has an empty cell next to the matched size, XLOOKUP might return a 0 instead of a blank. You can prevent this by appending an empty string to your formula, like this: =XLOOKUP(C2, $A$2:$A$100, $B$2:$B$100, "")&"".

Excel & Data Problems