CONTENTS

Excel XLOOKUP Function Complete Guide — Left/Right, Two-Way, Wildcards, Last Match, and Array Returns

XLOOKUP is a modern lookup function that replaces VLOOKUP/HLOOKUP/LOOKUP/INDEX+MATCH. It can look up values to the left or right, horizontally or vertically, and supports exact or approximate matches, wildcards, last matches, and even returning multiple columns as an array.

Quick Fix: 6 Most Common Workplace Patterns

=XLOOKUP($G2, 제품코드, 제품명)                          ; Basic
=XLOOKUP($G2, 제품명, 출시일)                              ; Left (reverse) lookup
=XLOOKUP($G2, 주문코드, 상태, , , -1)                     ; Last match
=XLOOKUP("Hoodie*", 상품명, 가격, "없음", 2)               ; Partial match (begins with)
=XLOOKUP($G2, 월_헤더행, 매출_행)                          ; Horizontal lookup
=XLOOKUP($G2, 제품코드, 제품명:납품처)                     ; Array return

Syntax and Key Arguments

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

match_mode: 0 exact (default), -1/1 approximate, 2 wildcard
search_mode: 1 top to bottom (default), -1 bottom to top (last match), 2/-2 binary search

Left/Right, Two-Way, and Last Match

There are no position restrictions on the return column, and you can search for the last match with search_mode=-1.

Wildcards and Partial Matches

=XLOOKUP("*Denim*", 상품명, 가격, "없음", 2)

Horizontal Lookups and Array Returns

=XLOOKUP($G2, 월_헤더행, 매출_행)
=XLOOKUP($G2, 제품코드, 제품명:납품처)

Multiple Criteria (AND/OR) and Approximate Match + Filter Combinations

=XLOOKUP(1,(카테고리=H5)*(색상=H6)*(사이즈=H7), 가격)   ; AND
=XLOOKUP(1,(색상="Red")+(색상="Pink"), 가격)               ; OR
=XLOOKUP(H7, IF(서비스=H6, 무게), 비용, , -1)             ; Approximate match (+ sorting)

Practical Examples

Item	Color	Size	Price
Hoodie	Blue	M	29
Hoodie	Blue	L	31
Hoodie	Red	M	28

=XLOOKUP(1,(A:A="Hoodie")*(B:B="Blue")*(C:C="L"),D:D)  → 31
=XLOOKUP("Hoodie*",A:A,D:D,"없음",2)                       → 29 (first match)
=XLOOKUP("Hoodie",A:A,D:D,"없음",0,-1)                     → 28 (last match)

Troubleshooting and Performance

Symptom	Cause	Solution
#N/A	Spaces, formatting, or mismatched values	Use TRIM/CLEAN, standardize formatting, use IFNA
Slow	String concatenation or overly broad ranges	Use Boolean multiplication, minimize ranges, use table references
Incorrect approximate match	Sorting requirement not met	Sort the column and check match_mode
Last value not found	Default top-to-bottom search	Use search_mode=-1

Alternatives and Extensions

FILTER: Extract a set of rows, then select from it
XMATCH: Find only the position, then return a value with INDEX
LET: Assign conditions and ranges to variables for better performance and readability

References: Oppadu XLOOKUP guide and overview, Microsoft/Exceljet official documentation.

XLOOKUP 다중 조건 완벽 가이드 — 불리언 곱·문자열 결합·근사일치·OR

2 thoughts on “Excel XLOOKUP Function Complete Guide — Left/Right, Two-Way, Wildcards, Last Match, Array Returns, and Multiple Criteria”

Pingback: Compare Two Excel Lists and Find Missing or Changed Records
Pingback: XLOOKUP Wrong Record? Find and Fix Duplicate Lookup Keys

Excel XLOOKUP Function Complete Guide — Left/Right, Two-Way, Wildcards, Last Match, Array Returns, and Multiple Criteria