1) Core Comparison Table — xlookup vlookup comparison

2) Eight Common Patterns (Copy and Paste)

① Basic lookup

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

② Look up to the left

=XLOOKUP(E2, C:C, B:B, "Not found")   // OK even when the return column is on the left

③ Partial match (wildcards)

=XLOOKUP("*"&E2&"*", A:A, B:B, "Not found")

④ Last value (search from last to first)

=XLOOKUP(E2, A:A, B:B, "Not found", 0, -1)

⑤ Default value for missing entries and error handling

=XLOOKUP(E2, A:A, B:B, "Not registered")  // IFERROR not required

⑥ Two-way (2D) lookup by row and column

=XLOOKUP(H2, A:A, XLOOKUP(I2, A1:Z1, A2:Z100))

⑦ Multiple criteria (AND)

=XLOOKUP(1, (A:A=E2)*(B:B=F2), C:C, "Not found")

⑧ Approximate match (price tables/code ranges)

=XLOOKUP(E2, A:A, B:B, "Not found", 1)   // Approximate match based on ascending order

3) Multiple Criteria, Two-Way Lookups, and Approximate Matches: Practical Examples

Multiple criteria + return multiple columns (spill)

=XLOOKUP(1,(tbl[품목]=E2)*(tbl[지역]=F2), CHOOSECOLS(tbl,3,4,5), "Not found")

Two-way: row (customer) + column (month)

=XLOOKUP(E2, 고객, XLOOKUP(F2, 월헤더, 데이터범위))

Approximate match + boundary validation

=LET(x, XLOOKUP(E2, 기준구간, 요율, , 1), IF(x="", "No matching range", x))

TIP For text preparation, use TEXTSPLIT/TEXTBEFORE/TEXTAFTER; for list cleanup, use FILTER, SORT, and UNIQUE to improve accuracy.

4) VLOOKUP → XLOOKUP Migration Checklist

• Remove the insertion-sensitive col_index_num → improve stability by referencing the return column directly
• Standardize the default match mode as exact (0) to reduce errors
• Standardize error messages with the default value in the fourth argument (for example, “Not found”)
• When you need to search from last to first, use search mode=-1
• For multiple criteria, combine keys with a multiplication expression (AND) or CHOOSE

// VLOOKUP(E2, A:D, 4, FALSE)
// ↓
=XLOOKUP(E2, A:A, D:D, "Not found", 0)

5) Common Errors and Fixes

• Text that looks like numbers: Clean up the format with NUMBERVALUE or Power Query
• Spaces/control characters: TRIM + SUBSTITUTE(,CHAR(160), " ")
• Duplicate keys: Diagnose with UNIQUE → combine with XMATCH if needed
• Frequent #N/A errors: Check spelling, capitalization, spaces, and Unicode spaces; provide a fallback message with IFERROR

6) Summary

• Recommended default: =XLOOKUP(key, lookup_column, return_column, "Not found", 0)
• Multiple criteria: XLOOKUP(1,(condition1)*(condition2), return)
• Two-way lookup: Nest XLOOKUP inside XLOOKUP
• Approximate match: Sort first, then use match_mode=1 or -1