If first text value in a range is equal to
This tutorial shows 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 through the use of an Excel formula, with the IF, IFERROR and HLOOKUP or VLOOKUP functions
|
GENERIC FORMULA
=IFERROR(IF(HLOOKUP("*",rng,1,FALSE)=text,value_if_true,value_if_false),value_if_error)
ARGUMENTS GENERIC FORMULA
=IFERROR(IF(HLOOKUP("*",rng,1,FALSE)=text,value_if_true,value_if_false),value_if_error)
ARGUMENTS EXPLANATION This formula uses the IF, IFERROR and HLOOKUP or VLOOKUP functions 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.
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 first text value in a range is equal to "sport". If it does, the formula will return a text value of "Include", otherwise if the first 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 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 | |
| HLOOKUP Function | The Excel HLOOKUP function searches for a specific value in the first row of the selected range (table) and returns a value that resides in the same column as the lookup value from a specific row | |
| VLOOKUP Function | The Excel VLOOKUP function searches for a specific value in the first column of the selected range (table) and returns a value that resides in the same row as the lookup value from a specific column |