Let's say the invoice numbers live in one sheet and the amounts live in another. You want one machine: give it an invoice number, get the amount. XLOOKUP(what to find, where to search, what to give back) is that machine.

It needs Excel 2021 or Microsoft 365, and it defaults to an exact match. The columns are named separately, so it finds things in any direction. The old way gave you a bald #N/A when nothing matched; here a fourth argument returns a friendly word instead: XLOOKUP(E2, B:B, D:D, "not found").

Name the column to search and the column to return. That's the whole trick.

Let's say you inherited a sheet full of =VLOOKUP(A2, $B$2:$D$99, 3, FALSE) and you'd like to know what the parts mean. Search the first column for A2, count across to column 3, FALSE means exact only.

Three traps come free. It cannot look left: the search value must sit in the range's first column. Leave the last argument out and you get an approximate match, which quietly returns a wrong value instead of an error. And numbers pasted as text never match while looking perfect.

FALSE at the end, always, unless you can say why not. That habit removes most VLOOKUP bugs.

Let's say the sheet has 400 rows and the boss wants "sales over ten thousand, added up". Scrolling and Ctrl-clicking is one way. =SUMIF(A2:A400, ">10000") is the other.

The criteria is a piece of text wearing a condition: ">10000", "North", B2 all work. Comparison criteria always live in quotes; without them it's a formula error, not a filter. When the range you test and the range you add differ, the third argument is the add-range, lined up row by row.

One condition, one line, live forever. When one condition stops being enough, SUMIFS is next page.

Let's say you filter to one region and the grand total keeps insisting on all four. The fix is one word: =SUBTOTAL(109, C2:C99). The total now follows the visible rows and changes as the filter changes.

The first argument is a code: 9 is SUM, 1 is AVERAGE, 2 is COUNT, 109 is SUM ignoring the rows you hid by hand too. The quiet superpower: SUBTOTAL ignores other SUBTOTALs in its range, so section totals plus a grand total never double-count.

Filtered rows never count, whichever code you pick. 101 to 111 also skip manually hidden rows.

Let's say 300 scores need Pass or Fail. Reading them yourself is a Tuesday nobody needs. =IF(B2>=40, "Pass", "Fail") does the whole column once you fill it down.

Three parts, always this order: the test, the answer if true, the answer if false. Text answers wear quotes; =IF(B2>=40, Pass, Fail) breaks because Excel hunts for cells named Pass. The classic mistake is nesting IFs three deep. Past two branches, the next page is the kinder road.

The test can hold anything that returns TRUE or FALSE, including the combiners two pages ahead.

Let's say fifty cities in a column need to become one line for an email. Copy, paste, type a comma, sigh, repeat. =TEXTJOIN(", ", TRUE, A2:A50) does the whole column in one cell.

The second argument is the quiet hero: TRUE skips empty cells, so gaps don't become double commas. FALSE and every blank rides through. The older CONCAT still works, but it has no delimiter argument, so the commas would all be yours.

Excel 2019 or 365. Any text works as the delimiter; a line break is CHAR(10) plus Wrap Text.

Let's say you need the list of regions that actually appear in the data. Remove Duplicates is a one-way edit. =UNIQUE(A2:A500) is a live answer that grows a new region the moment a row needs one. Excel 2021 or 365.

Its real job here is feeding COUNTIFS: UNIQUE gives the headings, COUNTIFS fills the counts, and two pages on they build a pivot together. The optional third argument is the connoisseur's tool: exactly_once TRUE keeps only values appearing a single time, perfect for finding one-offs.

Pass a multi-column range and uniqueness is judged on whole rows, which is usually what you meant.

Let's say VAT needs applying to a hundred rows and the rate sits in F1. Type =E2*F1 and fill down: row 3 reads F2, empty. References move because moving is what references do. Pin it: =E2*$F$1, and every row reads the one true cell.

Read the dollars as bolts: $F$1 bolts column and row, F$1 pins the row, $F1 the column. The F4 key cycles a selected reference through all four. The pivot thought leaned on exactly this: data pinned, heading free.

Change the rate once and the whole sheet reprices. That is a spreadsheet working as designed.