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
|
GENERIC FORMULA
=IFERROR(IF(LOOKUP(2,1/(ISTEXT(rng)),rng)=text,value_if_true,value_if_false),value_if_error)
ARGUMENTS GENERIC FORMULA
=IFERROR(IF(LOOKUP(2,1/(ISTEXT(rng)),rng)=text,value_if_true,value_if_false),value_if_error)
ARGUMENTS 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 Topic | Description | Related Topic and Description |
|---|---|---|
| If first text value in a range is equal to | 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 | |
| If first numeric value in a range is equal to | 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 | |
| If last numeric value in a range is equal to | 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 | Description | Related Functions and Description |
|---|---|---|
| IF Function | 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 | |
| IFERROR Function | 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 | |
| ISTEXT Function | 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 |