모든 엑셀 사용자가 오늘 VLOOKUP을 XLOOKUP으로 교체해야 하는 이유

스프레드시트 수식은 예전에는 쉽게 깨지는 것 같았다. 한 열 번호만 잘못 입력해도 전체 보고서가 엉망이 될 수 있었다. 하지만 마침내 VLOOKUP을 XLOOKUP으로 바꾸자, 엑셀은 예측 가능하고 유연하며 놀랍도록 잘 깨지지 않는다는 느낌을 주기 시작했다.

마침내 엑셀 논리가 이해되게 만든 구문 분석

현대 조회 수식의 해부

내가 왜 이전 작업 방식을 버렸는지 설명하기 전에, 이러한 함수들이 엑셀과 어떻게 소통하는지 보는 것이 도움이 됩니다. VLOOKUP은 전통적으로 데이터가 행보다는 열에 세로로 쌓여 있기 때문에 주로 사용하는 조회 수식이 되었습니다.

이 함수는 네 개의 엄격한 인수를 필요로 합니다. 조회 값을 입력하고, 전체 데이터 블록을 지정하며, 몇 번째 열을 참조할지 알려주고, 그 다음 임의의 근사값을 반환하지 않도록 FALSE를 명시적으로 입력해야 합니다:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

XLOOKUP은 이 경직된 설정을 훨씬 더 다재다능한 것으로 대체합니다. 큰 테이블 범위를 참조하고 열을 세는 대신, 검색할 값, 검색할 열, 반환할 데이터를 지정하기만 하면 됩니다:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

XLOOKUP는 데이터셋을 Excel 테이블로 변환하면(Ctrl+T를 누르거나 삽입 > 테이블을 클릭) 읽기가 훨씬 쉬워집니다. 테이블은 위치 기반 참조를 이름이 지정된 관계로 바꾸기 때문입니다. 아래의 모든 예제에서는 다섯 개의 구별된 열(ID, 이름, 부서, 역할, 이메일)을 포함하는 StaffDirectory라는 이름의 테이블을 사용하겠습니다.

VLOOKUP 때문에 사람 계산기처럼 열을 세어야 했다

열 순서가 모든 것을 망칠 때

항상 VLOOKUP에서 나를 좌절하게 만든 것은 수동으로 열을 세는 것이었습니다. 만약 셀 A2에 있는 이름을 기반으로 David Cho의 이메일 주소를 가져오고 싶다면, 전체 테이블 조회는 원하는 결과를 반환하지 않습니다. VLOOKUP은 지정한 범위의 첫 번째 열만 검색할 수 있기 때문입니다. 그 열이 'ID'이고 'Name'이 아니므로 VLOOKUP은 찾고자 하는 값을 찾을 수 없어서 수식이 오류를 반환합니다:

=VLOOKUP($A$2, StaffDirectory, 5, FALSE)

VLOOKUP을 작동시키려면, 수식 범위를 두 번째 열에서 시작하도록 바꿔야 하는데, 그러면 바로 계산이 복잡해 보이게 됩니다:

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 4, FALSE)

거기서 무슨 일이 일어났는지 주목했나요? 테이블 배열이 'Name' 열에서 시작하도록 변경해야 했기 때문에, 열 인덱스 번호가 5에서 4로 바뀌었습니다. 그러나 전체 테이블을 참조하지 않았다는 사실을 잊고 5로 두면 #N/A 오류가 발생합니다. 그리고 잘못 세어서 3을 입력하면, 수식은 그의 이메일이 아니라 직책을 반환합니다.

더 나쁘게도, 설령 지금 당신의 수식이 올바르더라도, 누군가 그 특정 서브셋 안에 새로운 열을 삽입하면, 하드코딩된 인덱스 번호가 즉시 깨져서 보고서가 엉망이 됩니다.

하지만 XLOOKUP을 사용할 때는 원시 숫자, 사용자 지정 범위, 열 개수가 사라집니다. 수식은 마치 말로 하는 문장처럼 읽힙니다:

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[Email])

이제 누군가 새로운 열을 삽입하거나 레이아웃 순서를 변경해도, 수식은 위치가 아니라 이름이 지정된 열을 참조하기 때문에 안정적으로 유지됩니다.

네이티브 오류 처리는 IFERROR의 필요성을 없애줍니다

추가 계층 없이 누락된 데이터 처리

검색어가 없으면 중지하고 오류 코드를 반환하는 VLOOKUP과 달리, XLOOKUP은 수식 안에서 직접 오류를 처리할 수 있게 해줍니다.

VLOOKUP을 사용하여 보기 흉한 #N/A 오류를 숨기려면, IFERROR 함수 안에 수식을 감싸야 하며, 이는 셀에 불필요한 잡음을 추가하고 복잡한 시트를 문제 해결하기 어렵게 만듭니다:

=IFERROR(VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 2, FALSE),"Employee not found")

XLOOKUP는 네 번째 인수를 사용하여 이름이 명단에 없을 경우 읽을 수 있는 메시지를 셀에 바로 넣음으로써 그 불필요한 래퍼를 완전히 제거합니다:

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[Department], "Employee not found")

네, 수식의 길이는 비슷할 수 있지만, XLOOKUP 버전을 만드는 것과 이해하는 것이 얼마나 더 쉬운지 잠시 감상해 보세요.

정확한 일치가 이제 숨겨진 함정 대신 기본값입니다

VLOOKUP의 FALSE 인수 제거

