Looking for ways to use ISNUMBER & MATCH function in Excel? Then, this is the right place for you. You can use both of these functions together and find matching values from a list or cell range. Here, you will find 2 ways to use ISNUMBER & MATCH functions in Excel.
Introduction to ISNUMBER Function
- Function Objective:
The ISNUMBER function is used to check if the value of a cell is a number or not.
- Syntax:
=ISNUMBER(value)- Arguments Explanation:
| ARGUMENT | REQUIRED/OPTIONAL | EXPLANATION |
| value | Required | Insert value to check if the value is a number or not |
- Return Parameter:
Returns TRUE if the value is a number, otherwise returns FALSE.
- Versions:
This function is available from Excel 2000.
Introduction to MATCH Function
- Function Objective:
The MATCH function is used to match values from a given cell range.
- Syntax:
=MATCH(lookup_value, lookup_array, [match_type]- Arguments Explanation:
| ARGUMENT | REQUIRED/OPTIONAL | EXPLANATION |
| lookup_value | Required | value which you want to match |
| lookup_array | Required | Cell range or an array where you want to find the value |
| match_type | Optional | Instructs how to find the match value.
Here, 1= Less than, 0= Exact match, -1= Greater than |
- Return Parameter:
Returns relative position of the lookup value.
- Versions:
This function is available from Excel 2003.
How to Use ISNUMBER & MATCH Functions in Excel: 2 Simple Ways
Here, we have a dataset containing Name and Marks of some students who passed in the exam. Now, using this dataset we want to check if some students Passed or Failed by matching their names.
You can easily do it by using the ISNUMBER and MATCH functions following the given 2 ways below.
1. Use MATCH Function Within ISNUMBER Function in Excel
In the first method, we will use the MATCH function within the ISNUMBER function to match the names from the given list with marks in Excel.
Here are the steps.
Steps:
- Firstly, select Cell F5 and insert the following formula.
=ISNUMBER(MATCH(E5,$B$5:$B$12,0))- After that, press Enter and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Thus, you will find which student has passed or failed from the list.
🔎 How Does the Formula Work?
- To start with, we used the MATCH function to find the relative position of the lookup value.
- After that, we used the ISNUMBER function to check if the value is a number or not.
2. Apply IF Function with MATCH & ISNUMBER Functions in Excel
Additionally, we will use the IF function with MATCH and ISNUMBER functions to return a value you wish to have if the return value of ISNUMBER is True and another value for FALSE.
Here are the steps to do that.
Steps:
- In the beginning, insert the following formula in Cell C6.
=IF(ISNUMBER(MATCH(E5,$B$5:$B$12,0)),"Passed","Failed")- Then, press Enter and drag down the Fill Handle tool for the rest of the cells.
- Finally, you will get results as Passed or Failed in the adjacent cells of the names.
🔎 How Does the Formula Work?
- Firstly, we used the MATCH and ISNUMBER functions to find the value of Cell E5 in cell range B5:B12 and return TRUE if found otherwise return FALSE.
- After that, we used the IF function to return “Passed” if the result of the ISNUMBER function TRUE or else return “Failed”.
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
Conclusion
So, in this article, you will find 2 ways to use ISNUMBER & MATCH functions in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Additionally, let us know any other approaches which we might have missed here. Thank you!
