Look up an employee ID in column A and return the name from column C, to the left of the key

公式

=XLOOKUP(E2, A:A, C:C, "Not found", 0)

说明

XLOOKUP can return a column to the left of the lookup column and does not break when you insert columns. The 0 match mode forces an exact match.

步骤

  1. Put the ID you want in E2.
  2. Lookup array is A:A (the IDs).
  3. Return array is C:C (the names).
  4. "Not found" is the value if the ID is missing.

变体

VLOOKUP equivalent (key must be leftmost)

Works in Excel 2016. Column C must be the third column of A:C.

=IFERROR(VLOOKUP(E2, A:C, 3, FALSE), "Not found")

INDEX / MATCH

Same left-lookup behavior on older Excel without XLOOKUP.

=INDEX(C:C, MATCH(E2, A:A, 0))

Return a whole row (365)

Spill helper columns for department and email in one call.

=XLOOKUP(E2, A:A, B2:D100)