엑셀 xlookup 함수 쓰는법 쉽게 익히는 방법

Last Updated :
엑셀 xlookup 함수 쓰는법 쉽게 익히는 방법

얼마 전 주문 목록에서 상품 코드를 보고 가격을 찾아야 했는데, 행이 많아지니 눈으로 확인하는 데 시간이 꽤 걸리더라고요. 이럴 때 엑셀 XLOOKUP을 쓰면 코드가 있는 열에서 값을 찾고, 같은 행의 상품명이나 가격을 바로 가져올 수 있습니다. 처음에는 수식이 길어 보여도 실제로 자주 쓰는 부분은 세 가지뿐입니다.

찾을 값, 찾을 범위, 가져올 범위부터 지정하기

기본 형태는 =XLOOKUP(찾을 값, 찾을 범위, 가져올 범위)입니다. 예를 들어 B2:B6에는 상품 코드, C2:C6에는 상품명, D2:D6에는 가격이 있다고 해볼게요. F2에 찾고 싶은 코드 A103을 적었다면 =XLOOKUP(F2,B2:B6,D2:D6)으로 가격을 가져옵니다. A103의 가격이 8900원이라면 결과 셀에는 8900이 표시됩니다.

수식을 읽을 때는 ‘F2의 값을 B열에서 찾아, 같은 행의 D열 값을 보여줘’라고 생각하면 편합니다. 상품명이 필요할 때는 마지막 범위만 바꿔 =XLOOKUP(F2,B2:B6,C2:C6)으로 입력하면 됩니다. 찾을 범위와 가져올 범위는 행 수가 같아야 하므로 B2:B6과 D2:D6처럼 시작과 끝을 맞춰 주세요.

VLOOKUP에 익숙하다면 차이도 금방 느껴집니다. XLOOKUP은 가져올 열이 찾는 열의 왼쪽에 있어도 됩니다. 상품명을 입력해 코드를 찾고 싶다면 =XLOOKUP(F2,C2:C6,B2:B6)처럼 범위 순서만 정하면 됩니다. 또 기본 설정이 ‘정확히 일치’라서, 정확한 코드를 찾는 일반적인 작업에는 일치 방식 인수를 따로 적지 않아도 됩니다.

값이 없을 때 안내 문구 표시하기

목록에 없는 코드를 찾으면 기본적으로 #N/A 오류가 나옵니다. 주문서를 다른 사람과 공유할 때는 오류 대신 알아보기 쉬운 문구가 낫겠죠. 네 번째 인수에 문구를 넣어 =XLOOKUP(F2,B2:B6,D2:D6,"상품 코드 없음")이라고 입력하면 됩니다. 찾는 코드가 없을 때만 ‘상품 코드 없음’이 표시되고, 코드가 있으면 가격이 그대로 나옵니다.

이 인수는 조회 대상이 없을 때를 위한 설정입니다. 예를 들어 상품 코드는 존재하지만 해당 가격 셀이 비어 있는 경우까지 ‘상품 코드 없음’으로 바뀌는 것은 아닙니다. 결과가 예상과 다르면 먼저 코드가 실제로 있는지, 가격 칸에 값이 들어 있는지 각각 확인하는 편이 빠릅니다.

수식을 여러 행에 복사할 계획이라면 범위 고정도 챙겨야 합니다. G2에 =XLOOKUP(F2,$B$2:$B$6,$D$2:$D$6,"상품 코드 없음")을 넣고 아래로 채우면, F2는 F3·F4로 바뀌지만 상품 목록 범위는 그대로 유지됩니다. 달러 표시가 없으면 아래로 복사할 때 목록 범위까지 내려가 일부 상품을 놓칠 수 있습니다.

중복 코드와 금액 구간도 찾을 수 있어요

같은 상품 코드가 여러 번 나오면 XLOOKUP은 기본적으로 위에서 처음 만나는 값을 가져옵니다. 입고 기록처럼 최신 항목이 맨 아래에 쌓이는 표라면 마지막 항목을 찾고 싶을 수 있어요. 이때는 =XLOOKUP(F2,B2:B100,D2:D100,"기록 없음",0,-1)을 사용합니다. 다섯 번째 인수 0은 정확히 일치, 여섯 번째 인수 -1은 아래에서 위로 검색한다는 뜻입니다. 표가 실제로 최신 기록을 맨 아래에 추가하는 방식일 때 유용합니다.

정확한 숫자가 없는 구간표에도 쓸 수 있습니다. H2:H5에 구매 금액 기준을 0, 100000, 300000, 500000 순서로 적고, I2:I5에 각각의 할인율을 넣었다고 해볼게요. K2의 구매 금액이 350000이라면 =XLOOKUP(K2,H2:H5,I2:I5,"기준 없음",-1)로 300000원 구간의 할인율을 가져옵니다. 여기서 -1은 정확한 값이 없을 때 그보다 작은 값 중 가장 가까운 기준을 선택합니다. 구간을 헷갈리지 않으려면 기준 금액을 작은 순서로 배열해 두는 게 좋습니다.

상품 코드 일부만 기억날 때는 와일드카드 검색도 가능합니다. =XLOOKUP("*텀블러*",C2:C20,D2:D20,"상품 없음",2)는 상품명에 ‘텀블러’가 들어간 첫 항목의 가격을 가져옵니다. 다섯 번째 인수 2가 있어야 별표가 임의의 글자를 뜻합니다. 같은 이름이 여러 번 나오면 첫 항목만 반환하므로, 특정 색상이나 용량까지 구별해야 한다면 상품 코드로 찾는 편이 정확합니다.

수식이 예상대로 작동하지 않을 때

#N/A가 보이면 찾는 값이 목록에 있는지부터 확인하세요. 화면에는 같은 코드처럼 보여도 한쪽은 숫자, 다른 쪽은 텍스트로 저장됐거나 앞뒤에 공백이 들어 있을 수 있습니다. 특히 다른 시스템에서 내려받은 자료는 공백이 섞이기 쉬워요. 셀을 눌러 입력줄의 값을 비교하고, 필요하면 데이터 형식을 통일한 뒤 다시 조회해 보세요.

상품명과 가격을 한꺼번에 가져오고 싶다면 가져올 범위를 C2:D6으로 지정할 수도 있습니다. =XLOOKUP(F2,B2:B6,C2:D6)은 일치하는 행의 상품명과 가격을 옆 칸에 함께 표시합니다. 그런데 옆 칸에 이미 값이 있으면 결과가 펼쳐질 자리가 없어 #SPILL! 오류가 날 수 있습니다. 이 경우 결과가 들어갈 칸을 비우면 됩니다.

함수 이름 자체가 인식되지 않는다면 엑셀 버전도 확인해 보세요. 마이크로소프트 안내에 따르면 XLOOKUP은 Microsoft 365용 엑셀과 엑셀 2021·2024에서 사용할 수 있지만, 엑셀 2016·2019에서는 사용할 수 없습니다. 처음 연습할 때는 상품 코드 한 개로 기본 수식을 만든 뒤, 필요한 경우에만 오류 문구나 검색 방향을 추가하는 흐름이 가장 덜 헷갈립니다. 저도 복잡한 옵션을 외우기보다 ‘어디에서 찾아서 어느 열을 가져올까’를 먼저 생각하는 편입니다.

엑셀 xlookup 함수 쓰는법 쉽게 익히는 방법 - 요약
엑셀 xlookup 함수 쓰는법 쉽게 익히는 방법 | 코리아앱 | 앱·IT 서비스 리뷰 : https://koreaapp.net/17758
코리아앱 © koreaapp.net All rights reserved. powered by modoo.io