엑셀 xlookup으로 정비 부품값 찾는 방법

얼마 전 정비 내역을 엑셀로 관리하는 지인이 부품 가격표와 작업 기록을 번갈아 열며 같은 번호를 찾고 있었습니다. 부품이 몇십 개일 때는 눈으로 확인할 만하지만, 항목이 수백 개로 늘면 번호 하나를 잘못 읽어 견적이 달라질 수 있죠. 엑셀 xlookup은 이런 상황에서 부품 번호를 입력하면 해당 가격이나 재고를 바로 가져오는 함수입니다.

부품 번호로 가격을 불러오는 기본 수식

부품표의 A열에 부품 번호, B열에 부품명, C열에 가격, D열에 재고 수량이 있다고 가정해 보겠습니다. 2행부터 101행까지 데이터가 있고, 견적서 F2 셀에 찾을 부품 번호를 입력한다면 가격을 표시할 셀에는 다음 수식을 넣습니다.

=XLOOKUP(F2,$A$2:$A$101,$C$2:$C$101,"등록되지 않은 부품")

F2는 찾을 번호, A2:A101은 번호를 찾을 범위, C2:C101은 같은 행의 가격을 가져올 범위입니다. 예를 들어 F2가 BR-120이고 부품표에서 그 번호가 있는 행의 가격이 38,000원이라면 결과는 38000이 됩니다. 표시 셀에 통화 서식을 적용하면 38,000원처럼 읽기 편하게 보입니다. 마지막 문구는 번호를 찾지 못했을 때 표시됩니다. 이 문구를 빼면 일치 항목이 없을 때 #N/A 오류가 나타납니다.

수식에 붙은 $는 범위를 고정합니다. 견적서의 여러 행에 수식을 복사할 때 찾는 번호가 든 셀은 행에 따라 바뀌고, 부품표 범위는 그대로 유지돼야 합니다. 재고를 가져오려면 반환 범위만 $D$2:$D$101로 바꾸면 됩니다. 찾는 범위와 반환 범위는 행 수를 같게 잡아야 한다는 점도 기억해 두면 좋습니다.

VLOOKUP과 다른 점은 찾는 방향과 기본 일치 방식

기존 VLOOKUP은 지정한 표 범위의 첫 열에서 값을 찾습니다. 그래서 부품 번호가 가격 오른쪽에 있으면 열을 옮기거나 다른 수식을 써야 할 때가 있습니다. XLOOKUP은 번호가 있는 열과 결과를 가져올 열을 따로 지정하므로, 가격이 번호의 왼쪽에 있어도 사용할 수 있습니다. 열 번호를 세어 입력하지 않아도 되니 부품표에 열을 추가했을 때 수식을 읽고 고치기도 수월합니다.

또 하나는 기본값이 정확히 일치한다는 점입니다. 자동차 부품 번호는 BR-120과 BR-121처럼 한 글자만 달라도 다른 품목일 수 있습니다. 이때 가장 비슷한 번호를 임의로 가져오면 견적 오류로 이어집니다. XLOOKUP은 별도 설정이 없으면 입력값과 정확히 같은 항목을 찾습니다. 다만 같은 부품 번호가 여러 행에 중복돼 있다면 기본적으로 위에서 처음 만난 행의 값을 돌려주므로, 가격표에는 번호가 중복되지 않게 관리하는 편이 안전합니다.

같은 차량의 가장 최근 작업 기록 찾기

차량별 정비 이력에서도 쓸 수 있습니다. 작업 기록의 A열에 접수일, B열에 차량 번호, C열에 작업 내용이 있고 오래된 기록부터 새 기록 순서로 쌓인다고 해보죠. F2에 차량 번호를 적고 최근 작업 내용을 확인하려면 이렇게 입력합니다.

=XLOOKUP(F2,$B$2:$B$500,$C$2:$C$500,"이력 없음",0,-1)

끝의 -1은 아래쪽부터 거꾸로 찾으라는 뜻입니다. 같은 차량 번호가 여러 번 등장해도 맨 아래에서 처음 만난 작업 내용을 가져옵니다. 앞의 0은 정확히 일치하는 차량 번호만 찾겠다는 설정입니다. 예컨대 같은 차가 3월에 엔진오일을 교환하고 8월에 브레이크 패드를 교체했으며 8월 기록이 아래에 있다면 브레이크 패드 교체가 표시됩니다.

여기서 중요한 조건은 기록의 순서입니다. 접수일과 상관없이 행을 뒤섞어 놓았다면 맨 아래 기록이 가장 최근 작업이라는 보장은 없습니다. 이 경우 먼저 날짜 순으로 배열하거나, 기록을 추가할 때 최신 행이 아래에 놓이도록 관리해야 합니다. 함수가 날짜를 비교해 최신 작업을 골라주는 것은 아닙니다.

값이 안 나올 때 확인할 부분

번호가 분명히 있는데도 찾지 못한다면 입력값과 원본값을 나란히 확인해 보세요. 앞뒤 공백이 섞였거나, 숫자처럼 보이는 번호가 한쪽에서는 텍스트로 저장된 경우가 흔합니다. 차량 번호도 띄어쓰기 하나가 다르면 정확히 일치하지 않습니다. 또 가격이 예상과 다르다면 같은 번호가 여러 행에 있는지 먼저 찾아보는 게 좋습니다. 중복된 첫 행의 오래된 가격을 가져왔을 가능성이 있습니다.

공유할 파일이라면 엑셀 버전도 확인해야 합니다. Microsoft 안내에 따르면 XLOOKUP은 Excel 2021, Excel 2024와 Microsoft 365용 Excel에서 사용할 수 있지만 Excel 2016과 2019에서는 사용할 수 없습니다. 정비소마다 사용하는 프로그램 버전이 다르다면 파일을 넘기기 전에 확인하는 편이 좋습니다. 부품 번호를 일관되게 입력하고 가격표의 중복을 관리해 두면, 수식 하나가 견적 확인에 쓰는 시간을 꽤 줄여줍니다.