MHLOOKUP
Description
We recommend this function as a replacement for HLOOKUP. MHLOOKUP performs a
horizontal (row) lookup on a table and returns all matches. MHLOOKUP is similar to
HLOOKUP, but:
- HLOOKUP scans only the top row for matches; in MHLOOKUP you specify the row to search.
- HLOOKUP stops after finding 1 match, and returns a single cell value; MHLOOKUP scans the entire lookup row for matches. Each match in that row results in a new row in the result. If you specify 4return_row_indexvalues, then each resulting row will have 4 columns.
Syntax
MHLOOKUP(
lookup_value
,
table_array
,
lookup_row_index
,
return_row_index
, ...)
- lookup_value: The value to match.
- table_array: The array or table to search.
- lookup_row_index: The row in the table to search (1-based).
- return_row_index: The row number to return results from. You can list any number of row numbers.
Example
The example is based on this workbook:
A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
1 | Salesperson | Dixon | Dixon | Kelly | Kelly | Payne | Payne | Payne |
2 | Customer | O'Reilly, Auer, & Lind | Runolfsson and Steuber | Kuphal Group | Ernser Inc | Williamson Group | Ratke-Sanford | Leuschke and Sons |
3 | Product | Qosolex | Saoplus | Qosolex | Voltflarn | Singlflix | Qosolex | Qosolex |
4 | Revenue | $8,232,000.00 | $4,853,000.00 | $2,358,000.00 | $2,064,000.00 | $6,974,000.00 | $8,433,000.00 | $8,181,000.00 |
The result of
MHLOOKUP("Qosolex",A1:H4,3,1,2,4)
is:
Dixon | Kelly | Payne | Payne |
O'Reilly, Auer, & Lind | Kuphal Group | Ratke-Sanford | Leuschke and Sons |
$8,232,000.00 | $2,358,000.00 | $8,433,000.00 | $8,181,000.00 |
Notes
- MHLOOKUP() scans the columns in thelookup_row_indexrow forlookup_value, then gathers values from the rows listed inreturn_row_index. Then it places each value into a column in the resulting matrix.
- This function is intended for use in array formulas. You submit array formulas with special keyboard shortcuts. We recommend that you use the unconstrained keyboard shortcut Ctrl+Alt+Enter (on Windows) or Command+Option+Enter (on Mac) so Worksheets can use all the cells it needs for the results. For more information, see Reference: Array Formula Keyboard Shortcuts.
Related Functions
MATCHEXACT
MVLOOKUP