Logo
×

Excel MATCH () Function

Excel MATCH () Function

The MATCH () function in excel locates the position of a lookup value in a row, column, or table and returns the relative position of an item in an array.

Syntax

MATCH (Lookupvalue, Lookup_Array, [Match_type])

Parameter

Lookupvalue (required)- This parameter represents the value which needs to be looked for in a column, row or table.

Lookup_Array (required)- This parameter refers to the column, table, or table in which we are checking for this value to be present.

Match_type(optional)- This parameter represents takes three values. 1 gives the smallest value position that is greater than, or equal to the value we are searching, 0 gives the value for the exact search, and 1 gives the largest value that is less than or equal to the value we are searching for. The default value is 1.

Category

Lookup & Reference

Return

This function returns the relative position of an item in an array.  

Example 1

Objective: Find the position of 'Lookup Value' given in range B121:B132 and at what position, column 'Product' is in above sample data?

Name Purchased Date Product Category Product # Unit Amount
Sukla Rani 21-Mar-16 Phone HP 15-ay011nr 2 $989
Rahul George 8-Jun-16 Palmtop Pebble Smart Watch 2 $753
Sid Rao 11-Jan-14 Watch Acer Aspire E 15 1 $871
Jenny Zame 16-Jul-15 Alexa Fitbit Blaze Smart Watch 4 $637
William Laslo 31-Mar-14 Watch Nikon D3200 3 $573
Charlie Den 22-Nov-15 Desktop Apple Watch Sport 4 $662
Bing jing 31-May-15 Speaker ASUS F556UA 2 $975
Paul White 8-Jun-16 Laptop Samsung Gear S2 3 $501
Ahmed Bilal 1-Jan-14 Watch ASUS ROG 2 $556
Hans Raj 16-Jun-16 Laptop Nikon D4S 1 $968
Christine 21-Dec-15 Camera Canon ES 1 $600
Look up Value Result Formula
Charlie Den 7 =MATCH(E135,B121:B132,0)
# Unit 5 =MATCH(E137,B121:G121,0)

Example 2

Objective: Pick 'ID' based on the lookup value provided in Lookup value cell.

ID Name Department Salary
651 Sukla Rani Marketing $1,378
774 Rahul George Sales Executive $920
650 Sid Rao Engineering $936
624 Jenny Zame Engineering $806
873 William Laslo CSE $1,218
818 Charlie Den Engineer $1,206
825 Bing jing CSE $1,007
694 Paul White Sales $1,479
604 Ahmed Bilal CSE $866
791 Hans Raj Sales $994
Look up Value Result/ ID Formula
Charlie Den 7 =INDEX(B142:E153,MATCH(C157,C142:C153,0),1)

Related Topics

Date and Time in Excel-VBA

In this modern world of computer technology the creation of Excel reports are termed to be the crucial elements, and the particular type of programs that helps us in performing...

6 minutes read.

Excel HLOOKUP() Function

Excel HLOOKUP() Function The HLOOKUP() function in excel is used to search a value in the topmost row of a table and then return a corresponding value in the same column for the specified...

2 minutes read.

Frequency Distribution in Excel

The term Frequency Distribution in Microsoft Excel is mainly used to explain how the data will be spread out. And it could be achieved with the help of the Histogram,...

7 minutes read.

How to Delete Row in Microsoft Excel?

Individuals can quickly delete the respective row from the particular Microsoft Excel whenever they want, according to their needs and requirements. In this tutorial, we will discuss the following things in...

7 minutes read.

Excel LARGE() Function

The LARGE() function in excel returns the k-th largest value in a data set. Syntax LARGE (Array, kth small number from the list) Parameter Array (required)- This parameter represents the list of numbers from which nth...

1 minute read.

Absolute Value in Excel

What is Absolute Value in Excel? Microsoft Excel worksheet is a combination of numeric values and alphabets. The numeric value is either a positive value or a negative value. Sometimes the...

5 minutes read.

Excel MIN() Function

Excel MIN() Function The MIN() function in excel returns the smallest or minimum value from the provided numbers, named range or array. It ignores logical values and text. Syntax MIN(Number1,Number2, ...) Parameter Number1(required)- This parameter represents the...

1 minute read.

Locate Maximum Values in Excel

Excel is a combination of numeric values and numbers. To retrieve the result, Excel provides various formulas and functions. Sometimes the user needs to find the largest value from the...

5 minutes read.

Excel SUBSTITUTE() Function

The SUBSTITUTE() function in excel substitutes/replaces the old text with the new text in a string. SUBSTITUTE function is used to clean data. This function is case-sensitive. Syntax SUBSTITUTE(Text, Old_Text, New_Text, [instance_number]) Parameter Text (required)- This...

2 minutes read.

Excel COUNTA() Function

Excel COUNTA() Function The COUNTA() function counts the number of cells in a range that are not empty. Syntax COUNTA([value1], [value [2], ...) Parameter value1(required)- This parameter represents the first cell in the range. value2, …(optional)- It represents...

1 minute read.

Decimals in Excel

Microsoft Excel is widely used for calculation purposes for various applications. The calculation consists of numbers which include whole numbers, decimals, integers etc. To perform quick calculations, the numbers should...

5 minutes read.

How to create drop down in excel?

How to create drop down in excel? Drop-down lists are mostly used to facilitate data entry operations. They are commonly used in interactive websites or applications. Microsoft has expanded his Excel's data validation feature...

5 minutes read.

Excel RIGHT() Function

The RIGHT() function in excel returns the rightmost characters from a text value. This function is used for data manipulation. Syntax RIGHT(Text, [num_chars]) Parameter Text (required)- This parameter represents the string from which you want to find...

1 minute read.

Excel AND() Function

The AND() function in excel tests the multiple sets of conditions and returns TRUE if all supplied conditions met the criterion, FALSE otherwise. You need to have a minimum of 2 conditions...

2 minutes read.

Excel SECOND() Function

Excel SECOND() Function The SECOND() function in excel converts a serial number to SECOND (0 - 59). Syntax SECOND (SERIAL_NUMBER) Parameter SERIAL_NUMBER (required)- This parameter represents the date or time from which you want to extract...

1 minute read.

How to Highlight Duplicates Words in the Microsoft Excel?

We can easily highlight the values that are duplicated in the selected dataset, whether it could be a column or a row from the particular table, with the help of...

6 minutes read.

Basics of Excel

Different ways to open an excel file There are various ways to open an excel file, some of them are listed below - From Start Menu:Go to the start menu and search for Ms-Excel...

5 minutes read.

Error Bar in Microsoft Excel

Error Bar is considered the essential term used in Microsoft Excel. It is defined as the graphical representation of the respective data used to denote the errors. The Error bars can...

7 minutes read.

SumIF Formula in Microsoft Excel

SUMIF Formula in Microsoft Excel is considered the primary and vital formula individuals can use to remove their workload. In this tutorial, we will discuss and learn about the SUMIF Formula...

7 minutes read.

Gauge Chart in Excel

A Gauge Chart in Microsoft Excel is considered the meter type chart of a group dial chart, which is eventually similar to the speedometer that has the pointer towards the...

7 minutes read.