Function Problems

Excel Exact Lookup for Unsorted Data with INDEX and MATCH

Tauseeq MagsiTauseeq Magsi Nov 22, 2026 868 views

Question details

Find an exact match in an unsorted customer column and return the corresponding date from a column to its left.

Find Exact Matches in Unsorted Excel Data
Product
Microsoft Excel
Device & OS
not provided
Scenario
Dates are in column A and customer actions are in columns to the right; sorting each customer column is impractical.
Observed behavior
The desired formula should find an exact action match without sorting and return the date from the same row.
Before you start

Check that the date and action ranges cover the same rows. Identify the exact action text to find, such as Contract, and the cell where you want the result.

Solution 1Recommended

Use INDEX and MATCH for an Exact Lookup

Use this formula when the lookup column is unsorted and the date is in a column to its left.

1
Select the result cell

Click the cell where you want the matching date to appear, such as B14.

2
Enter the formula

Enter =INDEX($A$2:$A$11,MATCH("Contract",B$2:B$11,0)) and press Enter. MATCH finds the first exact match in column B, and INDEX returns the date from the same row in column A.

3
Copy across customer columns

If other customer actions are in adjacent columns, drag the result cell's fill handle to the right. The lookup column changes while the date range stays fixed.

4
Format the result as a date

If the result appears as a number, select the result cell and choose a date format from Home > Number Format.

Use INDEX and MATCH for an Exact Lookup
First match and missing values: The formula returns the first matching row, not necessarily the latest date. If no exact match exists, MATCH returns #N/A.
Work with spreadsheets in WPS Office

Find Exact Matches with WPS Spreadsheets

Use INDEX and MATCH in WPS Spreadsheets to find an action in unsorted data and return its corresponding date.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Select a result cell: Click an empty cell, such as B14, where the matching date should appear.
  3. 3. Enter the lookup formula: Type =INDEX($A$2:$A$11,MATCH("Contract",B$2:B$11,0)) and press Enter. Adjust the ranges and action text to match your sheet.
  4. 4. Copy and format: Drag the cell's fill handle across for other customer columns. If needed, select the results and choose a date format from the number-format options on the Home tab.
Find exact matches without sorting customer columnsReturn a date from a column to the left of the lookup columnOpen and edit Microsoft Excel XLSX and XLS filesCopy the formula across adjacent customer columns
microsoft office alternative - wps office

Frequently Asked Questions

Do I need to sort the customer column first?

No. MATCH with 0 as its third argument searches for an exact match, so the lookup range does not need to be sorted.

What happens if Contract appears more than once?

MATCH returns the position of the first exact match in the selected range. INDEX then returns the date from that row, which may not be the latest date.

Why does the formula return #N/A?

The lookup range may not contain an exact match. Check the action text and the range in MATCH, including any extra spaces in the cells.

Why does the returned date appear as a number?

Excel stores dates as serial numbers. Select the result cell and choose a date format from Home > Number Format.

How can I make this data easier to maintain?

Consider organizing it as a table with Customer, Action, and Date columns. Select the data and use Insert > Table to create a structured table.