기획자로서 엑셀에서 다른 것은 다 몰라도 VLOOKUP 함수는 알아야 한다고 질리도록 들었다.
실제로 실무를 진행할 때에도 실질적으로 가장 많이 사용하는 함수는 VLOOKUP이다.
XLOOKUP은 데이터의 구성이 예외적일 때 사용하는 VLOOKUP과 유사한 함수이므로 함께 다뤄보도록 하겠다.
VLOOKUP이란?
Microsoft 공식 문서에 따르면 VLOOKUP 함수를 다음과 같이 설명하고 있다.
테이블이나 범위에서 행별로 항목을 찾아야 할 때 VLOOKUP을 사용합니다.
예를 들어 부품 번호로 자동차 부품의 가격을 찾거나 직원 ID를 기반으로 직원 이름을 찾습니다.
이 말처럼, VLOOKUP 함수는 첫 번째 열에 입력된 값을 기준으로, 해당하는 행의 다른 값들을 찾아 가져오는 기능이다.
예시 테이블과 함께 살펴보자.
[표1]
| 직원 Id | 이름 | 부서 | 직급 | 급여 |
| 1001 | 김철수 | 마케팅 | 과장 | 5,000,000 |
| 1002 | 이영희 | 인사 | 대리 | 4,000,000 |
| 1003 | 박민수 | 개발 | 사원 | 3,500,000 |
| 1004 | 정혜진 | 영업 | 부장 | 6,500,000 |
| 1005 | 최성호 | 디자인 | 팀장 | 5,800,000 |
여기서 VLOOKUP으로 검색할 때의 key 값은 첫 번째 열에 놓인 데이터인 직원 Id이다.
즉, 우리는 위와 같은 테이블에서 직원 Id를 입력해서 해당 직원 Id의 행에 있는 이름, 부서, 직급, 급여와 같은 다른 열의 정보를 가져올 수 있는 것이다.
VLOOKUP 함수는 다음과 같다.
=VLOOKUP(검색할_값, 표_범위, 열_인덱스_번호, [범위 검색])- 검색할_값: 표_범위의 첫 번째 열에서 값을 기준으로 행을 찾아낼 값을 입력한다.
- 위 표의 사례에선 1001~1005 중 하나를 입력하면 된다.
- 표_범위: 표 전체 영역을 선택하여 넣으면 된다.
- 예를 들자면, 표1[#모두] 라든지 A1:E6
- 열_인덱스_번호: 찾고자 하는 정보가 있는 열의 인덱스 번호를 입력한다.
- 위 표의 사례에서 이름은 2, 부서는 3, 직급은 4, 급여는 5의 인덱스를 갖는다.
- [범위 검색]: 선택사항, 기본값은 TRUE이지만 대부분의 경우 정확도를 위해 FALSE를 넣어주어야 한다.
- TRUE: 유사 일치, 검색할_값 이 유사한 경우에도 데이터를 가져온다. 정확도가 낮다.
- FALSE: 검색할_값이 정확히 일치하는 경우에만 데이터를 가져온다.
위 예시 테이블에서 직원 Id = 1001을 기준으로 그의 부서를 가져오려면 다음과 같이 입력하면 된다.
=VLOOKUP(1001, 표1[#모두], 3, FALSE)
VLOOKUP 함수를 사용해 다른 테이블에서 값 가져오기
범위만 잘 설정하면 다른 테이블의 값도 가져올 수 있기 때문에, 다음과 같은 작업도 가능하다.
표1의 '급여' 열에 직급에 해당하는 급여 입력하기
[표1]
| 직원Id | 이름 | 부서 | 직급 | 급여 |
| 1001 | 김철수 | 마케팅 | 과장 | ? |
| 1002 | 이영희 | 인사 | 대리 | ? |
| 1003 | 박민수 | 개발 | 사원 | ? |
| 1004 | 정혜진 | 영업 | 부장 | ? |
| 1005 | 최성호 | 디자인 | 팀장 | ? |
[표2]
| 직급 | 급여 |
| 사원 | 3,500,000 |
| 대리 | 4,000,000 |
| 과장 | 5,000,000 |
| 팀장 | 5,800,000 |
| 부장 | 6,500,000 |
이 문제를 해결하려면 [표 1]의 '급여' 열에 다음과 같은 수식을 입력하면 된다.
=VLOOKUP([@직급], 표2[#모두], 2, FALSE)- 표로 설정하지 않아 [@직급] 이 어려울 때에는 수식을 입력하는 행 기준으로 직급에 해당하는 셀 번호를 입력해주면 된다. (위 예시의 경우 D2)
XLOOKUP이란?
하지만 데이터 테이블이 언제나 내 마음대로 첫 열에 원하는 key 값이 존재하는 것은 아니다.
가령 우리는 [표1]에서 '이름' 을 기준으로 데이터를 조회하고 싶을 수도 있다.
이럴 땐 XLOOKUP 함수를 사용해주면 된다.
Microsoft 공식 문서에 따르면 XLOOKUP 함수를 다음과 같이 설명하고 있다.
XLOOKUP 함수를 사용하여 테이블 또는 행별 범위에서 항목을 찾습니다.
XLOOKUP을 사용하면 반환 열이 어느 쪽에 있는지와 관계없이 한 열에서 검색어를 찾고 다른 열의 동일한 행에서 결과를 반환할 수 있습니다.
XLOOKUP 함수는 다음과 같다.
=XLOOKUP(검색할_값, 검색할_범위, 반환할_범위, [일치하는_항목이_없음], [일치_모드], [검색_모드])- 검색할_값: 검색할_범위에서 찾아낼 값을 입력한다.
- 예를 들어 표1에서 이름이 '김철수'인 항목을 검색하고 싶다면 '김철수'가 들어가면 된다.
- 검색할_범위: 검색할_값을 찾아낼 범위를 입력한다.
- 아래의 예시에서 표2의 '이름' 열에서 '김철수'를 찾아내야 하기 때문에 표2[이름] 이 들어가면 된다.
- 반환할_범위: 찾아낸 행의 어느 열을 반환할지를 입력한다.
- 아래의 예시에서 표2의 '부서' 열을 반환하려면 표2[부서] 가 들어가면 된다.
- [일치하는_항목이_없음]: 선택사항, 검색할_범위에 검색할_값이 없을 때 어떤 값으로 보여줄 지 입력한다.
- 예를 들어 표2에서 '강영수' 를 검색하려고 시도하면 값을 찾을 수 없는 상태가 된다. 이럴 때 '값을 찾을 수 없음' 이라든지 잘못된 상태를 표시해주는 텍스트를 입력하면 상황을 명확하게 이해하는 데에 도움이 된다.
- 다만, 이쪽을 차라리 비워놓고 조건부 서식을 쓰는 쪽이 좀 더 가시성이 좋다고 생각한다.
- [일치_모드]: 선택사항, 값을 찾을 때의 옵션을 입력한다. VLOOKUP의 [범위 검색]과 유사하지만 입력되는 값이 다르다.
- 0: 정확히 일치
- -1: 정확히 일치하거나 다음으로 작은 항목
- 1: 정확히 일치하거나 다음으로 큰 항목
- 2: 와일드카드 문자(링크) 일치 / 3: Regex(정규식) 일치
- [검색_모드]: 선택사항, 검색 로직을 입력한다.
- 1: 오름차순 검색 / -1: 내림차순 검색
- 2: 이진 검색 (오름차순으로 정렬됨) / -2: 이진 검색 (내림차순으로 정렬됨)
이름으로 검색해서 표1의 '부서' 열에 해당하는 부서 입력하기
[표1]
| 직원Id | 이름 | 부서 | 직급 | 급여 |
| 1001 | 김철수 | ? | 과장 | 5,000,000 |
| 1002 | 이영희 | ? | 대리 | 4,000,000 |
| 1003 | 박민수 | ? | 사원 | 3,500,000 |
| 1004 | 정혜진 | ? | 부장 | 6,500,000 |
| 1005 | 최성호 | ? | 팀장 | 5,800,000 |
[표2]
| 부서 | 이름 |
| 마케팅 | 김철수 |
| 인사 | 이영희 |
| 개발 | 박민수 |
| 영업 | 정혜진 |
| 디자인 | 최성호 |
이 문제를 해결하려면 표1의 '부서' 열에 다음과 같은 수식을 입력하면 된다.
=XLOOKUP([@이름], 표2[이름], 표2[부서])
VLOOKUP과의 다른 점은 검색하려는 값(@이름)이 표1과 2에서 모두 첫 번째 열에 있지 않은데도 해당 값을 key값으로 검색할 수 있다는 점이다.
공식적으로 엑셀이 안내하는 바에 따르면 XLOOKUP이 VLOOKUP의 상위 호환이라고는 하지만, VLOOKUP이 좀 더 단순하고 가볍게 느껴져서 자주 사용하게 되는 것 같다.
상황에 따라 적합한 함수를 적절하게 사용하면 작업을 수월하게 진행할 수 있을 것이다.