The match function which is the column_num argument returns 5 as the value Product-D in the 5th row in the range you have referred. You could use Data Validation to create dropdown menus to select salesperson and month. To evaluate multiple criteria we use the multiplication operation that works as the AND operator in array formulasBelow you will find a real-life example and the detailed explanation of the logic.
We can get the maximum if multiple criteria value of a dataset with the help of array formula approach based on MAX or LARGE function along with IF function in Excel.
The formula is an advanced version of the iconic INDEX MATCH that returns a match based on a single criterion. Below is the syntax of the INDEX function. Type the following given function in cell C9 INDEXB3B7. Here is one example to IF INDEX and MATCH combination in Excel.