If last text value in a range is equal to

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

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

EXCEL

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

=IFERROR(IF(LOOKUP(2,1/(ISTEXT(rng)),rng)=text,value_if_true,value_if_false),value_if_error)

ARGUMENTS
rng: A range of values in which you want to find the last text value that is equal to a specific number.
text: A text value that you want to test if the last text value in a range is equal to.
value_if_true: Value to be returned if the last text in a range is equal to a specific number.
value_if_false: Value to be returned if the last text 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/(ISTEXT(rng)),rng)=text,value_if_true,value_if_false),value_if_error)

ARGUMENTS
rng: A range of values in which you want to find the last text value that is equal to a specific number.
text: A text value that you want to test if the last text value in a range is equal to.
value_if_true: Value to be returned if the last text in a range is equal to a specific number.
value_if_false: Value to be returned if the last text 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 ISTEXT functions to test if the last text value in a range is equal to a specific text 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 text 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 text value in a range is equal to "sport". If it does, the formula will return a text value of "Include", otherwise if the last text value in a range is equal to anything else but "sport" the formula will return a text value of "Exclude". If a range doesn't contain any text 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 text 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 text values in a selected range.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to test if the first text value in a range is equal to a specific text and return a value if the test is True or False
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 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

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 IFERROR function returns a specified value if the first value in the IFERROR formula returns an error, otherwise it will return the standard result
The Excel ISTEXT function tests a specified value (cell) if it's a text value and returns TRUE if it's a text value or FALSE if it's not a text value