SUM
One condition or several?
Use SUMIF when one condition controls the sum. Its argument order is the condition range, the condition, then the optional values to add.
Add column C when column A is West
=SUMIF(A2:A100,"West",C2:C100)Use SUMIFS when every row must satisfy two or more conditions. The sum range comes first, followed by condition-range and condition pairs.
Add column D for West rows with amount at least 100
=SUMIFS(D2:D100,B2:B100,"West",C2:C100,">=100")The argument-order difference is the common trap: SUMIF places the sum range last; SUMIFS places it first.
LOOKUP
Prefer XLOOKUP when the workbook supports it
XLOOKUP separates the lookup range from the return range. It can return values on either side and includes an explicit not-found result.
Find E2 in column A and return column C
=XLOOKUP(E2,A2:A100,C2:C100,"Not found")VLOOKUP still matters for older Excel versions. Its lookup column must be the first column in the table, and the returned value is selected by a fragile column number. Use FALSE for an exact match.
Older-version exact lookup
=VLOOKUP(E2,A2:C100,3,FALSE)FLEX
Use INDEX/MATCH for durable older workbooks
INDEX/MATCH works in older Excel versions and can look left or right. MATCH finds a row position; INDEX returns the value at that position.
Exact match with a separate return range
=INDEX(C2:C100,MATCH(E2,A2:A100,0))The final 0 requests an exact match. Omitting it can produce a plausible but wrong answer when the lookup range is not sorted.
DECIDE
A practical choice order
- 1. Count the conditions: one suggests SUMIF; several suggest SUMIFS.
- 2. For lookups, use XLOOKUP when every recipient has a compatible Excel version.
- 3. Use INDEX/MATCH when backward compatibility or left-side returns matter.
- 4. Keep VLOOKUP only when an existing workbook already depends on it.
- 5. Test one known match and one known miss before filling the formula down.