Function Problems

How to Find a Location Associated with a Value in an Excel Grid

Natalie Taylor Oct 10, 2026 869 views

Question details

The user needs to retrieve a location name that corresponds to a specific deposit value within a multi-column Excel grid.

How to Find a Location Associated with a Value in an Excel Grid

Product: Microsoft Excel
Device & OS: not provided
Scenario: Searching for a specific numerical value across multiple receipt columns to find the associated location in the first column.
Observed behavior: Modern functions like XLOOKUP are unavailable in older versions such as Excel 2011, requiring an array-based workaround to search a 2D grid and return a row header.

Before you start

Identify the exact range of your location names (e.g., Column A) and the range of your data grid (e.g., Columns B through D) before applying the formula.

Solution 1Recommended

Use an INDEX and MAX Array Formula

This formula finds the matching value in a multi-column range and returns the corresponding row label from the location column.

Because XLOOKUP and XMATCH are not available in Excel 2011, you must use an array formula to evaluate a multi-column range. The MAX function combined with the ROW function helps pinpoint the exact row index of your target value.

Select the destination cell

Click on the cell where you want the location result to appear.

Enter the INDEX and MAX formula

Type the formula: =INDEX(A3:A5,MAX((B3:D5=G2)*ROW(1:3))). Adjust A3:A5 to your location column, B3:D5 to your data grid, and G2 to the cell containing your lookup value. Note that ROW(1:3) corresponds to the relative row numbers of the data range.

Confirm as an array formula

Press Ctrl+Shift+Enter (or Cmd+Shift+Return on Mac) to calculate the formula. Curly braces will automatically appear around the formula indicating it is an array.

Array Formula Syntax: Do not type the curly braces {} manually. Excel will add them automatically when you confirm the formula with the Ctrl+Shift+Enter shortcut.

Solution 2

Use an INDEX and SUM(IF) Array Formula

An alternative array method that uses the IF function to locate the row index of the target value.

Advanced Spreadsheets

Easily Handle Complex Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas and provides modern functions to make retrieving data from 2D grids effortless.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your deposit data.
2. Select the result cell: Click the cell where you want to display the matched location.
3. Enter the array formula: Type the INDEX array formula or use the built-in Formula Wizard to easily set up your search parameters.
4. Confirm and calculate: Press Ctrl+Shift+Enter to calculate the array and instantly retrieve the matching location.

Fully compatible with Microsoft Excel formulas (.xlsx)Supports modern lookup functions like XLOOKUPLightweight and runs smoothly on older devicesAdvanced data analysis tools available for free

microsoft office alternative - wps office

Frequently Asked Questions

Why does my array formula return a #VALUE! error?

This usually happens if you press Enter instead of Ctrl+Shift+Enter. Array formulas require the Ctrl+Shift+Enter shortcut to calculate correctly in older versions of Excel.

How do I handle duplicate values in the Excel grid?

The INDEX and MAX array formula will return the location of the last matching value (highest row number) if duplicates exist. If you need the first match, you would require a more complex formula using the MIN function instead of MAX.

Will these formulas work if my data grid has empty cells?

Yes, empty cells will simply evaluate to zero or be safely ignored during the exact match comparison.

Can I use VLOOKUP to search a multi-column grid?

No, VLOOKUP only searches for a value in the first column of a specified range. To search across multiple columns and return a value from a completely different column, you must use an INDEX array formula or modern functions like XLOOKUP.

Excel & Data Problems