Pull the Latest Record for a Name

Excel & Google Sheets

How it works

Normal lookups stop at the first match from the top — the oldest entry in an append-style log. XLOOKUP's sixth argument flips the direction: -1 means search last-to-first, so the first match it finds is the bottom-most row, which is the newest entry when rows are added chronologically. The 0 before it is the match mode (exact match), and the "" is what to return when the name isn't found at all, instead of #N/A. XLOOKUP needs Excel 365 or Excel 2021; Google Sheets has it too, with the same arguments.

E2

The name to look up.

A:A,B:B

Search column A, return the matching value from column B.

""

What to show when there's no match at all — a blank instead of #N/A.

0

Match mode: exact match only.

-1

Search mode: scan from the last row up, so the bottom-most match wins.

When to use it

Use this on status logs, payment histories, ticket updates, and check-in sheets — any table where rows are appended over time and "current state" means the last row for each name.

Common mistakes

• ✕XLOOKUP isn't available in older Excel.

  ✓Excel 2019 and earlier lack XLOOKUP. Use the legacy last-match trick instead: =LOOKUP(2,1/(A:A=E2),B:B) — it also returns the bottom-most match.

• ✕Putting -1 in the wrong slot.

  ✓=XLOOKUP(E2,A:A,B:B,"",-1) sets match mode to -1 (exact or next smaller), not the search direction. The search mode is the sixth argument: =XLOOKUP(E2,A:A,B:B,"",0,-1).

• ✕The log isn't in chronological order, so the last row isn't the latest.

  ✓Bottom-up search only equals "most recent" when new rows go at the bottom. If the log gets sorted or merged, sort it by date ascending first — or look up by the maximum date instead.