Formula Errors

Excel LOOKUP Formula to Exclude a Value and Return the Last Nonblank Cell

Steve KSteve K Nov 18, 2026 868 views

Question details

Find the last nonblank value in an Excel row while excluding a specified value.

Exclude a Specific Value from an Excel LOOKUP Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
A row contains values in A2:M2, and the user wants the last value that is neither blank nor equal to 90.
Observed behavior
The desired result is the rightmost qualifying value in the row.
Before you start

Identify the row range to search and the value to exclude. The example below searches A2:M2 and excludes the numeric value 90.

Solution 1Recommended

Add an Exclusion Condition to the Excel LOOKUP Formula

Use this formula to return the last nonblank value in a row that is not equal to 90.

Each condition in the denominator produces an error for cells that should be excluded. LOOKUP ignores those errors and returns the value corresponding to the last qualifying cell.

1
Select an output cell

In Excel, click a cell outside A2:M2 where you want the result to appear.

2
Enter the formula

Type =LOOKUP(1,1/(A2:M2<>"")/(A2:M2<>90),A2:M2) in the cell or formula bar, then press Enter.

3
Adjust the criteria

Replace each A2:M2 reference with your row range and replace 90 with the value you want to exclude. For a text value, put it in quotation marks, such as <>"Pending".

Add an Exclusion Condition to the Excel LOOKUP Formula
When no value qualifies: The formula returns #N/A if every cell in the range is blank or excluded. To show a message instead, use =IFERROR(LOOKUP(1,1/(A2:M2<>"")/(A2:M2<>90),A2:M2),"No match").
WPS Spreadsheets

Find the Last Eligible Row Value in WPS Spreadsheets

Use the same LOOKUP conditions in WPS Spreadsheets to return the last nonblank row value while excluding 90.

  1. 1. Open your spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheets.
  2. 2. Select a result cell: Click a cell outside the row range you want to search, such as a cell outside A2:M2.
  3. 3. Enter the LOOKUP formula: Type =LOOKUP(1,1/(A2:M2<>"")/(A2:M2<>90),A2:M2) in the formula bar and press Enter.
  4. 4. Adapt the formula: Change all three A2:M2 references to your actual range and replace 90 with the value you want to exclude.
Exclude a specific value from a row lookupOpen and edit Microsoft Excel XLSX filesWork with familiar spreadsheet formulas
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula ignore blank cells and 90?

The conditions A2:M2<>"" and A2:M2<>90 evaluate to false for excluded cells. Dividing by those false results creates errors in the lookup array, which LOOKUP ignores.

Do I need ARRAYFORMULA in Excel?

No. Enter =LOOKUP(1,1/(A2:M2<>"")/(A2:M2<>90),A2:M2) directly in an Excel cell. ARRAYFORMULA is a Google Sheets function.

How can I exclude a text value instead of 90?

Replace the numeric condition with a quoted text condition. For example, use =LOOKUP(1,1/(A2:M2<>"")/(A2:M2<>"Pending"),A2:M2) to exclude Pending.

What if there are no matching values?

LOOKUP returns #N/A when no cell qualifies. Wrap the formula in IFERROR and specify a message, such as "No match", to display that message instead.