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


Download Practice Workbook

You are welcome to download the practice workbook from the link below.


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.


2 Simple Ways to Use ISNUMBER & MATCH Functions in Excel

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.

Read More: [Fixed!] Excel MATCH Function Not Working


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

Read More: How to Find Intersection of Two Lists in Excel (3 Easy Methods)


Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


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. And visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin Islam

Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo