- In short
- Multi Lookup returns every record that matches the lookup value, not just the first hit. The result is built from the empty output start cell you pick: a styled header is written as static text and the records spill from a single anchor cell holding one dynamic array formula. Change the lookup value and the output refreshes instantly. With an Excel Table as the source, rows added later join the lookup automatically; with a plain range, the scope stays fixed.
What it is for
VLOOKUP stops at the first match, which is useless when one value has many records, such as all orders of one customer. Multi Lookup spills every matching row from a single formula written into one anchor cell.
The Excel problem it solves
Getting every match for a value in Excel usually means filtering and copying by hand, or maintaining fragile array formulas. Copies go stale and formulas need care. A single-anchor dynamic array removes both burdens.
When to use it
Used when you need all orders of a customer, every leave record of an employee, or the full movement history of a product in one list.
Example scenarios
- Pull every order of a single customer out of the orders table into its own list.
- List all leave records recorded against one employee code.
- Build a match report over a movement list kept as an Excel Table, so new rows are covered automatically.
What it needs
The lookup value, the source table or range, and the column to match on. There is no separate table-versus-range option to set; the behaviour follows the kind of source you select.
How to use it
- Decide the value to look up, the source, and the empty area where the result should start.
- Open Multi Lookup from the Combine and Get group on the XLMind Plus tab.
- Pick the source table or range, the match column, the columns to return, and the output start cell.
- Confirm; a styled header is written at your chosen spot and every match spills into place from the single formula.
What it produces
The result is written from the output start cell you pick: the header is styled and static, and the records spill from a single anchor cell holding one dynamic array formula.
Source data
The source tables are left unchanged. When the source is an Excel Table, new rows join the lookup automatically.
How it differs from similar tools
VLOOKUP and XLOOKUP stop at the first hit; here every match arrives. Unlike filter-and-copy, the result is live: change the lookup value or the table and the list refreshes itself.
Good to know
If cells below the spill area are occupied, Excel raises a spill blockage; pick an empty area for the output. Range-based setups do not grow on their own the way tables do.
Limitations
Size limits (≥2×2).
Frequently asked questions
- Does it only return the first match?
- No. Every row matching the value is listed; that is precisely how it differs from VLOOKUP.
- Where does the result go?
- From the output start cell you pick. The header is static; the records fill through the spill of a single anchor formula.
- Does the result grow when I add table rows?
- Yes, when the source is an Excel Table; new rows join the lookup automatically. With a plain range the scope is fixed.
- Is a formula written into every cell?
- No. One dynamic array formula sits in one anchor cell; the rest fill through its spill.
- Is the source table modified?
- No. The source is only read; the result is built in the separate area you chose.
Works well with
- If one match per value is enough, use Lookup from Table
- To see how many times each value occurs, use Add Duplicate Count
- To find the records that do not match between two lists, use Compare Two Lists
- To fix text-stored numbers that break matching, use Text to Numbers
Your Excel data is processed on your own computer and is not shared with AI services. The limited data needed for licensing, sales and support is held on our infrastructure in Türkiye.