Lookups, SUMIF and COUNTIF

Office roles rarely need advanced Excel. They need three things: find a value in another table, add up a column by condition, and count rows by condition. These are VLOOKUP (or XLOOKUP), SUMIF and COUNTIF.

VLOOKUP

=VLOOKUP(lookup_value, table, column_number, FALSE). Excel searches the first column of the table for the value and returns the cell from the column number you give. Use FALSE for an exact match; TRUE is for sorted ranges and bands, and it is the cause of most wrong answers. #N/A means the value was not found, often because of a trailing space or text-versus-number mismatch.

XLOOKUP(lookup_value, lookup_column, return_column) does the same without counting columns and can look left. Use it when the workbook will be opened in a recent Excel.

SUMIF and COUNTIF

=SUMIF(A:A, "Paid", C:C) adds column C where column A says Paid. =COUNTIF(A:A, "Paid") counts those rows. Criteria can use operators in quotes: ">35", "<>Cancelled". For two or more conditions use SUMIFS and COUNTIFS, where the sum range comes first.

Common errors

• Numbers stored as text: the green triangle in the cell corner. Multiply by 1 or use VALUE().
• Ranges of different lengths in SUMIF.
• Column number in VLOOKUP counted from the sheet rather than from the table's first column.

Try the Excel test. Every lookup or conditional question comes with the reason behind the correct answer.