How to Search Multiple Table Arrays with HLOOKUP in Excel
Question details
The user needs to find a specific data point, such as a terminal code, across multiple separate report sections or table arrays and return a matching value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a specific value across a complex report split into multiple non-continuous sections or arrays.
- Observed behavior
- Standard HLOOKUP only searches a single continuous table array, requiring either data consolidation or a workaround to search multiple separate blocks.
Before applying these formulas, ensure that the lookup value (e.g., terminal code) is unique across all table sections to prevent the formula from returning incorrect duplicate matches.
Combine Report Sections and Use Standard HLOOKUP
The simplest method is to consolidate your data into a single continuous table array so you can use a basic HLOOKUP formula.
If your report layout can be altered, combining the multiple sets of lines into one table is the most robust and least error-prone approach.
Highlight the separate blocks of data across your report and copy them.
Paste the data into a new worksheet or an empty area, aligning the headers to create one continuous horizontal table.
Enter the formula =HLOOKUP(lookup_value, new_combined_table_array, row_index_num, FALSE) to find your desired value.
Use INDEX and SUMPRODUCT for Separate Arrays
If you cannot alter the report layout, use a combination of INDEX and SUMPRODUCT to locate the value across multiple separate arrays.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like HLOOKUP, INDEX, and SUMPRODUCT, allowing you to easily search across multiple table arrays for free.
- 1. Open your file: Open your spreadsheet file in WPS Office.
- 2. Select the target cell: Click on the cell where you want the lookup result to appear.
- 3. Insert formula: Go to the Formulas tab and insert your HLOOKUP or INDEX/SUMPRODUCT formula.
- 4. Calculate: Press Enter to instantly search across your multiple table arrays.
Frequently Asked Questions
Can I use IFERROR to chain multiple HLOOKUP formulas?
Yes. If you have a small number of separate tables, you can nest HLOOKUP formulas using IFERROR: =IFERROR(HLOOKUP(val, table1, row, 0), HLOOKUP(val, table2, row, 0)). This formula checks the first table, and if the value isn't found, it seamlessly moves on to search the second table.
What happens if my lookup value appears in multiple sections?
If the lookup value appears multiple times, a standard HLOOKUP will return the first match it finds. However, if you are using the SUMPRODUCT workaround on duplicate values, it will add the column indices together, returning an entirely incorrect column reference. It is critical to ensure lookup values are unique.
Can I use VLOOKUP instead of HLOOKUP for multiple arrays?
VLOOKUP is designed for searching vertically down columns, whereas HLOOKUP searches horizontally across rows. If your separate data arrays are arranged vertically, you should use VLOOKUP combined with IFERROR or an INDEX/MATCH function to scan across the multiple tables.
