실무에서 바로 써먹는 XLOOKUP를 알아보자

실무에서 바로 써먹는 XLOOKUP를 알아보자

VLOOKUP을 아직도 쓰고 계신가요? 
2021년부터 엑셀의 표준이 된 XLOOKUP 하나면 그동안 우리가 IFERROR로 덕지덕지 붙이고, 열 번호 세다가 틀리고, 왼쪽은 못 찾는다고 한숨 쉬던 모든 문제가 사라집니다.

첨부해주신 치트시트를 바탕으로, 실무에서 바로 써먹는 XLOOKUP 완전정복 블로그로 정리합니다.

반응형

1. XLOOKUP이 왜 혁신인가? - WHY XLOOKUP?

VLOOKUP의 5가지 고질병을 한 번에 해결했습니다.

• 왼쪽, 오른쪽 자유: VLOOKUP은 무조건 오른쪽만 봤지만, XLOOKUP은 찾는 방향이 자유입니다.
• 정확도 자유: 정확히 일치, 근사값, 와일드카드까지 하나의 함수로.
• 행/열 전체 반환: 하나의 값만 주는게 아니라, 행 전체, 열 전체를 통째로 반환할 수 있습니다.
• 열 번호가 필요 없음: col_index_num 3, 4 세다가 열 하나 삽입되면 다 깨지는 참사 없음.
• 에러 처리가 내장: IFERROR를 쓸 필요 없이 함수 안에서 "못 찾으면"을 정의합니다.

2. 문법, 이렇게 외우세요

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

어렵게 보이지만 말 그대로입니다.

인수	필수?	의미	예시
lookup_value	Yes	내가 찾고 싶은 값	A2 (제품ID P102)
lookup_array	Yes	어디서 찾을지	A:A (제품ID가 있는 열)
return_array	Yes	찾으면 어디서 가져올지	C:C (제품명 열)
[if_not_found]	No	못 찾았을 때 뭐라고 할지	"Not Found"
[match_mode]	No	어떻게 찾을지	0 = 정확히 일치
[search_mode]	No	어느 방향으로 찾을지	1 = 위에서 아래로

 

가장 기본형:
=XLOOKUP(A2, A:A, C:C, "Not Found")
A2에 있는 값을 A열에서 찾아서, 같은 행의 C열 값을 가져와라. 없으면 "Not Found"를 보여줘. 끝.

3. 실무 레벨 200% 활용하는 match_mode와 search_mode

이 두 개 때문에 XLOOKUP은 VLOOKUP을 완전히 초월합니다.

UNDERSTANDING MATCH_MODE - 어떻게 찾을까?

• 0 (기본값): 정확히 일치. 99%는 이걸로 해결됩니다.
• -1: 정확히 일치하거나, 없으면 다음으로 작은 값. 단가표에서 수량 15개 주문 시 10개 구간 단가 찾기 같은 근사값 검색에 필수. 단, lookup_array는 오름차순 정렬 필수.
• 1: 정확히 일치하거나, 없으면 다음으로 큰 값. -1의 반대.
• 2: 와일드카드. *는 모든 문자, ?는 한 글자. =XLOOKUP("Lap*", B:B, A:A, ,2) 처럼 "Lap"으로 시작하는 모든 제품 찾기 가능.

UNDERSTANDING SEARCH_MODE - 어느 방향으로 찾을까?

• 1 (기본값): 위에서 아래로. First to last
• -1: 아래에서 위로. Last to first - 최신 데이터가 아래에 쌓이는 로그에서 마지막 값을 찾을 때 기적 같은 옵션입니다. VLOOKUP으로는 불가능했죠.
• 2, -2: 이진 검색. 데이터가 10만 행 이상으로 많을 때 속도 향상을 위해 사용. 2는 오름차순, -2는 내림차순 정렬된 데이터에만 사용.

4. 이거 모르면 손해! PRACTICAL EXAMPLES 5가지

1) 기본 정확히 일치: 가격 찾기
=XLOOKUP(A2, A:A, D:D, "Not Found")
A2의 제품ID를 A열에서 찾아 D열의 가격 반환. 가장 기본.

2) 여러 열을 한 번에 반환: 제품명과 가격을 동시에
=XLOOKUP(A2, A:A, B:D, "Not Found")
return_array를 B:D 처럼 범위로 지정하면 결과가 옆으로 스필(Spill) 됩니다. B열은 제품명, D열은 가격이 한 번에 튀어나옵니다.

3) 왼쪽을 찾아라: VLOOKUP의 최대 약점 극복
=XLOOKUP(A2, C:C, A:A, "Not Found")
C열에서 찾아서 A열을 반환. 이제 더 이상 참조 열을 일부러 오른쪽으로 옮길 필요가 없습니다.

4) 근사치 매칭: 구간별 단가, 성적 등급
=XLOOKUP(E2, A:A, B:B, "-", -1)
수량 E2에 대해 A열에서 가장 가까운 작은 값을 찾아 B열 단가 반환.

5) 와일드카드 매칭: 애매한 텍스트 찾기
=XLOOKUP("Lap*", B:B, A:A, "Not Found", 2)
B열에서 Lap으로 시작하는 첫번째 텍스트를 찾아 A열 반환.

5. 고수들의 꿀팁 3가지

1) IF_NOT_FOUND는 습관처럼 쓰세요
=XLOOKUP(A2, A:A, C:C, "") 처럼 빈칸으로 처리하거나, =XLOOKUP(A2, A:A, C:C, "제품ID " & A2 & " 없음") 처럼 친절한 메시지를 넣으면 보고서가 훨씬 깔끔해집니다.

2) 중첩 XLOOKUP으로 2중 백업 만들기
=XLOOKUP(A2, A:A, C:C, XLOOKUP(A2, E:E, G:G, "Not Found"))
첫 번째 테이블(A:C)에서 못 찾으면 두 번째 테이블(E:G)에서 다시 찾아봐. 창고가 2개일 때 완벽합니다.

3) 에러는 이렇게 해결

• #N/A: 못 찾은 값. if_not_found 인수를 안 썼을 때 뜹니다.
• #VALUE!: lookup_array와 return_array의 크기가 다릅니다. A:A vs C2:C100처럼 크기를 맞춰주세요.
• #SPILL!: 반환할 결과가 표시될 공간에 이미 다른 데이터가 있습니다. 주변 셀을 비워주세요.

결론: XLOOKUP vs VLOOKUP
이제 VLOOKUP을 쓸 이유는 단 하나도 없습니다. 학습 난이도는 더 쉽고, 기능은 5배 강력합니다. 오늘부터 V를 X로만 바꿔보세요. 당신의 야근이 30분은 줄어들 겁니다.

이 치트시트 이미지 저장해두고 책상 앞에 붙여두는 걸 추천합니다.

와인병다육이세상사는이야기

 

와인병다육이세상사는이야기