IN OUR EXAMPLE, we're using MATCH($B$3,$B$5:$M$5,0), which as per the definition of MATCH function returns the index number / position of cell B3 in the array B5:M5. If match_type is omitted, it is assumed to be 1.Lookup_array must be placed in descending order: TRUE, FALSE, Z-A. If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value.If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value.Lookup_array must be placed in ascending order. If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value.Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array. Lookup_array must be an array or an array reference.
Lookup_array is a contiguous range of cells containing possible lookup values. (Source: Excel Help on Match Function) Lookup_value is the value you use to find the value you want in a table. It is used instead of the LOOKUP functions when we need the position of an item in a range instead of the item itself. MATCH Function Syntax: MATCH(lookup_value,lookup_array,match_type) Definition: It returns the relative position of an item in an array that matches a specified value in a specified order.
Unwinding the Nested Formula and Conditional Formatting used in the Sample Dashboard
Before we begin, let's have a look at a dashboard where we're effectively using SUM Function in conjunction with OFFSET and MATCH function.
Now we'll try to understand how to use OFFSET function in conjunction with SUM Function. Now this range can be provided to various formulas where it will be used as Dynamic Range. As we learned in our previous article on OFFSET function, when we increase either the row height or column width in the offset function "=OFFSET(A1,2,0, 1,1)" to more than 1, the reference is converted to a range from a single cell value.