Function Problems

Excel Formula to Find the Last Matching Cell Above a Row

Adam Davis Oct 8, 2026 868 views

Question details

The user needs a formula to search upward in a column for a specific string (like an email domain) and return the value from the cell exactly one row above the last matched criteria.

Excel Formula to Find the Last Matching Cell Above a Row

Product: Microsoft Excel
Device & OS: not provided
Scenario: Locating the last instance of a specific text string within a dataset and extracting data positioned relative to that match, specifically offset by minus one row.
Observed behavior: The user successfully retrieves the target cell value by using advanced lookup formulas with strategically offset ranges or bottom-to-top search parameters.

Before you start

Ensure your dataset does not contain merged cells, as this can disrupt the row indexing and cause array formulas to return incorrect offsets.

Solution 1Recommended

Use the LOOKUP and SEARCH Function Combination

This method works in all versions of Excel to search for a text string and return the value directly above the last matching occurrence.

By intentionally offsetting the search range and the return range by one row, the LOOKUP function naturally retrieves the cell situated exactly one row above the matched criteria.

Select the destination cell

Click on the empty cell where you want the final extracted value to be displayed.

Enter the LOOKUP formula

Type the formula =LOOKUP(2,1/SEARCH("example.cz",A2:A10),A1:A9) into the formula bar. Replace "example.cz" with the actual text or email domain you are trying to find.

Confirm the offset ranges

Verify that your search range (A2:A10) starts and ends exactly one row below your return range (A1:A9). Press Enter to apply the calculation.

Use the LOOKUP and SEARCH Function Combination

Understanding the Array Math: Dividing 1 by the SEARCH result creates an array of 1s and errors. LOOKUP(2) ignores the errors and matches the final 1 in the array, thereby locating the very last occurrence in your data.

Solution 2

Use XMATCH and INDEX (Microsoft 365 & Office 2021)

A modern and highly robust approach for newer spreadsheet versions that natively support searching from bottom to top.

Efficient Spreadsheet Alternative

Easily Handle Complex Array Formulas in WPS Spreadsheet

WPS Spreadsheet provides flawless support for advanced data lookup functions like LOOKUP, INDEX, and XMATCH. You can process heavy array calculations and align offset ranges perfectly without any performance lag.

1. Open your data file: Launch WPS Spreadsheet and open your existing .xlsx or .csv data file.
2. Select the target cell: Click on the blank cell where you want to execute your bottom-up lookup.
3. Apply the formula: Type your LOOKUP or INDEX formula into the formula bar, ensuring your search and return ranges are properly offset.
4. Execute the calculation: Press Enter to instantly fetch the corresponding cell value from the row above your matched criteria.

100% compatible with Microsoft Excel formulas (.xlsx and .xls)Lightweight architecture ensures fast performance even with complex array calculationsFree built-in data analysis, filtering, and visualization toolsFamiliar interface makes migrating from Microsoft Excel seamless

microsoft office alternative - wps office

Frequently Asked Questions

Why does my LOOKUP formula return an #N/A error?

This error occurs if the target string cannot be found anywhere in the specified search range. Verify your spelling, check for hidden trailing spaces in the dataset, and ensure you are using wildcards correctly if needed.

Can I reference a range on a different worksheet with this formula?

Yes, you can pull data from another sheet by appending the sheet name followed by an exclamation mark before the cell range, such as =LOOKUP(2,1/SEARCH("text",Sheet2!A2:A10),Sheet2!A1:A9).

Why does the XMATCH formula display a #NAME? error?

The XMATCH function was introduced in Microsoft 365, Office 2021, and recent versions of WPS Office. If you are using an older spreadsheet version, the program will not recognize XMATCH. In that case, use the LOOKUP method instead.

What happens if I don't offset the ranges in the LOOKUP formula?

If both the search range and the return range are identical (e.g., both A1:A10), the formula will return the value from the exact same row where the match is found, rather than the row above it.

Excel & Data Problems