If last numeric value in a range is equal to

This tutorial shows how to test if the last number in a range is equal to a specific number and return a value if the test is True or False through the use of an Excel formula, with the IF, IFERROR, LOOKUP and ISNUMBER functions

EXCEL FORMULA 1. If last numeric value in a range is equal to

EXCEL

Hard coded formula
If last numeric value in a range is equal to
Cell reference formula
If last numeric value in a range is equal to
=IFERROR(IF(LOOKUP(2,1/(ISNUMBER(C5:E5)),C5:E5)=500,"Include","Exclude"),"Exclude")
=IFERROR(IF(LOOKUP(2,1/(ISNUMBER(C10:E10)),C10:E10)=$C$5,$C$6,$C$7),$C$7)
GENERIC FORMULA

=IFERROR(IF(LOOKUP(2,1/(ISNUMBER(rng)),rng)=number,value_if_true,value_if_false),value_if_error)

ARGUMENTS
rng: A range of values in which you want to find the last numeric value that is equal to a specific number.
number: A number that you want to test if the last numeric value in a range is equal to.
value_if_true: Value to be returned if the last number in a range is equal to a specific number.
value_if_false: Value to be returned if the last number in a range is not equal to a specific number.
value_if_error: Value to be returned if the formula returns an error.

GENERIC FORMULA

=IFERROR(IF(LOOKUP(2,1/(ISNUMBER(rng)),rng)=number,value_if_true,value_if_false),value_if_error)

ARGUMENTS
rng: A range of values in which you want to find the last numeric value that is equal to a specific number.
number: A number that you want to test if the last numeric value in a range is equal to.
value_if_true: Value to be returned if the last number in a range is equal to a specific number.
value_if_false: Value to be returned if the last number in a range is not equal to a specific number.
value_if_error: Value to be returned if the formula returns an error.

EXPLANATION

This formula uses the IF, IFERROR, LOOKUP and ISNUMBER functions to test if the last number in a range is equal to a specific number and return a value if the test is True or False.
Click on either the Hard Coded or Cell Reference button to view the formula that has the numeric value to be tested for and the return values directly entered into the formula or referenced to specific cells.

In this example the formula tests if the last number in a range is equal to 500. If it does, the formula will return a text value of "Include", otherwise if the last number in a range is equal to anything else but 500 the formula will return a text value of "Exclude". If a range doesn't contain any numbers the formula would return an error without the use of an IFERROR function. We therefore have used the IFERROR function to return a text value of "Exclude", meaning if there are no numeric values in a range the formula will return a text value of "Exclude". Please take caution applying the IFERROR function as this will return a text value of "Exclude" if the formula returns an error, even if there are numeric values in a selected range.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to test if the first number in a range is equal to a specific number and return a value if the test is True or False
How to count cells that contain numeric values in a specific range
How to return the last numeric value in a column

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE
The Excel ISNUMBER function tests a specified value (cell) if it's a numeric value and returns TRUE if it's a text value or FALSE if it's not a numeric value
The Excel IFERROR function returns a specified value if the first value in the IFERROR formula returns an error, otherwise it will return the standard result