VLOOKUP 함수는 위험한 기본값에서 작동합니다: 마지막 인수를 생략하면 시스템은 근사 일치를 원한다고 가정합니다. 이러한 배경 동작은 일상적인 스프레드시트 작업에서 두 가지 주요 문제를 만듭니다.

먼저, 잘못된 양성 결과가 나올 수 있습니다. 뒤따르는 인수를 잊어버렸다고 가정해 보겠습니다:

=VLOOKUP($A$2, StaffDirectory[[ID]:[Name]], 2)

비록 "1065"가 목록에 없더라도, VLOOKUP은 오류를 반환하지 않습니다. 대신, 정렬 순서에 따라 조회 값보다 작거나 같은 가장 큰 값을 찾습니다. 이 경우, Priya Patel의 ID인 "1064"를 찾아 그녀의 이름을 반환합니다. 결과적으로 시트에는 잘못된 데이터가 들어가고, 무엇인가 잘못되었다는 경고도 없게 됩니다.

둘째, 데이터를 정렬하는 것을 잊을 수 있습니다. VLOOKUP의 근사 일치 엔진은 조회 열이 오름차순(작은 값에서 큰 값 순)으로 정렬되어 있어야만 작동합니다. 테이블이 내림차순으로 정렬되어 있거나 완전히 임의로 배열되어 있다면, 마지막 인수를 생략할 경우 엔진은 예측할 수 없고 혼란스러운 결과를 반환할 수 있습니다. 데이터가 올바르게 정렬되지 않았다면, VLOOKUP은 잘못된 일치를 반환하거나 때로는 #N/A 오류와 함께 완전히 실행되지 않을 수도 있습니다.

그러나 XLOOKUP은 정확한 일치(앞서 언급한 두 가지 선택적 동작 중 첫 번째)를 기본값으로 설정함으로써 이러한 두 가지 함정을 모두 없앱니다. 일치 모드 인수를 비워 두면 데이터가 어떻게 정렬되었는지 완전히 무시하고 어쨌든 정확한 일치를 찾습니다:

=XLOOKUP($A$2, StaffDirectory[ID], StaffDirectory[Name], "ID not found")

정확한 일치가 기본값이기 때문에, 여기서 '1065'를 검색하면 사용자 지정 'ID를 찾을 수 없음' 메시지가 안전하게 반환되어, 스프레드시트를 조용한 잘못된 긍정이나 정렬 함정으로부터 모두 보호합니다.

검색 방향을 변경하는 데 복잡한 우회 방법이 필요하지 않아야 합니다

데이터를 정렬하지 않고 아래쪽에서 위쪽으로 스캔하기

데이터셋이 커지면서, 가장 오래된 정보보다는 실행 로그에서 가장 최근 정보를 찾아야 할 때가 많습니다. 이것은 앞서 말씀드린 두 가지 유용한 선택적 동작 중 두 번째입니다: 검색 방향 역순.

우리 샘플 데이터셋을 살펴보세요: 이름 "Marcus Vance"가 두 번 나타나는 이유는 그가 "Marketing"(데이터 행 1)에서 "Sales"(데이터 행 5)로 이동했기 때문입니다. 즉, 데이터 행 5에는 그의 최신 정보가 포함되어 있습니다.

하지만 데이터를 정렬하거나 밀집 배열 수식을 작성하지 않는 한, 표준 VLOOKUP은 처음 발견한 일치 항목(이 경우 데이터 행 1)을 반환하며, 더 최근의 항목을 놓치게 됩니다:

=VLOOKUP($A$2, StaffDirectory[[Name]:[Department]], 2, FALSE)

XLOOKUP에서 검색 방향을 바꾸는 것은 단지 여섯 번째 인수로 -1을 입력하는 것을 의미합니다:

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[Department], "Employee not found", 0, -1)

이것은 아래에서 위로 스캔을 실행하므로 그의 현재 부서를 반환합니다.

전체 배열을 반환하면 나중에 수식이 깨지는 것을 방지할 수 있습니다

단일 수식으로 여러 행과 열 가져오기

VLOOKUP을 사용하여 직원의 부서, 역할 및 이메일을 요약 행에 가져오려면 세 개의 개별 조회 수식을 작성하고 세 개의 셀에서 인덱스 번호를 수동으로 변경할 수 있습니다:

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 2, FALSE)

그리고

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 3, FALSE)

그리고

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 4, FALSE)

동적 배열은 스필링을 통해 이것을 훨씬 간단하게 만듭니다. 반환 배열에 인접한 열 블록을 선택하면 하나의 XLOOKUP 수식으로 여러 셀이 자동으로 채워집니다:

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[[Department]:[Email]])

이로 인해 유지해야 할 수식이 훨씬 적어져 큰 시트를 더 쉽게 다룰 수 있습니다.

엑셀을 보다 스마트하게 검색하는 방법 수용하기

VLOOKUP을 XLOOKUP으로 바꾸는 것은 내가 스프레드시트를 만드는 방식을 완전히 바꿨습니다. 깨진 열 인덱스, 숨겨진 근사 일치, 또는 엄격한 테이블 레이아웃에 대해 걱정하는 대신, 수식을 고치는 데 시간을 덜 쓰고 실제로 데이터 작업을 하는 데 더 많은 시간을 쓸 수 있습니다. 물론, 이 함수가 옛 친구의 유일한 현대적 버전은 아닙니다. 복잡한 그리드에서 INDEX-MATCH 조합을 사용하는 것에 익숙하다면, 전통적인 MATCH 함수 대신 XMATCH로 전환하는 것은 동일한 안전하고 정확한 일치 기본값을 행 및 열 검색에도 적용할 수 있음을 의미합니다.