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,A2:D11,1,0,2,3,4)
or alternatively,
=WD.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
- 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. 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
MHLOOKUP