To return every matching row for one lookup value in Microsoft 365, use FILTER rather than VLOOKUP. With order IDs in A2:A10, customer names in B2:B10, amounts in C2:C10, and the requested customer in F2, enter =FILTER(A2:C10,B2:B10=F2,"No matches"); if F2 contains Ana, it returns all of Ana’s matching orders, not just the first one.
“VLOOKUP multiple values” can mean several different things: finding multiple matching records, looking up several search terms, returning more than one column, or matching on multiple conditions. The right formula depends on which result you need. VLOOKUP is useful for a single match, but its standard behavior is to return the first match it finds.
What “multiple values” can mean
Before choosing a formula, decide what should appear in the result. A request for “multiple values” often means one of these:
- Every row matching one search term: for example, all orders placed by Ana. Use FILTER in supported Excel versions.
- Results for several search terms: for example, the amount for each of three order IDs. Use a lookup formula with a list of search values, or copy a single-value formula down.
- Several fields from one matching row: for example, the customer and amount for one order. VLOOKUP can return multiple columns in current Excel.
- One row matching two conditions: for example, a customer and a region together. Use a multi-condition formula or a helper column.
These are different lookup problems. A formula that returns several columns for one order does not automatically return every order belonging to a customer.
Return every matching row with FILTER
FILTER is generally the simplest way to get all matching records. In the order layout from the opening example, the formula checks each value in B2:B10 against F2 and returns the corresponding rows from A2:C10. If Ana appears on three orders, all three rows spill into the cells below and to the right of the formula. The example’s optional third argument displays “No matches” if there are no results.
FILTER is available in Microsoft 365 and Excel 2021 or later. Keep the cells where results will spill clear; content blocking the output can produce a #SPILL! error. The formula updates when source data changes, as long as the referenced ranges include the new rows. For a growing list, consider converting the data to an Excel table so formulas can use structured references and expand with the table.
To return only one column, use B2:B10 as the first argument instead of A2:C10. To return selected columns that are not adjacent, create a suitable output range or use functions such as CHOOSECOLS where available; FILTER itself returns the columns included in its first argument.
Match on more than one condition
Suppose the data uses order IDs in A2:A10, customers in B2:B10, regions in C2:C10, and amounts in D2:D10. Put the desired customer in F2 and region in G2. To return all order rows where both conditions are true, use =FILTER(A2:D10,(B2:B10=F2)*(C2:C10=G2),"No matches"). If Ana has two East-region orders, the result contains those two records and excludes her orders in other regions.
The multiplication sign between the condition tests acts as AND: a row is included when both tests evaluate as true. For OR logic—customer is Ana or region is East—use addition between the tests instead. These tests must cover ranges of the same size as the data being filtered. If one range starts on row 2 and another starts on row 3, Excel may return an error or the wrong result.
VLOOKUP cannot directly search two separate criteria in its standard lookup-value argument. A helper column is one workable option when you need to keep using VLOOKUP. For this separate layout, put customer names in A2:A10 and regions in B2:B10. In D2, create a combined key with =A2&"|"&B2, then fill it down. Put amounts in E2:E10, the desired customer in F2, and the region in G2. With that setup, use =VLOOKUP(F2&"|"&G2,D2:E10,2,FALSE); for Ana and East, it returns the amount from the first matching combined key.
Choose a separator that cannot be confused with the source values, or use a more robust key-building method if the data might contain that separator. A helper key can also match unintended pairs if values are inconsistently typed or spaced. Cleaning the source data and checking that the combined key is unique can prevent hard-to-spot errors.
Look up several search values
If you have a list of order IDs and want the amount for each, you do not need to search for all IDs as one combined value. In the order layout with IDs in A2:A10 and amounts in C2:C10, place the requested IDs in E2:E4. In Microsoft 365 or Excel 2021 and later, use =XLOOKUP(E2:E4,A2:A10,C2:C10,"Not found"). If E2:E4 contains 1001, 1002, and 1003, and those orders have amounts of 120, 90, and 75, respectively, Excel returns those three amounts as a spilled list.
XLOOKUP is often clearer than VLOOKUP for this task because the lookup range and return range are specified separately. It also returns the first match for each search value, so repeated order IDs in the source still do not produce every matching row. Use FILTER when the goal is to return all records for a search term.
If you specifically need VLOOKUP, enter a single-value formula beside the first search term and fill it down. With order IDs in A2:A10, amounts in C2:C10, and the first requested ID in E2, use =VLOOKUP(E2,$A$2:$C$10,3,FALSE). For E2 containing 1001, the result is 120. The dollar signs keep the source range fixed as you copy the formula down; the reference to E2 changes to E3, E4, and so on.
Return multiple columns from one match
VLOOKUP can return more than one column from a matching row in current Excel. With order IDs in A2:A10, customer names in B2:B10, amounts in C2:C10, and the order ID in F2, enter =VLOOKUP(F2,A2:C10,{2,3},FALSE). If F2 is 1001 and that row contains Ana and 120, the formula returns both values in adjacent cells. The column numbers 2 and 3 refer to the second and third columns of the table range, not to worksheet columns B and C in every possible layout.
This is still a single-match lookup: it returns the first row for the ID, with two fields from that row. It does not find every row where a customer name repeats. The result also needs clear adjacent cells to spill. In older Excel versions, array behavior may differ, so the formula may not spill as it does in Microsoft 365; use separate formulas for each return column if needed.
When VLOOKUP is still the right choice
VLOOKUP remains useful when the lookup key is in the leftmost column of a table and you need a value from a column to its right. Its approximate-match mode can also support certain sorted lookup tasks, though for ordinary exact searches you should specify FALSE as the fourth argument. Without an explicit match mode, VLOOKUP defaults to approximate matching, which can return an unexpected result if the first column is not sorted as required.
Its main limitations matter for multiple-value questions: standard VLOOKUP returns only the first matching row; the return column is identified by a numeric position; and the lookup column must be the first column in the selected table range. XLOOKUP avoids the leftmost-column restriction, while FILTER is designed to return multiple matching rows. Both XLOOKUP and FILTER are available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual versions.
Common problems and how to fix them
- Only one result appears: that is normal for ordinary VLOOKUP. Use FILTER to return all matching rows, or check whether you actually wanted multiple columns from one row.
- #N/A appears: confirm the lookup value exists and that numbers are stored as numbers rather than text. For exact VLOOKUP matches, include FALSE.
- Wrong row is returned: verify the match mode. Approximate matching is not a substitute for an exact lookup, and its sorted-data requirement is easy to overlook.
- #SPILL! appears: clear cells in the intended output area. Dynamic-array formulas cannot place results over existing content or merged cells.
- Some expected matches are missing: look for extra spaces, inconsistent spelling, hidden characters, or ranges that stop before the last data row.
- A multi-condition result is too broad or too narrow: check whether the logic should be AND or OR, and make sure each condition range lines up row by row.
For repeated work, using an Excel table can reduce range-maintenance errors. Select the data and choose Insert > Table, then use the table’s named columns in formulas. Table references are easier to read than large blocks of cell addresses, though the exact formula syntax depends on the names assigned to the table and its columns.
Frequently Asked Questions
Can VLOOKUP return all matches?
Not with its standard lookup behavior: VLOOKUP returns the first matching row. Use FILTER in Microsoft 365 or Excel 2021 and later to return every matching record.
How do I return multiple columns with VLOOKUP?
In current Excel, use an array of column indexes, such as 2 and 3, as the third argument. The matching row’s values spill into adjacent cells. This returns multiple fields from one match, not multiple matching rows.
How do I use VLOOKUP with two criteria?
Create a helper column that combines the two criteria, then look up the combined key. Make sure the key is built consistently and does not create ambiguous combinations. FILTER can handle multiple conditions without a helper column in supported versions.
Can I search for several values at once?
Yes. In Microsoft 365 or Excel 2021 and later, XLOOKUP can accept a range of lookup values and return a corresponding spilled list. Alternatively, copy a single-value VLOOKUP formula down beside the list of search terms.
Why does VLOOKUP show only the first duplicate?
That is how VLOOKUP is designed: it stops at the first match. If duplicates represent separate records you need to see, filter the source with FILTER rather than expecting VLOOKUP to enumerate them.
Choosing the right formula
For a single match and a familiar left-to-right table, VLOOKUP with FALSE is a straightforward choice. For one match with several return fields, current Excel can spill multiple VLOOKUP columns. For a list of search terms, XLOOKUP can return a result for each term. For every row meeting one or more conditions, FILTER is usually the better fit. Identifying whether “multiple” refers to rows, search terms, columns, or criteria is the quickest way to select a formula that returns the result you actually need.
Related
- Excel Data Analysis Skills: What They Include and Why
- Excel Solver for Data Analysis: Setup, Models, and Limits
- Cash Flow Excel Sheet: Build a Reliable Monthly Forecast
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- What-If Analysis in Excel: A Step-by-Step GuideTo add what-if analysis in Excel, open the Data tab and choose Forecast > What-If Analysis, then select…
- Remove Duplicates in Excel: Sort Data the Right WayTo remove duplicates in Excel and keep the right record, sort your data first if the order determines…
- Excel Length of Service Formula: Calculate Years and MonthsIf the start date is in B2 and the date to measure service through is in C2, use…
- How to Add Parentheses in Excel Formulas CorrectlyTo add parentheses in an Excel formula, edit the cell, type an opening parenthesis where you want a…
- Excel Conditional Formatting: Use a Formula for RulesYes. In Excel, conditional formatting can use a formula that evaluates to TRUE or FALSE to decide which…
- Excel Named Range Not Appearing in Formula? FixesIf an Excel named range is not appearing as you type a formula, first confirm that the name…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.