How to Return the Matching Row and Area from Excel Search Results
Question details
The user needs a formula to search an inventory grid and return the specific row identifier and area (column header) where a stock number is located.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Searching for an inventory number within a 2-dimensional worksheet grid categorized by areas (A through E).
- Observed behavior
- The user wants the matching row and area to be displayed directly below the search box when an item is found to be in stock.
Ensure your inventory data is organized in a clear grid format, with row identifiers explicitly listed in the first column and area labels (A through E) placed in the top header row.
Use INDEX and SUMPRODUCT Formulas for a 2D Grid Search
This advanced formula combination allows you to search across multiple columns and rows to find a specific value, then return its corresponding row identifier and column header (Area) without needing VBA.
Standard VLOOKUP functions only search in one dimension (a single column). To search a two-dimensional grid, we can use SUMPRODUCT to locate the row and column numbers of the matching cell, and then use INDEX to fetch the corresponding labels from your axes.
Assume your row identifiers are in cells A2:A10, your Area headers are in B1:F1, and the actual inventory numbers fill the grid in B2:F10. Place your search term in cell H2.
Select the cell where you want the row identifier to appear and enter this formula: =INDEX(A2:A10, SUMPRODUCT((B2:F10=H2)*ROW(B2:F10))-MIN(ROW(B2:F10))+1). Press Enter.
Select the cell where you want the Area label to appear and enter this formula: =INDEX(B1:F1, SUMPRODUCT((B2:F10=H2)*COLUMN(B2:F10))-MIN(COLUMN(B2:F10))+1). Press Enter.
Restructure Data into a Flat Table for Simpler Lookups
If complex array formulas are difficult to maintain, restructuring your 2D grid into a traditional 1D table makes standard lookup functions like VLOOKUP or XLOOKUP highly effective.
Easily Perform Advanced Lookups with WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools and fully supports complex formulas like INDEX, MATCH, and SUMPRODUCT to help you build highly automated, error-free inventory trackers.
- 1. Open your inventory sheet: Launch WPS Spreadsheet and open your inventory .xlsx file.
- 2. Set up a search dashboard: Designate specific cells at the top of your sheet to act as your 'Search Box' and 'Results Area'.
- 3. Input the array formulas: Use the provided INDEX and SUMPRODUCT formulas in the Results Area to fetch the row and column coordinates seamlessly.
- 4. Add Conditional Formatting: Go to Home > Conditional Formatting to automatically highlight the inventory cell when it matches your search box, making it easier to spot visually.
Frequently Asked Questions
Why does my INDEX formula return an error or the wrong area label?
This typically occurs if the ranges in your formula are not perfectly aligned. Ensure that the row (A2:A10) or column (B1:F1) headers referenced in the INDEX function exactly match the height and width of your main search grid (B2:F10).
How do I return both the row and area in a single cell?
You can combine the two lookup formulas into one cell using an ampersand (&) to concatenate them. For example: =Formula1 & " - " & Formula2. This will display a result like 'Row 5 - Area B' directly in one cell.
Can I use Conditional Formatting to highlight the searched inventory number in the grid?
Yes. Select your entire data grid, go to Conditional Formatting > New Rule, and choose 'Use a formula to determine which cells to format'. Enter a formula like =B2=$H$2 (where H2 is your search box) and choose a highlight color.
What should I do if my inventory items are not unique?
If you have duplicate inventory items across different areas, the SUMPRODUCT method will fail. You will need to use more complex array formulas using SMALL and IF statements, or preferably restructure your data into a flat table and use Power Query or filter functions.
