엑셀 xlookup 예시로 원하는 값 찾는 방법
얼마 전 상품 목록에서 가격을 찾다가 코드는 분명 있는데 다른 상품의 가격을 가져온 적이 있어요. 목록이 길어질수록 눈으로 행을 맞춰 보는 일이 생각보다 어렵더라고요. 이럴 때 상품 코드를 기준으로 값을 찾아 주는 XLOOKUP을 쓰면 편합니다. 수식이 처음에는 길어 보여도, ‘무엇을 찾을지, 어디서 찾을지, 무엇을 가져올지’만 정하면 금방 익숙해져요.
상품 코드로 가격 찾는 기본 예시
상품 목록이 A열부터 D열까지 있다고 해볼게요. A열은 상품 코드, B열은 상품명, C열은 가격, D열은 재고입니다. 데이터는 2행부터 6행까지 있고, A4에 P103, C4에 12,500이 들어 있습니다. E2에 조회할 코드 P103을 입력했다면 F2에는 다음 수식을 넣습니다.
=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"코드 확인")
결과는 12,500입니다. E2의 값을 A2:A6에서 찾고, 같은 행의 C열 값을 가져오는 방식이에요. 마지막의 ‘코드 확인’은 일치하는 코드가 없을 때 표시할 문구입니다. 이 부분을 빼면 찾지 못했을 때 오류가 나타납니다.
범위 앞의 달러 기호는 수식을 다른 셀로 복사해도 검색 범위가 움직이지 않게 고정합니다. 반면 E2는 고정하지 않았으므로 수식을 아래로 복사하면 E3, E4로 바뀝니다. 여러 상품의 가격을 한꺼번에 조회할 때 유용한 차이예요.
가격 말고 상품 코드나 재고를 가져오려면
가져올 열을 바꾸고 싶다면 세 번째 항목만 바꾸면 됩니다. 같은 상품 코드 P103의 재고가 D4에 28개라면 아래 수식의 결과는 28입니다.
=XLOOKUP(E2,$A$2:$A$6,$D$2:$D$6,"코드 확인")
상품명으로 상품 코드를 찾는 것도 가능합니다. 예를 들어 G2에 상품명을 적고 B열에서 찾아 A열의 코드를 가져오려면 이렇게 씁니다.
=XLOOKUP(G2,$B$2:$B$6,$A$2:$A$6,"상품명 확인")
검색하는 B열보다 결과가 있는 A열이 왼쪽에 있어도 괜찮습니다. 다만 상품명이 중복되면 기본 설정에서는 위에서부터 처음 만나는 행의 코드가 나옵니다. 같은 이름의 상품이 여럿이라면 고유한 상품 코드로 조회하는 편이 정확해요.
점수 구간처럼 정확히 일치하지 않는 값 찾기
이번에는 점수에 따라 등급을 표시해 볼게요. G2:G4에 구간 시작 점수 0, 50, 100을 차례로 적고, H2:H4에 각각 ‘기초’, ‘보통’, ‘우수’를 적습니다. J2에 점수 87이 있다면 다음 수식을 사용할 수 있어요.
=XLOOKUP(J2,$G$2:$G$4,$H$2:$H$4,"범위 밖",-1)
마지막의 -1은 점수가 구간 시작값과 정확히 같지 않을 때 그보다 작은 값 중 가장 가까운 값을 찾으라는 뜻입니다. 87은 50 구간에 들어가므로 ‘보통’이 표시됩니다. 점수가 100이면 정확히 일치하는 100 구간을 찾아 ‘우수’가 나와요. 시작 점수보다 낮은 음수가 들어오면 찾을 구간이 없어 ‘범위 밖’이 표시됩니다. 구간 시작값을 오름차순으로 적어 두면 나중에 기준을 고칠 때도 한눈에 확인하기 쉽습니다.
중복된 코드에서 가장 아래 기록 가져오기
거래 이력처럼 같은 상품 코드가 여러 번 나오는 표도 있죠. A10:A30에 상품 코드, D10:D30에 기록 날짜가 있고 E2의 코드에 해당하는 마지막 행의 날짜가 필요하다면 다음처럼 씁니다.
=XLOOKUP(E2,$A$10:$A$30,$D$10:$D$30,"기록 없음",0,-1)
끝의 0은 정확히 일치하는 코드만 찾겠다는 뜻이고, 마지막 -1은 아래에서 위로 검색하겠다는 뜻입니다. 그래서 같은 코드가 여러 번 있어도 가장 아래쪽에 있는 기록을 가져옵니다. 단, 이 값이 실제 최신 날짜가 되려면 거래 이력이 날짜순으로 쌓여 있어야 해요. 행 순서가 섞여 있다면 먼저 날짜를 확인해야 합니다.
수식이 예상과 다르게 나오면 검색 범위와 결과 범위의 행 수가 같은지, 코드에 앞뒤 공백이 들어갔는지부터 확인해 보세요. 특히 화면에는 똑같이 보이는 코드라도 문자와 숫자로 각각 저장되어 있으면 일치하지 않을 수 있습니다. 처음에는 상품 코드 하나로 가격을 찾아 보고, 익숙해지면 재고나 등급표에도 같은 방식으로 적용하는 게 가장 편하더라고요.
