What is your favorite lookup function? Perhaps the most common answer is VLOOKUP. Other standard lookup functions include MATCH, INDEX, HLOOKUP, GETPIVOTDATA, and LOOKUP. Rarely is SUMIFS included in any list of lookup functions. In fact, Excel categorizes each function, and following is a list of all functions in the Lookup & Reference category:
SUMIFS is not found in this list. SUMIFS is typically referred to as a conditional summing function and is found in the Math & Trig function category.
SUMIFS can’t perform all lookup tasks and is not appropriate in all situations. Recall that it returns a number, not a text string. When the value we are trying to return is a number, such as an amount, it is a wonderful lookup function. In this whitepaper, we’ll explore the advantages of using it to perform lookups.