들어가기 전에
Excel의 VLOOKUP 함수는 실무에서 가장 많이 알려진 조회 함수 중 하나이지만 2019년에 XLOOKUP 함수가 등장하면서 상황이 많이 달라졌습니다. VLOOKUP 함수가 가진 한계를 보완해 주기 때문입니다. 왜 VLOOKUP 보다 XLOOKUP을 더 선호하는지, XLOOKUP 함수가 얼마나 유용한지 등에 대해 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Forget VLOOKUP in Excel: Here's Why I Use XLOOKUP
- URL: https://www.howtogeek.com/forget-vlookup-hlookup-excel-use-xlookup/
XLOOKUP과 VLOOKUP 구문 비교
VLOOKUP보다 XLOOKUP을 선호하는 이유를 설명하기 전에 두 구문이 어떻게 작동하는지 살펴봅니다.
XLOOKUP
XLOOKUP에는 6개의 인수가 있습니다.
=XLOOKUP(a,b,c,d,e,f)
- a(필수): 조회 값
- b(필수): 조회 배열
- c(필수): 반환 배열
- d(선택 사항): 조회 배열(b)에서 조회 값(a)을 찾을 수 없는 경우 반환할 텍스트
- e(선택 사항): 일치 모드
- f(선택 사항): 검색 모드
이 예에서는 Excel에서 H1 셀의 이름을 기준으로 직원 ID를 조회하고 그 결과를 H2 셀에 반환하도록 하려고 합니다.
이를 위해 H2 셀에 다음 수식을 입력합니다.
=XLOOKUP(H1,B2:B12,A2:A12,“Invalid name”,0,1)
H1 셀에는 Excel에서 조회해야 하는 값(Mary)이 들어 있고, B2~B12는 해당 값을 찾을 수 있는 위치(직원 이름), A2~A12는 해당 결과를 가져올 위치(직원 ID)이며, “Invalid name”은 조회 배열에서 조회 값을 찾을 수 없는 경우 Excel에서 반환할 값입니다.
인수 e와 f의 기본값을 선택한 이유는 정확히 일치하는 결과를 원하고 Excel이 조회 배열의 맨 위부터 검색하기를 원하기 때문입니다(자세한 내용은 나중에 설명합니다).
VLOOKUP
VLOOKUP 함수는 4개의 인수를 가질 수 있습니다.
=VLOOKUP(a,b,c,d)
- a(필수): 조회 값
- b(필수): 조회 및 반환 배열
- c(필수): 열 인덱스 번호
- d(선택 사항): 일치 모드
이 예에서는 Excel에서 H4 셀의 직원 ID를 기준으로 국적을 조회하고 그 결과를 H5 셀에 반환하도록 하려고 합니다.
이를 위해 H5 셀에 다음과 같이 입력합니다.
=vlookup(h4,a2:e12,5,false)
H4 셀에는 조회 값(ID 3264)이 포함되어 있고, A2~E12 셀은 Excel에서 해당 값과 해당 반환값을 찾아야 하는 곳이며, 다섯 번째 열(국가)은 결과를 찾을 배열이고, 정확히 일치하는 값(FALSE)을 원하기 때문입니다.
열을 셀 필요가 없음
한 가지 중요한 차이점은 전자는 결과를 찾을 열 인덱스 번호를 지정해야 하는 반면, 후자는 그렇지 않다는 점입니다. 이는 VLOOKUP은 조회 배열과 반환 배열을 하나의 인수로 결합하는 반면, XLOOKUP은 두 개의 개별 인수로 정의하기 때문입니다.
VLOOKUP
VLOOKUP에서 열 인덱스 번호를 지정해야 하는 경우 몇 가지 문제가 발생할 수 있습니다.
- 특히 조회 배열이 수백 개의 열로 구성된 경우 실수로 열을 잘못 계산하기 쉽습니다.
- 열을 추가하거나 제거하면 열 인덱스 번호의 정확도에 영향을 줄 수 있습니다.
- 조회 배열은 가장 왼쪽 열이어야 하고 반환 배열은 오른쪽 열이어야 합니다. 이는 VLOOKUP의 다양성을 제한합니다.
XLOOKUP
반면에 XLOOKUP은 조회 배열을 별도의 인수로 포함하므로 다음과 같은 이점을 누릴 수 있습니다.
- 계산할 필요가 없습니다! 수식의 해당 부분에 도달하면 마우스를 사용하여 반환 배열을 선택하기만 하면 됩니다. 이렇게 하면 시간이 절약되고 정확도가 높아집니다.
- 반환 배열은 지정된 셀 범위 내에 있으므로 스프레드시트에서 열을 제거하거나 추가해도 XLOOKUP 수식에 영향을 미치지 않습니다.
- 반환 배열은 조회 배열의 양쪽에 위치할 수 있으므로 XLOOKUP이 VLOOKUP보다 더 다재다능합니다.
더 많은 근사치 일치 옵션
VLOOKUP과 XLOOKUP은 모두 정확히 일치하는 값(행에서 정확히 일치하는 값)과 근사 일치하는 값(가까운 일치하는 값)을 반환할 수 있습니다.
VLOOKUP
보다 구체적으로, VLOOKUP의 근사 일치(수식에서 TRUE로 표시됨)는 조회 값보다 큰 값을 찾을 때까지 조회 배열을 검색합니다. 그런 다음 거기에서 한 행 위에 있는 값을 반환합니다.
아래 예에서 학생 D의 점수가 65점이라고 가정해 보겠습니다. VLOOKUP은 65라는 조회 값을 가져와서 조회 배열을 검토하고 조회 값보다 큰 첫 번째 값(이 경우 70)을 찾은 다음, 위 행에서 성적을 반환합니다(C 등급).
여기에는 두 가지 단점이 있습니다. 첫째, 조회 배열은 오름차순으로 나열되어야 합니다. 둘째, 일치하지 않는 인수를 명시할 수 있는 옵션이 VLOOKUP에 없기 때문에 조회 배열에 FAIL 행을 추가해야 합니다.
XLOOKUP
XLOOKUP은 VLOOKUP의 1차원 근사 일치에 대한 세 가지 대안을 제공합니다.
- -1: 정확히 일치하는 항목이 없는 경우 조회 배열에서 다음으로 작은 값을 반환합니다.
- 1: 정확히 일치하는 항목이 없는 경우 조회 배열에서 다음으로 큰 값을 반환합니다.
- 2: 와일드카드를 사용하여 보다 유연한 조회를 허용합니다.
다시 학생 D를 예로 들어 보겠습니다. 점수가 65점인 경우 Excel은 조회 배열에서 다음으로 작은 값은 60이고 다음으로 큰 값은 70임을 알 수 있습니다. 이 학생은 아직 B 성적의 임계값(70점)에 도달하지 않았으므로 Excel에서 조회 배열에서 다음으로 작은 값(60점)을 가져와 반환 배열에서 C 성적을 반환해야 합니다. 따라서 수식에 일치하는 옵션으로 -1을 입력합니다.
즉, 정확히 일치하는 값이 없는 경우 Excel은 전체 조회 배열을 샅샅이 뒤져서 가장 가까운 상위 또는 하위 값을 찾습니다. 또한 학생의 점수가 어떤 성적과도 일치하지 않는 경우 XLOOKUP 구문의 네 번째 인수를 사용하여 FAIL이라는 단어를 반환할 수 있으므로 조회 배열에서 FAIL 행을 생략할 수도 있습니다.
추가 검색 모드(방향)
VLOOKUP은 첫 번째부터 마지막까지 검색하여 가장 먼저 일치하는 값을 반환하는 반면, XLOOKUP은 네 가지 검색 옵션을 제공합니다.
VLOOKUP
대부분의 조회 시나리오에서 조회 배열을 첫 번째부터 마지막까지 검색하면 필요한 결과가 반환됩니다. 예를 들어 전화번호와 사람 이름이 나열된 디렉토리가 있는 경우, 입력한 이름을 기준으로 전화번호를 찾으려면 해당 사람의 이름이 한 번만 나타날 가능성이 있으므로 VLOOKUP을 사용하면 문제가 없습니다.
XLOOKUP
하지만 XLOOKUP을 사용하면 검색 방향을 선택할 수 있습니다.
- 1: 처음부터 마지막까지 검색
- -1: 마지막에서 첫 번째로 검색
- 2: 이진 검색(조회 배열이 오름차순으로 정렬됨)
- -2: 이진 검색(조회 배열을 내림차순으로)
마지막에서 첫 번째 조회의 장점은 날짜 순서로 나열된 조회 배열에서 가장 최근에 발생한 값을 찾을 수 있다는 것입니다. VLOOKUP으로 이 작업을 수행하려면 먼저 데이터의 순서를 반대로 해야 합니다.
오류 출력 정의하기
XLOOKUP의 유용한 기능 중 하나는 VLOOKUP에는 없는 '찾을 수 없는 경우 인수'입니다.
VLOOKUP
정확히 일치하는 VLOOKUP 수식에서 값을 찾을 수 없는 경우 Excel은 끔찍한 #N/A 오류 메시지를 반환합니다. 이 문제를 해결하기 위해 저는 항상 IFERROR 함수 내에 VLOOKUP 함수를 포함시켜 VLOOKUP이 일치하는 값을 찾지 못할 경우 출력을 정의할 수 있도록 했습니다.
=IFERROR(VLOOKUP(B6,$E$2:$F$8,2,TRUE),” ”)
이 방법은 합리적인 해결책이지만 수식 작성을 훨씬 더 복잡하게 만들고 데이터 분석의 정확성에 영향을 줄 수 있는 문제를 숨길 수 있습니다.
XLOOKUP
XLOOKUP에는 “if not found” 인수가 미리 제공되므로 값이 조회에 나타나지 않을 경우 발생하는 상황을 정의할 수 있으므로 IFERROR 내에 수식을 포함할 필요가 없습니다.
유출된 배열 반환
VLOOKUP의 가장 까다로운 속성 중 하나는 단일 일치만 반환할 수 있는 반면, XLOOKUP은 범위를 반환할 수 있다는 점입니다.
XLOOKUP
이 예제에서는 다음과 같이 입력합니다.
=XLOOKUP(I1,A2:A7,B2:F7)
셀 I1(이 경우 Quizpicable Me)의 값을 조회하고, 셀 A2~A7에서 해당 값을 찾은 다음, 해당 값을 모두 유출된 배열로 반환합니다.
VLOOKUP
VLOOKUP을 사용하여 이를 복제하려면 다음과 같이 입력합니다.
=vlookup(i1,a2:f7,2:6)
세 번째 인수(열 인덱스 번호)는 범위가 아닌 한 자리 숫자만 가능하기 때문에 #REF! 오류가 반환됩니다. 즉, 수식에 추가하는 매개 변수에 따라 단일 값 또는 범위를 반환할 수 있으므로 XLOOKUP이 훨씬 더 적응력이 뛰어납니다.
마치며
2019 이전 버전의 Excel은 XLOOKUP을 지원하지 않으므로 VLOOKUP과 HLOOKUP은 여전히 유효합니다. 몇 년 동안 Office 패키지를 업그레이드하지 않은 사람에게 스프레드시트를 보내는 경우와 같이 여전히 사용해야 하는 경우가 있을 수 있습니다.
'Excel' 카테고리의 다른 글
| 소규모 기업이 Excel로 CRM을 구축하기 위한 7가지 팁 (3) | 2025.01.12 |
|---|---|
| Excel 이미지 처리 관련 4가지 최신 업데이트 (5) | 2025.01.11 |
| Excel 통합 문서를 자동화하는 좋은 방법 6가지 (2) | 2025.01.09 |
| Excel에서 AI를 사용하는 6가지 유용한 방법 (4) | 2025.01.08 |
| Excel의 조사식 창으로 주요 데이터 포인트 모니터링하기 (9) | 2025.01.07 |
