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

Introduction to ISNUMBER Function

Function Objective

The ISNUMBER function checks whether the value of a cell is a number or not.

Syntax

=ISNUMBER(value)

Arguments Explanation

ARGUMENT	REQUIRED/OPTIONAL	EXPLANATION
value	Required	Insert the value you want to check if it is a number or not.

Return Parameter

Returns TRUE if the value is a number, otherwise returns FALSE.

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	The value you want to match
lookup_array	Required	The 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 the relative position of the lookup value.

Dataset Overview

Let’s say we have a dataset containing Names and Marks of some students who passed an exam. We want to check if certain students Passed or Failed by matching their names. Here are the two methods:

Method 1 – Use MATCH Function Within ISNUMBER Function in Excel

In this method, we’ll use the MATCH function within the ISNUMBER function to match the names from the given list with marks in Excel. Follow these steps:

Select cell F5 and insert the following formula:

=ISNUMBER(MATCH(E5,$B$5:$B$12,0))

Press Enter and drag down the Fill Handle tool to auto-fill the formula for the rest of the cells.

This will help you determine which student has passed or failed from the list.

How Does the Formula Work?

First, we use the MATCH function to find the relative position of the lookup value.
Then, we use the ISNUMBER function to check if the value is a number or not.

Method 2 – Apply IF Function with MATCH & ISNUMBER Functions in Excel

Additionally, we can use the IF function with MATCH and ISNUMBER functions to return a value based on whether the result of ISNUMBER is true or false. Follow these steps:

In cell C6, insert the following formula:

=IF(ISNUMBER(MATCH(E5,$B$5:$B$12,0)),"Passed","Failed")

Press Enter and drag down the Fill Handle tool for the rest of the cells.

This will give you results as Passed or Failed in the adjacent cells next to the student names.

How Does the Formula Work?

First, we use the MATCH and ISNUMBER functions to find the value of cell E5 in the cell range B5:B12 and return TRUE if found; otherwise, return FALSE.
Then, the IF function handles the final output.

Practice Section

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