엑셀 XLOOKUP과 VLOOKUP, 같은 표에서 결과가 달라지는 이유
중복된 품목 코드와 수량별 할인표까지 들어 있는 주문서. 함수만 XLOOKUP으로 바꿔도 같은 단가와 할인율이 나올까?
같은 품목표에서 두 수식을 비교한다
주문서에 품목 코드를 입력하면 기준표의 단가가 따라오는 파일을 생각해 보자. XLOOKUP은 단가 열을 직접 지정하므로 기준표에 열을 끼울 때 반환할 열 번호를 다시 세는 수고를 줄인다. 다만 같은 코드가 두 번 있으면 기본적으로 첫 행을 가져오고, 수량별 할인표에서는 기존 수식의 구간 조회 방식을 따로 옮겨야 한다. 아래 품목표와 할인표에서 기존 수식을 나란히 남겨 두면 어느 조건에서 결과가 바뀌는지 볼 수 있다.
아래는 설명용 품목표다. 위치 안내용 행 번호를 제외한 코드·품목명·단가 세 열을 A1:C5에 넣고, F2에 찾을 코드 P-20을 입력한다고 가정한다. P-20을 두 번 넣은 이유는 기본 조회와 ‘마지막 항목 조회’의 차이를 같은 자료에서 보기 위해서다. 단가 숫자는 예제 값이며 실제 상품 가격이 아니다.
| 행 | A열: 코드 | B열: 품목명 | C열: 단가 |
|---|---|---|---|
| 2 | P-10 | 노트 | 2500 |
| 3 | P-20 | 펜 세트 | 4800 |
| 4 | P-30 | 파일 | 3200 |
| 5 | P-20 | 펜 세트 | 5200 |
=VLOOKUP(F2,$A$2:$C$5,3,FALSE)
=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5)첫 수식은 A2:C5의 첫 열에서 F2를 찾고, 그 표의 세 번째 열을 반환한다. 둘째 수식은 A2:A5에서 찾은 위치에 대응하는 C2:C5의 값을 반환한다. 기본 검색 순서에서는 둘 다 첫 P-20에 해당하는 4800이 나온다. 아래쪽에 5200이 적혀 있다는 사실만으로 새 가격을 고르는 것은 아니다.
달러 기호는 복사할 때 기준표 주소를 고정한다. 수식을 아래로 복사하면 F2는 F3으로 이동하지만 기준표는 계속 A2:C5를 가리킨다. 이 예제는 쉼표로 인수를 구분한다. Excel의 지역 설정에서 구분자가 세미콜론이면 그 설정에 맞춰 입력한다. 함수의 기본 인수 구조는 VLOOKUP과 XLOOKUP 공식 설명에 나와 있다.
정확한 코드와 구간 조회는 전환 방법이 다르다
품목 코드처럼 같은 값만 찾아야 하는 열에서는 VLOOKUP의 마지막 인수를 FALSE로 쓴다. 생략하거나 TRUE로 쓰면 근사 일치 방식이 적용된다. 이 방식은 첫 열의 정렬을 전제로 하므로 정렬되지 않은 코드표에 사용하면 예상하지 않은 단가가 나올 수 있다. XLOOKUP은 일치 방식의 기본값이 정확히 일치인 0이어서 같은 목적의 수식을 짧게 쓸 수 있다.
그렇다고 기존 VLOOKUP의 TRUE를 모두 잘못된 설정으로 볼 수는 없다. 주문 수량에 따라 할인율이 달라지는 구간표라면 근사 일치가 의도한 계산이다. 예를 들어 H2:H4에 구간 시작 수량 0, 10, 50을 오름차순으로 넣고, I2:I4에 할인율 0%, 5%, 10%를 넣었다고 하자. F2에 수량 27을 입력하면 10개 이상 구간의 5%가 필요하다.
=VLOOKUP(F2,$H$2:$I$4,2,TRUE)
=XLOOKUP(F2,$H$2:$H$4,$I$2:$I$4,"해당 구간 없음",-1)이때 XLOOKUP의 다섯 번째 인수 -1은 정확히 같은 값이 없으면 다음으로 작은 값을 찾는다. 그래서 27보다 작은 구간 시작값 중 가장 가까운 10을 선택한다. 이 인수를 생략하면 27이라는 행이 없어 ‘해당 구간 없음’이 나온다. 전환 전후 결과가 달라지는 이유는 새 함수가 더 정확해서가 아니라 계산하려는 관계를 다르게 지정했기 때문이다.
위 예제는 ‘10개부터’라는 하한표여서 다음으로 작은 경계값을 찾는다. 반대로 ‘10개까지, 50개까지’처럼 상한을 적은 표에서 수량 27은 상한 50에 속한다. 이때 XLOOKUP의 다섯 번째 인수를 1로 지정하면 정확히 같은 값 또는 다음으로 큰 값을 찾는다. 같은 근사 조회라도 표가 구간의 시작과 끝 중 무엇을 적었는지에 따라 -1과 1의 선택이 달라진다. 공식 일치 모드 설명에 두 방향이 정의돼 있다.
왼쪽 열과 새로 끼운 열을 다루는 방식
VLOOKUP의 기본 형태는 찾을 열이 표 범위의 맨 왼쪽에 있어야 한다. 예제에서 품목명 B열로 A열 코드를 가져오려면 표 배치를 바꾸거나 다른 조합이 필요하다. XLOOKUP은 검색 범위와 반환 범위를 별도로 지정하므로 =XLOOKUP(F2,$B$2:$B$5,$A$2:$A$5)처럼 왼쪽 코드 열을 바로 지정할 수 있다. 이 수식을 쓸 때 F2에는 코드 대신 품목명을 넣는다.
원래 표의 B열과 C열 사이에 ‘규격’ 열을 삽입하면 단가는 D열로 이동한다. VLOOKUP 수식의 표 범위가 A2:D5로 확장돼도 반환 번호 3은 세 번째 열인 규격을 뜻한다. 반면 C열을 직접 반환 범위로 지정했던 XLOOKUP은 통상적인 워크시트 열 삽입에서 이동한 D열 참조를 따라간다. 열 번호를 사람이 다시 세는 작업을 줄일 수 있는 차이다. 열 삽입은 별도 사본에서 시험하며, 뒤의 수식은 처음 A~C열 표를 사용한다.
이는 모든 표 변경에서 수식이 자동으로 안전해진다는 뜻은 아니다. 찾을 범위가 A2:A5인데 반환 범위를 C3:C6으로 잘못 지정하면 길이는 같아도 행이 한 칸 어긋난다. P-10을 찾은 위치에 다음 행의 단가가 대응하는 식이다. 두 범위의 첫 행과 끝 행을 함께 맞춰야 원하는 품목의 값이 연결된다.
| 작업 | VLOOKUP | XLOOKUP |
|---|---|---|
| 코드의 정확한 일치 | FALSE를 지정한다. | 기본값으로 적용된다. |
| 반환 열 선택 | 표 안의 열 번호를 적는다. | 반환할 범위를 지정한다. |
| 왼쪽 값 조회 | 기본 형태로 직접 처리하기 어렵다. | 반환 범위를 왼쪽에 지정한다. |
| 여러 반환 열 | 일반 단일 결과 수식은 열별로 작성한다. | 같은 행의 여러 열을 한 번에 반환할 수 있다. |
중복 코드에서 마지막 행을 찾는다는 뜻
처음 품목표에서 P-20이 두 번 나오는 이유가 가격 변경 이력이라면 첫 값 4800을 계속 쓰는 것이 잘못일 수 있다. 먼저 =COUNTIF($A$2:$A$5,F2)로 코드가 몇 번 있는지 센다. P-20이면 결과는 2다. COUNTIF는 조건과 맞는 셀의 수를 반환하므로, 한 행만 있어야 하는 기준표에서 중복을 드러내는 데 쓸 수 있다.
‘아래에 추가한 행이 현재 적용값’이라는 규칙으로 관리하는 표라면 뒤에서부터 찾는 수식을 쓸 수 있다. 아래 식의 다섯 번째 인수 0은 정확한 일치이고, 여섯 번째 인수 -1은 마지막 항목부터 검색하는 방향이다.
=XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"코드 없음",0,-1)P-20을 입력하면 이 예제에서는 5200을 반환한다. 그러나 표를 단가순으로 다시 정렬하거나 과거 행을 아래에 붙이면 마지막 행이 최신 가격이라는 전제가 깨진다. 역순 검색은 날짜를 비교하지 않는다. 실제로 최신 적용일을 찾아야 하는 파일에는 적용일 열을 두고 그 날짜에 맞는 행을 선정하는 계산이 필요하다. ‘최신값 찾기’라는 이름으로 역순 수식만 배포하면 표를 정렬한 사람이 계산 기준까지 바꾸게 된다.
같은 코드가 여러 번 있다는 이유만으로 행을 지워서도 안 된다. 고객별 계약 단가처럼 코드와 고객이 함께 한 항목을 정하는 자료일 수 있다. 그런 표에서 코드 하나로만 조회하면 함수가 아니라 검색 조건이 부족한 것이다. 주문서가 사용하는 기준표를 품목당 한 행으로 만들거나, 실제 항목을 구별하는 키를 추가한 다음 조회하도록 설계를 바꾼다.
한 행의 여러 열과 모든 일치 행
XLOOKUP에서 반환 범위를 B2:C5로 지정하면 찾은 한 행의 품목명과 단가를 나란히 받을 수 있다. 예를 들어 =XLOOKUP(F2,$A$2:$A$5,$B$2:$C$5)는 P-20의 첫 행에서 ‘펜 세트’와 4800을 반환한다. 결과가 옆 셀로 펼쳐지므로 출력할 공간을 비워 둔다.
반환 범위를 넓혔다고 P-20의 두 행이 모두 나오는 것은 아니다. 변경 이력 전체를 펼쳐 보고 싶다면 조건과 맞는 행들을 반환하는 FILTER가 맞다. 아래 수식은 품목표의 두 P-20 행을 모두 가져온다.
=FILTER($A$2:$C$5,$A$2:$A$5=F2,"코드 없음")이 결과에서는 품목명이 중복돼도 두 가격을 함께 볼 수 있다. 한 가격을 주문서에 채우는 업무와 변경 기록을 검토하는 업무가 다른 결과를 요구하기 때문이다. FILTER 공식 설명에 따르면 결과 배열은 필요한 크기로 펼쳐지며, 조건에 맞는 값이 없을 때 표시할 문구도 지정할 수 있다. 출력 범위에 기존 표가 있다면 비어 있는 다른 위치에 결과를 만든다.
‘코드 없음’을 0원으로 처리하지 않는다
XLOOKUP의 네 번째 인수에 ‘코드 없음’을 넣으면 일치하는 항목을 찾지 못했을 때 그 문구가 나타난다. 품목표에 없는 P-99를 입력해 이 분기가 작동하는지 볼 수 있다. 빈칸이나 숫자 0을 넣으면 화면이 깔끔해지지만, 주문서에서는 미등록 코드가 실제 단가 0인 항목처럼 합산될 수 있다. 이 예제에서는 후속 계산 전에 누락된 코드를 찾기 쉽도록 문구를 사용한다.
기준표에 코드가 분명히 있는데도 찾지 못하면 검색값과 기준표 값의 자료형, 앞뒤 공백을 비교한다. 숫자로 저장한 123과 텍스트로 저장한 123, 끝에 공백이 붙은 코드가 원인일 수 있다. Microsoft의 #N/A 해결 안내도 이 두 원인을 따로 다룬다. 먼저 보조 열에서 공백을 제거한 값과 원래 값을 대조하고, 업무상 같은 코드임을 확인한 뒤 정규화한다.
주소가 한 행 어긋나 잘못된 단가를 반환하는 경우에는 ‘코드 없음’도 뜨지 않는다. 일치 항목이 없는 오류와, 항목은 찾았지만 다른 행의 값을 가져온 오류는 증상이 다르다. 전자는 입력 코드·자료형·공백을, 후자는 검색 범위와 반환 범위의 시작 행을 대조해야 잘못 연결된 값을 찾을 수 있다.
기존 수식을 남긴 채 비교 열을 만든다
실무 파일에서는 원본 사본을 만든 뒤 기존 결과 옆에 XLOOKUP 결과 열을 추가한다. 정상 코드, 없는 코드, 중복 코드, 구간 경계값을 각각 입력해 두 열이 무엇을 반환하는지 대조한다. 수량 할인 예제라면 0, 10, 27, 50에서 구간이 맞는지 볼 수 있다. 기존 결과와 다르다는 사실만으로 새 수식을 틀렸다고 판단하지 않고, 해당 주문이 받아야 하는 단가나 할인율과 비교한다.
그다음 실제로 관리하는 방식대로 표를 바꿔 본다. 규격 열을 끼우고, 새로운 품목을 범위 끝에 추가하고, 가격 이력을 정렬했을 때 같은 조건의 주문이 계속 같은 결과를 받는지 본다. 고정 범위 A2:A5 아래에 여섯 번째 행을 추가했다면 참조 범위에 새 행을 포함해야 한다. 함수만 교체해도 모든 신규 데이터가 저절로 검색되는 것은 아니다.
마지막으로 파일을 다시 계산할 사람의 Excel 버전을 맞춘다. Microsoft는 XLOOKUP이 Excel 2016과 2019에서 제공되지 않는다고 명시한다. 그런 사용자가 직접 편집해야 한다면 VLOOKUP을 유지하거나 =INDEX($C$2:$C$5,MATCH(F2,$A$2:$A$5,0))처럼 INDEX와 MATCH의 정확한 일치 조합을 쓸 수 있다. 수식이 필요 없는 최종 보고서만 전달한다면 별도 사본에 값을 붙여넣되, 그 사본은 새 코드를 입력해도 다시 조회하지 않는 고정 결과물이다. 원본 계산 파일은 따로 보존한다.