VLOOKUP vs XLOOKUP 이라는 주제로 엑셀에 대해서 알아보도록 하겠습니다. 각 함수의 정의 VLOOKUP 함수란? XLOOKUP 함수란? 의 형태로 설명하고, 함수 구문을 다음으로 확인한 후, 각 엑셀 함수의 기능상 장점을 이야기하겠습니다.
사용방법
아시다시피 VLOOKUP 함수는 데이터 분석 및 작성에 있어 가장 많이 사용하는 함수 중 하나입니다. 하지만 VLOOKUP 의 수식 구조 한계상 원본 데이터의 형태에 따라 사용이 불가능한 경우가 발생합니다.
이 경우 XLOOKUP 을 사용한다면 해결할 수 있는 여러 예제가 있기 때문에 그것을 핵심으로 확인해 주신다면, 해당 블로그에 투자하신 약 3분 정도의 시간이 가치로 환원될 수 있다고 생각합니다.
VLOOKUP 함수란?
엑셀 찾기 함수에서 대표 함수는 무엇일까요? VLOOKUP 이 대표 함수라고 말씀하시는 분들이 가장 많을 겁니다. 그 외에는 XLOOKUP, INDEX MATCH, 또는 OFFSET의 동적 범위를 활용한 여러 변형된 함수들이 있을 겁니다.
정의
엑셀 VLOOKUP 함수는 지정한 셀 범위에서 조건 갑을 활용하여 원하는 값을 찾을 때 사용하는 찾기 함수입니다.
함수 언어 구조
VLOOKUP이라는 함수 이름은 수직의 의미인 Vertical (Abbr. Vert.) 와 찾기 기능을 의미하는 Look-up을 조합하여 만든 이름입니다. 그래서, 수직 방향으로 조건 값을 활용하여 원하는 값을 찾아 출력하는 함수의 기능을 합니다.
함수 구문
= VLOOKUP (조건 값, 지정한 범위, 찾을 값의 열 위치, 정확 일치 여부)
함수 구문 항목별 확인
- 조건 값 : 찾기 기능을 사용할 데이터에 존재하는 값 중 일반적으로 중복되지 않는 값을 조건 값으로 사용합니다.
- 지정한 범위 : 일반적으로 열의 개수를 2개 이상으로 설정하며, 행의 숫자는 데이터의 끝의 행까지 포함하여 설정합니다. VLOOKUP 함수 수식 구문을 작성 후 여러 다른 셀에 그대로 사용하기 위하여 2개의 기능을 사용합니다. 첫 번째는, 절대 참조의 기능으로 범위를 고정하는 것입니다. 그리고 두 번째는, 데이터의 테이블 기능을 사용하여 범위 고정뿐만 아니라 항목에 대한 이름도 함께 설정합니다.
- 찾을 값의 열 위치 : 지정한 범위의 가장 왼쪽부터 오른쪽으로 숫자를 세어 열 위치를 숫자로 표현합니다. 일반적으로 숫자는 1부터 세어 열의 위치를 계산해 주시면 되십니다. 범위 내 숨겨진 셀까지도 포함하여 숫자를 세어주면 되며, 일반적으로 범위의 열 항목의 위치를 번호로 확인한 뒤, 해당 번호를 VLOOKUP 함수에 사용하거나, MATCH 함수를 활용하여 항목의 이름을 조건 값으로 활용하여 지정한 범위 내의 찾을 열의 위치를 숫자로 자동 출력합니다.
- 정확 일치 여부 : 정확한 일치는 숫자 0을 사용하여 TRUE 기능을 사용하며, 일부 일치는 숫자 1을 사용하여 FALSE 기능을 사용합니다. 일반적으로 0의 숫자를 사용합니다.
XLOOKUP 함수란?
XLOOKUP 함수는 EXCEL 365 버전에서 사용할 수 있으며, VLOOKUP 함수와 HLOOKUP 함수의 기능의 한계를 넘어선 엑셀 찾기 함수입니다. 더 나아가서, 함수의 적용 되는 범위를 넘어선 상황에서 ERROR 값이 있을 때 원하는 형식으로 변환하는 IFERROR 함수의 기능도 대체할 수 있습니다.
정의
XLOOKUP 함수는 범위 또는 배열을 검색한 다음, 찾은 첫 번째 일치 항목에 해당하는 항목을 자동으로 반환합니다. 일치하는 항목이 없으면 함수 구문에 따라 근사치 값에 대해 반환하는 것도 가능합니다.
함수 언어 구조
XLOOKUP이라는 함수는 찾기 함수의 한계인 가로 방향과 세로방향의 경계가 없는 함수입니다. 개인적으로 X의 의미를 HLOOKUP 함수의 Horizontal과 VLOOKUP 함수의 Vertical의 Multiple 기능으로 여기고 있습니다.
함수 구문
=XLOOKUP(lookup value, lookup_array, return_array,[if_not_found], [Match mode], [search mode])
함수 구문 항목별 확인
- lookup value: 데이터를 찾기 위해 기준이 될 수 있는 값을 입력합니다.
- lookup_array: 데이터에 기준이 있는 열을 입력합니다. (A열 이라면, A:A, 열전체를 선택하는 것 권장합니다. 가장 완벽한 응용방법은 절대참조를 사용 하는 것입니다.)
- return_array: 불러오고자 하는 데이터의 열을 입력합니다. (D열 이라면, D:D, 이 역시 열전체를 선택하는 것을 권장합니다.)
- [if_not_found]: 선택 사항으로, 기준 값과 비교하여 데이터가 없을 경우 어떤 형식으로 반환할 것인지 입력합니다. IFERROR 함수의 기능이 XLOOOKUP 함수에는 포함 되어 있다고 볼 수 있습니다.
- [Match mode]: 선택 사항으로, 기준 값과의 일치 형태를 선택할 수 있는 구문입니다.
- [search mode]: 선택 사항으로, 기준값이 단일 값이 아닐 경우 가져올 방향을 결정합니다.
위의 3가지 구문만으로도 값을 불러 올 수 있지만, XLOOKUP 함수는 단일 데이터를 불러오는 데 가장 최적화된 함수이기 때문에 모든 기능에 대해 터득한 뒤 활용하는 것이 좋습니다. - [if_not_found] 구문은 위에서 말한 것과 같이 선택 사항입니다. 만약 기준 값과 비교하여 데이터가 없을 경우 어떤 형식으로 반환할 것인지 입력합니다. 아래의 예시를 확인 부탁 드립니다.
=XLOOKUP(lookup value, lookup_array, return_array,0)
이 함수 구문은 데이터의 기준과 같은 비교 값이 없을 시 0을 출력하며, 텍스트 형식의 입력을 원한다면 큰따옴표를 사용하여 “텍스트”의 형식으로 입력 가능합니다. - [Match mode] 구문은 선택 사항으로, 기준 값과의 일치 형태를 선택할 수 있는 구문입니다. 0과 -1, 그리고 1과 2를 사용 할 수 있습니다. 0이 완벽한 일치를 의미하기 때문에, 해당 구문까지 사용을 원할 시에는 0을 입력합니다.
- 0 : 정확한 일치
- -1 : 정확한 일치를 우선시 하고 없을 경우 기준 값 보다 작은 값을 기준으로 찾습니다. (기준값이 숫자일 경우 사용 가능 합니다.)
- 1 : 정확한 일치를 우선시 하고 없을 경우 기준 값 보다 큰 값을 기준으로 찾습니다. (기준값이 숫자일 경우 사용 가능합니다.)
- 2: 와일드카드 캐릭터를 사용 하여 원하는 데이터를 찾을 수 있습니다. 와일드카드란 [ , ?, ~ ] 3가지를 의미하며, 기준 값이 되는 값이 TEXT일 경우 사용이 가능합니다. 예를 들어 기준값을 “1”로 두어 검색한다면 TEXT의 서식에서 1로 시작하는 부분 기준과 일치하는 값을 가져옵니다.
- [search mode] 구문은 선택 사항으로, 기준값이 단일 값이 아닐 경우 가져올 방향을 결정합니다. 1과 -1, 그리고 2와 -2를 사용합니다.
- 1 : 가장 위의 값을 가져오며, 일반적으로 가장 많이 쓰입니다.
- -1 : 가장 밑의 값을 가져옵니다.
- 만약 기준값이 2회 정도 중복일 경우는 search mode의 1과 -1을 변수로 두고 수식을 만드시면 lookup 이후에도 본인이 원하는 값을 가져올 수 있습니다. 2와 -2는 “이진탐색“을 사용하여 값을 찾습니다. 2는 오름차순, -2는 내림차순에서 실시하며, 실제 빠른 찾기가 가능하기 때문에 오름차순까지 필요한 lookup의 경우 사용합니다.
VLOOKUP 함수 예제
XLOOKUP 함수 예제
결론
VLOOKUP 함수는 Excel에서 오래 전부터 사용되어 온 검색 함수입니다. 이 함수는 단일 열에서 특정 값을 찾아 다른 열에서 해당 값을 반환합니다.
반면에, XLOOKUP 함수는 VLOOKUP 함수보다 보다 간단하고 유연한 검색 기능을 제공합니다. XLOOKUP 함수는 VLOOKUP 함수와 비교하여 다음과 같은 장점을 가지고 있습니다.
- 검색 대상 열과 반환 대상 열을 자유롭게 선택할 수 있습니다.
- 검색 대상 열이 반환 대상 열보다 왼쪽에 있을 필요가 없습니다. (VLOOKUP 함수의 최대 한계)
- 검색 대상 열에 중복된 값이 있어도 작동합니다.
- 검색 대상 값이 정확히 일치하지 않아도 가까운 값을 찾아 반환할 수 있습니다.
따라서, XLOOKUP 함수는 VLOOKUP 함수보다 더 유연하고 강력한 검색 기능을 제공합니다.