If first numeric value in a range is equal to
This tutorial shows 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 through the use of an Excel formula, with the IF, IFERROR, INDEX, MATCH and ISNUMBER functions
|
GENERIC FORMULA
=IFERROR(IF(INDEX(rng,MATCH(TRUE,INDEX(ISNUMBER(rng),0),0))=number,value_if_true,value_if_false),value_if_error)
ARGUMENTS GENERIC FORMULA
=IFERROR(IF(INDEX(rng,MATCH(TRUE,INDEX(ISNUMBER(rng),0),0))=number,value_if_true,value_if_false),value_if_error)
ARGUMENTS EXPLANATION This formula uses the IF, IFERROR, INDEX, MATCH and ISNUMBER functions 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.
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 first number in a range is equal to 500. If it does, the formula will return a text value of "Include", otherwise if the first 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 Topic | Description | Related Topic and Description |
|---|---|---|
| Count numeric cells in a range | How to count cells that contain numeric values in a specific range | |
| Return last numeric value in a column | How to return the last numeric value in a column |
| 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 | |
| ISNUMBER Function | 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 | |
| INDEX Function | The Excel INDEX function returns a value that is referenced from a specified range | |
| MATCH Function | The Excel MATCH function searches for a specified value in a range and returns its position relative to the range | |
| 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 |