WD.MVLOOKUP
Description
This function is the same as MVLOOKUP but WD.MVLOOKUP creates an index (b-tree) for the specified range or array, and it ignores the
order
argument. This function provides better performance for repeated use on the same range/array. Null and error values are ignored. We recommend this function as a replacement for VLOOKUP, especially when you're working with live data. WD.MVLOOKUP performs a vertical (column) lookup on a table and returns all matches. WD.MVLOOKUP is similar to VLOOKUP, but:
- VLOOKUP scans only the left column for matches; in WD.MVLOOKUP you specify the column to search.
- VLOOKUP stops after finding 1 match, and returns a single cell value; WD.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
WD.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: Not used.
- 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:
=WD.MVLOOKUP(A6,$A$2:$D$11,1,0,2,3,4)
or alternatively,
=WD.MVLOOKUP("Payne",$A$2:$D$11,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
- WD.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