Excel LOOKUP Formula to Exclude a Value and Return the Last Nonblank Cell
Question details
Find the last nonblank value in an Excel row while excluding a specified value.

- 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.
Identify the row range to search and the value to exclude. The example below searches A2:M2 and excludes the numeric value 90.
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.
In Excel, click a cell outside A2:M2 where you want the result to appear.
Type =LOOKUP(1,1/(A2:M2<>"")/(A2:M2<>90),A2:M2) in the cell or formula bar, then press Enter.
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".

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. Open your spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheets.
- 2. Select a result cell: Click a cell outside the row range you want to search, such as a cell outside A2:M2.
- 3. Enter the LOOKUP formula: Type =LOOKUP(1,1/(A2:M2<>"")/(A2:M2<>90),A2:M2) in the formula bar and press Enter.
- 4. Adapt the formula: Change all three A2:M2 references to your actual range and replace 90 with the value you want to exclude.

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.




