xlookup - vlookup을 대체할 엑셀 함수

01 개요

엑셀을 어느 정도 하다 보면 단순 사칙연산에서 벗어나 조건을 넣는 if, sumif, countif 등을 쓰고, 조건에 맞는 값을 가져오는 vlookup도 요긴하게 사용하는데요. vlookup의 최고 단점인 찾고자 하는 데이터 열이 반환될 값을 갖고 있는 열보다 왼쪽에 있어야 한다는 조건을 해결해 주는 함수가 있어 소개해보려 합니다.

 

02 vlookup 복습

VLOOKUP(Vertical Lookup)은 특정 값을 기준으로 수직 방향으로 데이터를 검색합니다.

 

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

• lookup_value: 찾고자 하는 값
• table_array: 데이터 범위
• col_index_num: 반환할 열 번호 (1부터 시작)
• range_lookup: 정확히 일치 여부 (TRUE: 근사치, FALSE: 정확히 일치)

 

사실 설명만 놓고 보면 무슨 말인지 모릅니다.

 

어떤 기준이 되는 데이터가 있고, 찾고자 하는 값이 쭉 나열되어 있을 때 그 값과 매칭되는 데이터를 자동으로 넣고 싶을 때 쓰는 함수입니다.

 

예를 들어 E 열에 있는 데이터를 A, B 열을 참고하여 F에 집어넣고 싶을 F2에 아래와 같이 넣어 만들 수 있습니다.

=VLOOKUP(E2, $A$2:$B$4,2,FALSE)

 

이렇게 자동으로 A가 어떤 값을 가지는지 찾아 넣어줍니다.

 

VLOOKUP 함수 안에서 세번째 값인 '2'는 데이터 범위에서 '왼쪽'에서 '두 번째' 열에 있는 값을 가져오라는 뜻입니다.

그리고 실사용에서 마지막 값에 TRUE를 쓸 일은 거의 없으니 FALSE로 알고 있는 것이 좋습니다.

 

근데 사용하다 보면 vlookup의 큰 단점들을 알게 될 것입니다.

• 왼쪽 열 기준 검색 불가: 검색 값은 반드시 첫 번째 열에 있어야 함
• 열 번호 고정: 열 번호를 직접 입력해야 하기 때문에, 데이터 열을 중간에 삽입하거나 삭제하면 수식을 수정해야 함

 

03 xlookup 소개

 

vlookup의 두 단점을 완벽하게 해결해줄수 있는 함수가 xlookup입니다.

 

이전에 설명했듯이 이렇게 A,B,C가 반환하려는 값 보다 오른쪽에 있으면 vlookup을 쓸 수가 없어집니다.

 

이때, xlookup은 열 순서 상관없이 똑같이 vlookup을 수행할 수 있게 해 줍니다.

 

=xlookup(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: 찾고자 하는 값
• lookup_array: 검색할 범위
• return_array: 반환할 범위
• if_not_found: 값을 찾을 수 없을 때 반환할 값 (옵션)
• match_mode: 정확도 설정 (0: 정확히 일치, -1: 작거나 같은 값, 1: 크거나 같은 값) (옵션)
• search_mode: 검색 방향 (1: 위에서 아래, -1: 아래에서 위) (옵션)

 

=XLOOKUP(D2,B:B,A:A,"")

즉, D2에 있는 값을 B열에서 찾아 있으면 같은 A열 같은 행에 있는 값을 불러오고, 만약 없다면 아무 값도 반환하지 않게 해 줍니다.

 

04 xlookup의 강점

양방향 검색 가능 (왼쪽, 오른쪽 모두 가능)

lookup_array와 return_array를 따로 지정하기 때문에 원하는 방향으로 검색이 가능합니다.

가독성 개선

vlookup의 col_index_num으로 숫자를 적어야 하는 것 대신 return_array를 지정함으로써 직관적으로 어느 열에 있는 값을 가져오는지 알 수 있습니다.

오류 처리 내장

if_not_found에 값을 넣음으로써, 아무 값도 없을 때 채울 기본 값을 설정할 수 있어 iferror를 사용하지 않아도 됩니다.

 

05 xlookup의 한계점

엑셀 버전 제한 - 엑셀 2019 이상 또는 엑셀 365 사용

실사용 입장에서는 이게 가장 큰 문제일 수도 있습니다. 회사가 크게 지원해주지 않아 아직도 엑셀 2019 이전 버전 또는 엑셀 365를 사용하고 있지 않다면 쓸 수 없습니다.

중복 불가

vlookup 함수 사용 시 기준이 되는 table_array에 중복이 있다면 가장 첫 번째 값만 사용이 되었는데요.

이 부분은 xlookup을 사용해도 해결되지 않습니다.

 

06 결론

현재 xlookup을 쓸 수 있는 환경이라면, vlookup을 사용하던 습관을 멈추고 xlookup으로 갈아타는 습관을 들이는 것을 추천합니다.