Excel MATCH: Find, Filter, and Export Matching Data

Formulas & functions · Updated November 18, 2026

Excel’s MATCH function does not export data: it finds the position of a value in a range. To export matching records, use MATCH to identify them, filter or return the complete rows, then copy those results to a separate sheet or workbook and save that file as Excel or CSV.

What MATCH does in Excel

MATCH searches one row or one column and returns a number showing where the first matching item sits within that lookup range. It does not return the matched value or the entire record, and it does not create a separate export file. Think of it as a locator that other steps or formulas can use.

For example, a product list might have SKUs in A2:A100, product names in B2:B100, and a SKU to find in E2. With the search value in E2 and the lookup range in A2:A100, the formula below returns 6 if the SKU is in A7. The result is relative to the first cell of the lookup range, not the worksheet row number.

=MATCH(E2,A2:A100,0)

The final argument, 0, requests an exact match. That is usually the right choice for names, IDs, order numbers, and other records you intend to export. MATCH returns the position of the first matching item when the lookup range contains duplicates.

Choose exact matching for record lists

MATCH has three match modes. With 0, it looks for an exact match and does not require the data to be sorted. With 1, it finds the largest value less than or equal to the lookup value, and the lookup range must be sorted in ascending order. With -1, it finds the smallest value greater than or equal to the lookup value, and the range must be sorted in descending order. Using an approximate mode on an unsorted list can produce a misleading result.

Exact matching is not the same as case-sensitive matching: MATCH generally treats uppercase and lowercase letters as equivalent. It can also miss values that look alike but are stored differently, such as the number 123 and the text value 123. Extra spaces can cause problems too. If a lookup unexpectedly returns #N/A, check the underlying cell values, not just their appearance.

Return the matching value or a complete record

To get a related value after MATCH locates a row, combine it with INDEX. Suppose SKUs are in A2:A100, product names are in B2:B100, and the requested SKU is in E2. This formula returns the product name from the corresponding row; if the SKU occurs in A7, the result is the value in B7.

=INDEX(B2:B100,MATCH(E2,A2:A100,0))

That is useful for looking up one result, but it is not a practical way to export many matching records one at a time. If you need a larger result set, identify all qualifying rows first. For example, suppose source records occupy A2:C100 and the list of wanted SKUs is in F2:F20. Add a helper column beside the source data. With the source SKU in A2, this formula returns TRUE if A2 appears in the wanted list and FALSE otherwise:

=ISNUMBER(MATCH(A2,$F$2:$F$20,0))

Fill the formula down for the source rows, then filter the helper column to TRUE. In Microsoft 365 or Excel 2021, you can also return matching rows with FILTER. With records in A2:C100, the SKU field in A2:A100, and the requested SKU list in F2:F20, this returns the full rows whose SKU appears in that list. If the example data contains three matching records, the result is three rows by three columns.

=FILTER(A2:C100,ISNUMBER(MATCH(A2:A100,F2:F20,0)),"No matches")

FILTER spills its results into neighboring cells, so leave the output area clear. It is available in Microsoft 365 and Excel 2021; older Excel versions can use a helper column and AutoFilter instead. A filtered view is often the easiest route when the export needs to include the original columns and their existing values.

Export matching rows to Excel or CSV

For a clean export, keep the source data intact and place the results on a separate worksheet or in a new workbook. This avoids accidentally changing the original list and makes it easier to verify exactly what will be sent. A dependable workflow is:

  1. Turn the source range into an Excel table or confirm that it has clear column headings and no blank rows splitting the data.
  2. Use a helper column with MATCH, or use FILTER if your Excel version supports it, to identify the rows you need.
  3. Check the results for missing values, duplicates, and unexpected records. If you used AutoFilter, make sure the visible rows represent the intended criteria.
  4. Copy the output to a new worksheet or workbook. If you used a formula, use Paste Special > Values when you want a fixed snapshot that does not depend on the source data or formulas.
  5. Choose File > Save As, select the destination and file type, and save the new file. Use an Excel workbook (.xlsx) to retain worksheets, formatting, and formulas, or CSV UTF-8 (Comma delimited) (.csv) for a plain-text data file.

