One of the most often used functions when creating an Excel model is consolidating data from different sources. Traditionally, there were 3 major functions for combining data from different tables or worksheets: VLOOKUP, SUMIFS and INDEX/MATCH. Now, Microsoft has introduced XLOOKUP. So what is the difference between these four lookup functions and which one should… Continue reading XLOOKUP, VLOOKUP or INDEX/MATCH? Big Lookup Functions Guide!
Tag: sumifs
Case-Sensitive Lookups in Excel: 4 Methods (+XLSX-Download)
By definition, the VLOOKUP formula is not case-sensitive. Case-sensitive means, that it matters if you use capital letters or small letters. For instance, a VLOOKUP search for “AAA” will return the same value as for “aaa” or “Aaa”. But in some cases, you want to differentiate between capital and small letters. So how do you… Continue reading Case-Sensitive Lookups in Excel: 4 Methods (+XLSX-Download)
Multi-Condition VLOOKUP and INDEX/MATCH (+Excel-Download)
There are many cases in which you want to conduct a lookup with several search criteria. As of now only the SUMIFS formula allows a multi-condition lookup. Unfortunately, SUMIFS only works for numeric values (including dates) as the return value. If you want to return text, there is no direct method. The good news: Both… Continue reading Multi-Condition VLOOKUP and INDEX/MATCH (+Excel-Download)
SUMIFS in Excel: Everything You Need to Know (+Download)
The SUMIFS formula adds up all numbers, when one or more than one criterion is fulfilled. SUMIFS only exists since Excel 2007 and is especially useful, as it can regard several search criteria.