MATCH Function:

MATCH Function:

  

 

Summary

The MATCH function searches an item in a range of cells and then returns the relative position of that item in the range. For example, if the range is H4:H7 contains the values SONU, PAWAN, HARUN, and RITTULBHAI, then the formula =MATCH("HARUN",$H$4:$H$7,0) returns the number 3 because HARUN is the third item in the range.

Trick Tip: You can use the MATCH function to provide a value for the row_num argument of the INDEX function, using this function you don't need to write numbers manually. So, advise you to use this MATCH function with INDEX function, VLOOKUP, HLOOKUP, etc.

First will let you know about the purpose and syntax of the same.

 

Purpose

The MATCH function can use to searches an item in a range of cells and then returns the relative position of that item in the range.

Arguments & Syntax

 

 As given above syntax contain 3 different arguments, meaning of the same is given below.

 

Arguments	Meaning
lookup_value	The value is common in both the data and looking for the position of the same in the array table.
lookup_array	The table specified as a range from which to retrieve the position of the lookup_value.
[match_type]	The same is optional less then, Exact Match or Greater than valued by 1, 0, -1

  

Translation

 So now we will start using a sample data table. This table contains Names.

 Formula entered above: =MATCH("HARUN",$H$4:$H$7,0)

Syntax =MATCH(lookup_value,lookup_array,[match_type])

 

Sr. No.	Argument	Value
1	lookup_value	HARUN
2	lookup_array	$H$4:$H$7
3	[match_type]	0 for an exact match

In the above table, HARUN is the value, of which position we are trying to identify in the range of$H$4:$H$7. In this range, HARUN is in the third position so the return value is 3.

Click here to open ASHVI CITY – Mr. Excel visualizes learning sessions on my YouTube channel.

Function Return Value

The value is the position of the lookup_value.

Functions Used

=MATCH("HARUN",$H$4:$H$7,0)

 

Evaluation Operator	Meaning
=	Equal To
$	To block the selected cell or range
“	To give functional value to a word

 

Common Difficulties

ERROR	Meaning of Error
#NAME? in cell	This usually means that the formula is misspelled.
#N/A	This usually means that the formula is misspelled.

Important Notes:

(1) While any formula execution if we are using any word like used above HARUN, we need to cap the word with a double-quote ( " ).

(2) MATCH does not distinguish between uppercase and lowercase letters when matching text values.

(3) If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

 

Click here for excel to use MATCH with Vlookup.

Click here for Hindi Video to understand the MATCH with Vlookup.

Are you are having any Question or Comment? Hit me!