How to Use ISNUMBER & MATCH Function in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

isnumber match function excel


Introduction to ISNUMBER Function

Syntax of 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

Syntax of 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.

Dataset to use ISNUMBER and MATCH functions in Excel


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.

Using Excel MATCH Function Within ISNUMBER Function

  • Thus, you will find which student has passed or failed from the list.

Results found after using MATCH Function Within ISNUMBER Function

🔎 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.

Applying IF function

  • Finally, you will get results as Passed or Failed in the adjacent cells of the names.

Final Output

🔎 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.

Practice Section


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 that we might have missed here. Thank you!


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo