[엑셀 함수] XLOOKUP 사용법 – VLOOKUP보다 쉬운 찾기 함수 (왼쪽 조회·여러 열·못 찾을 때)

환경 : Microsoft 365 / Excel 2021 이상

VLOOKUP 사용법(124번 글)에서 VLOOKUP은 찾는 값이 첫 열에 있어야 하고, 열 번호를 세야 한다는 불편함이 있었습니다. XLOOKUP은 찾을 범위와 가져올 범위를 따로 지정해 이런 제약이 없는 새 찾기 함수입니다.

1. 사용 방법

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

인수	의미
lookup_value	찾을 값
lookup_array	찾을 범위 (한 열 또는 한 행)
return_array	가져올 범위
if_not_found	못 찾았을 때 표시할 값 (생략 시 #N/A)
match_mode	0 = 정확히 일치(기본) / -1 = 같거나 바로 작은 값 / 1 = 같거나 바로 큰 값 / 2 = 와일드카드
search_mode	1 = 위에서부터(기본) / -1 = 아래에서부터

2. 예제 데이터

	B	C	D	E
2	코드	제품명	분류	단가
3	A01	사과	과일	3,000
4	A02	배	과일	4,500
5	A03	포도	과일	6,000
6	B01	오이	채소	1,200
7	B02	당근	채소	1,500

예제 ① 기본 : 코드로 제품명 찾기

=XLOOKUP("A03", B3:B7, C3:C7)

결과는 포도입니다. 열 번호 대신 가져올 열(C3:C7)을 직접 지정합니다.

예제 ② 왼쪽 값 가져오기 : 제품명으로 코드 찾기

=XLOOKUP("오이", C3:C7, B3:B7)

결과는 B01입니다. VLOOKUP으로는 불가능했던 '왼쪽 조회'가 됩니다.

예제 ③ 못 찾았을 때 메시지

=XLOOKUP("C01", B3:B7, C3:C7, "등록 안 됨")

IFERROR로 감쌀 필요 없이 네 번째 인수로 처리합니다.

예제 ④ 여러 열을 한 번에

=XLOOKUP("A02", B3:B7, C3:E7)

수식은 한 칸에만 입력해도 배 / 과일 / 4,500이 오른쪽으로 펼쳐집니다(스필).

[캡처 : 여러 열이 한 번에 펼쳐진 결과]

예제 ⑤ 구간 찾기 : 점수로 학점

점수 하한값(K열)과 학점(L열) 기준표가 있을 때, match_mode를 -1로 하면 '같거나 바로 작은 값'을 찾습니다. VLOOKUP의 TRUE와 달리 기준표가 정렬되지 않아도 됩니다.

=XLOOKUP(G5, $K$4:$K$13, $L$4:$L$13, , -1)

예제 ⑥ 마지막 기록 찾기

같은 코드가 여러 번 나오는 입출고 기록에서 가장 최근(맨 아래) 값을 가져오려면 search_mode를 -1로 합니다.

=XLOOKUP("A01", B:B, F:F, , 0, -1)

3. VLOOKUP과 비교

구분	VLOOKUP	XLOOKUP
찾는 열 위치	표의 첫 열만	어디든
가져올 열	열 번호로 지정	범위로 직접 지정
기본 일치 방식	생략 시 유사 일치	정확히 일치
못 찾을 때	IFERROR 필요	인수로 지정
열 삽입 시	열 번호 어긋남	영향 없음
사용 가능 버전	모든 버전	365 · 2021 이상

4. 주의사항

1. lookup_array와 return_array의 행 수가 같아야 합니다. 다르면 #VALUE!가 납니다.

2. 파일을 Excel 2019 이하 사용자와 공유하면 XLOOKUP이 #NAME?으로 보입니다. 이때는 INDEX + MATCH 조합을 쓰세요.

3. 여러 열 결과가 펼쳐질 자리에 값이 있으면 #SPILL! 오류가 납니다.