A crucial detail: filtering a worksheet hides rows; it does not remove them from the sheet. Do not assume that saving a filtered source sheet as CSV will export only the visible records. Copy the filtered results to a clean sheet or workbook first, then save that export. In Excel for Windows, Home > Find & Select > Go To Special > Visible cells only can help select visible cells before copying a filtered range. Check the copied result before saving.

CSV has no support for multiple worksheets, formulas, cell formatting, or Excel tables. It represents the data in one sheet as text separated by commas, so the CSV export contains the active worksheet’s data rather than an entire multi-sheet workbook. If formulas are present, the exported file reflects their displayed results, not the formulas as reusable Excel calculations. Reopen the saved file or inspect it in a text editor if it is important to confirm the output.

Choose the right export format

Use .xlsx when the recipient needs a working spreadsheet, multiple tabs, formulas, formatting, filters, or data validation. Use CSV when another system specifically requests a simple, single-table data file. CSV is widely supported, but it does not preserve the spreadsheet features people may expect from an Excel workbook.

For names or other text containing accented characters or symbols, CSV UTF-8 is generally a safer choice than a legacy CSV encoding. A CSV file can also display incorrectly if the receiving program expects a different delimiter or encoding. If the destination system specifies a format, follow its requirements; do not change commas to another separator unless that system calls for it.

For a repeatable export, consider keeping one worksheet for source data, one for criteria, and one for output. Give the export clear headers, remove irrelevant columns, and avoid including helper formulas unless they are part of the requested data. If the output is intended as a snapshot, paste values before saving so later changes to the source do not alter the exported results.

Common MATCH and export problems

#N/A: An exact match was not found. Check whether the value exists, whether one entry is stored as text while the other is numeric, and whether either contains leading or trailing spaces. Be careful when cleaning data: removing spaces or changing number formats can alter identifiers, especially values with leading zeroes.

Wrong row or value: Confirm that the lookup range begins and ends on the same rows as the return range, and that the final MATCH argument is 0 for an exact lookup. Remember that the position MATCH returns starts at 1 within its range. If the range begins at row 2, a position of 6 points to worksheet row 7.

Only one duplicate appears: MATCH returns the position of the first matching item, so INDEX/MATCH returns one corresponding value. For all records matching a criterion, use a helper-column filter or FILTER rather than expecting MATCH alone to list every duplicate.

Export includes extra records or misses the latest ones: Check that the filter range includes all data and that copied results contain only the intended rows. Excel tables expand as data is added and can make ranges easier to manage. If you used a formula-based result, verify that its source and criteria ranges include new entries.

CSV looks different after opening: CSV does not preserve Excel formatting, and another application may interpret dates, leading zeroes, or delimiters differently. Keep an .xlsx master copy, and check the CSV in the receiving application when the import format matters.

Excel for Mac and Excel for the web

The MATCH, INDEX, and FILTER formulas described here work in supported Excel editions on Windows and Mac. Menu labels and dialog layouts can vary slightly between platforms. On Mac, use File > Save As and choose the desired file format in the save dialog. Excel for the web can edit and save Excel workbooks, but a downloadable copy or a desktop Excel workflow may be more convenient when you need to create a particular CSV export. Always verify which sheet and records are being saved.

In older desktop Excel versions without FILTER, a helper column is a straightforward alternative: calculate whether each source row appears in the criteria list, apply Data > Filter, and copy the qualifying rows to a clean destination. This approach also makes the match decision visible, which can help when another person needs to audit the export.

Frequently Asked Questions

Can MATCH export data from Excel?
No. MATCH returns a position within a range. Use it with a helper column or another function to find records, then copy the resulting rows and save them as an .xlsx or CSV file.

How do I export only matching rows?
Mark rows with a helper formula that checks MATCH, filter for TRUE, and copy the matching rows to a clean sheet or workbook. In Microsoft 365 or Excel 2021, FILTER can return matching rows directly.

Does MATCH return every duplicate?
No. MATCH returns the position of the first match. Use a filter or FILTER formula if you need every row that meets the condition.

Should I export as XLSX or CSV?
Choose XLSX when you need formulas, formatting, or multiple worksheets. Choose CSV when the recipient needs a plain-text table for another system, and confirm its encoding and delimiter requirements.

Why does my MATCH formula return #N/A?
The lookup value may be absent, may have extra spaces, or may be stored as text while the corresponding entry is a number. Check the values and use 0 as MATCH’s final argument when you want an exact match.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.