How to apply multiple criteria using match function in Excel

Match function in Excel allows you to search for a value in an array and return the relative position of the item in specific order. Sometime you need to apply more than one criteria in that case you can use Match function in Excel. Excel Match function is an important function which is located in Lookup and Reference category.

Match function in Excel allows you to apply multiple criteria and find the relative position of the given item. Excel provide several ways like VLookup to search more than one criteria with VLookup function. You can also use Match and Index function to search the specific value relative position.

Must Read: MS Excel DMIN Database Function

How to apply multiple criteria using match function in Excel

If you want to know how to apply multiple criteria using Match function in Excel, then first you must know about this function.

Syntax of Match Function in Excel

MATCH(lookup_value, lookup_array, [match_type])

lookup_value: The value that you want to match in lookup_array. It can be a number, text, logical value or a cell reference.

lookup_array: It is the range of cells being searched.

match_type: It’s an optional value. You can put the number -1, 0, or 1 in match type. The default value for this argument is 1.

1: Match function finds the largest value that is less than or equal to lookup_value.
0: Match finds the first value that is exactly equal to lookup_value.
-1: Match finds the smallest value that is greater than or equal to lookup_value.

Must Read: How to use Advance Backward VLookup in Reverse Order in Excel

For eg: If you have a worksheet data in which you have Month Name, Product Name and Number of Units sold.

Steps to apply more than one criteria using match function in Excel

You have to take few given simple steps which allows you to use Match function with more than one criteria.

searches for a value in an array and returns the relative position of that item

Step 1: Create the following given worksheet to start the process of multiple criteria using the match function in Excel.

Step 2: Now you want to search number of units sold which match the given month and product name. Fill the month name in E2 and Product name in F2. Now apply the following given function in cell G2 to get the result using Match function in Excel.

=MATCH(E2&F2,A2:A19&B2:B19,0) Now press Ctrl+Shift+Enter key to get the result.

I hope after reading this guide you can easily understand how to apply multiple criteria using match function in Excel. If you have any suggestion regarding this guide then write us your suggestion in the comment box. Thanks to all.

Leave a Reply

Your email address will not be published. Required fields are marked *