Function Problems

How to Use an Excel Lookup Formula Based on Area or Position

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to calculate a rating from a Total Score using a specific divisor that changes depending on the Area/Position, without manually modifying the formula for each row.

Product: Excel
Device & OS: not provided
Scenario: Calculating condition-based metrics across a large dataset where the divisor value relies on matching a specific category to a predefined list.
Observed behavior: The user wants a single automated formula that looks up the correct metric for each row, replacing the need to manually adjust calculations row by row.

Before you start

Ensure your main data is formatted as an Excel Table (Ctrl+T) so you can utilize structured references, and verify that your spreadsheet application supports the XLOOKUP function.

Solution 1Recommended

Use XLOOKUP with a Dedicated Reference Table

Create a separate reference table for your mean scores and use XLOOKUP to dynamically retrieve the correct divisor for each area or position.

By storing your divisors in a separate structured table, you can update your metrics in one place without ever having to edit the formulas in your main dataset. Using XLOOKUP combined with IFERROR ensures your calculations remain clean and error-free.

Create the reference table

Set up a new data range with two columns titled 'Area/Position' and 'Mean Score', inputting your respective categories and their corresponding divisors.

Format as an Excel Table

Highlight this new data range, press Ctrl+T to convert it into a Table, and use the Table Design tab on the ribbon to name it 'MeanScores'.

Enter the XLOOKUP formula

Click into the first cell of the rating column in your main data table and input the following formula: =IFERROR([@[Total Score]]/XLOOKUP([@[Area/Position]],MeanScores[Area/Position],MeanScores[Mean Score])*5,0)

Apply to the entire column

Press Enter. Thanks to Excel's structured references, the formula will automatically fill down the entire column, calculating the correct score for every row based on its specific Area/Position.

Legacy Version Alternative: If you are using an older version of Excel that lacks the XLOOKUP function, you can substitute it with a VLOOKUP formula: =IFERROR([@[Total Score]]/VLOOKUP([@[Area/Position]],MeanScores,2,FALSE)*5,0).

Powerful Spreadsheet Management

Easily Manage Advanced Formulas with WPS Spreadsheet

WPS Office provides comprehensive support for advanced lookup functions, including XLOOKUP, VLOOKUP, and structured table references. You can seamlessly calculate complex, condition-based ratings in a lightweight and highly compatible environment.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing workbook containing the scoring data.
2. Define your Data Tables: Select your reference data, navigate to the Insert tab, and click Table to quickly create and name your 'MeanScores' lookup table.
3. Input the Lookup Formula: Type your XLOOKUP formula into the target cell. WPS Spreadsheet will intelligently auto-fill the formula down the entire table.

Fully compatible with Microsoft Excel formulas, functions, and table structuresNatively supports modern functions like XLOOKUP, IFERROR, and structured referencesLightweight application that handles large datasets smoothly without laggingFree to use for everyday spreadsheet tasks and complex data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why is the IFERROR function necessary in this formula?

The IFERROR function acts as a safety net. If a specific Area or Position from your main dataset is misspelled or missing from the MeanScores lookup table, IFERROR prevents the cell from displaying an ugly #N/A error, returning a neat '0' instead.

What are the benefits of using structured references like [@[Total Score]]?

Structured references make formulas significantly easier to read and maintain. They automatically adjust when you add or remove rows in an Excel Table, eliminating the tedious need to lock cell references with dollar signs (absolute references).

Can I use multiple criteria for my lookup?

Yes. With the XLOOKUP function, you can match multiple criteria by concatenating the lookup values and the lookup arrays using the ampersand (&) symbol within the formula.

Will this lookup formula slow down my spreadsheet?

Using XLOOKUP is generally highly optimized and faster than traditional array formulas or complex INDEX/MATCH setups. However, applying it to hundreds of thousands of rows may cause slight calculation delays depending on your system's processing power.

Excel & Data Problems