MVLOOKUP
Description
We recommend this function as a replacement for VLOOKUP, especially when you're working with
live data. MVLOOKUP performs a vertical (column) lookup on a table and returns all
matches. MVLOOKUP is similar to VLOOKUP, but:
- VLOOKUP scans only the left column for matches; in MVLOOKUP you specify the column to search.
- VLOOKUP stops after finding 1 match, and returns a single cell value; MVLOOKUP scans the entire lookup column for matches. Each match in that column results in a new row in the output. If you specify 4return_column_indexvalues, then each resulting row will have 4 columns.
Syntax
MVLOOKUP(
lookup_value
,
table_array
,
lookup_column_index
,
order
,
return_column_index
, ...)
- lookup_value: The value to match.
- table_array: The array or table to search.
- lookup_column_index: The column in the table to search (1-based).
- order: The order for the search, based on the sort order oftable_array.
- 0: The default linear search, for unsorted data.
- 1: Binary search, for data sorted in ascending order.
- -1: Binary search, for data sorted in descending order.
- return_column_index: The column number to return results from. You can list any number of column numbers.
Example
The example is based on this workbook:
A | B | C | D | |
|---|---|---|---|---|
1 | Salesperson | Customer | Product | Revenue |
2 | Dixon | O'Reilly, Auer, & Lind | Qosolex | $8,232,000.00 |
3 | Dixon | Runolfsson and Steuber | Saoplus | $4,853,000.00 |
4 | Kelly | Kuphal Group | Qosolex | $2,358,000.00 |
5 | Kelly | Ernser Inc | Voltflarn | $2,064,000.00 |
6 | Payne | Williamson Group | Singlflix | $6,974,000.00 |
7 | Payne | Ratke-Sanford | Qosolex | $8,433,000.00 |
8 | Payne | Leuschke and Sons | Qosolex | $8,181,000.00 |
9 | Wu | Dach-Halvorson | Singlflix | $2,361,000.00 |
10 | Wu | Wisoky LLC | Bextain | $5,752,000.00 |
11 | Wu | Stiedemann Grp | Saoplus | $3,987,000.00 |
The result of:
=MVLOOKUP(A6,A2:D11,1,0,2,3,4)
or alternatively,
=MVLOOKUP("Payne",A2:D11,1,0,2,3,4)
is:
Williamson Group | Singlflix | $6,974,000.00 |
Ratke-Sanford | Qosolex | $8,433,000.00 |
Leuschke and Sons | Qosolex | $8,181,000.00 |
Notes
- MVLOOKUP() scans the rows in columnlookup_column_indexforlookup_value. The function then gathers values in the columns listed inreturn_column_index. Then it places each value into a column in the resulting matrix.
- This function is intended for use in array formulas.
Related Functions
MATCHEXACT
MHLOOKUP