엑셀에는 오래전부터, 표에서, 수직 방향이나, 수평방향으로 키워드를 검색한 후, 이를 토대로 해당 행위나 열에 속한 값을 돌려주는 함수인 VLOOKUP과, HLOOKUP만을 지원했었다.
Vlookup은 수직 방향으로 키워드를 검색한 후 같은 행에 속한 데이터를 반환하며, HLOOKUP은 수평방향으로 키워드를 찾은 후, 해당 키워드가 속한 열에 포함된 데이터들을 반환하기 때문에, 여러 개의 값으로 구성된 테이블에서, 특정 키워드에 부속되는 정보 값들을 찾을 때 이들 vlookup과 hlookup은 아주 유용하게 사용되어 왔다.
예를 들어 다음과 같이 데이터가 정리된 테이블에서,
만일 반려견의 이름을 키워드로 삼아 해당 반려견에 추가 정보를 찾으려면, 다음과 같이 매 컬러 면마다 함수를 지정해야만 했다.
vlookup(검색어, 데이터 범위, 반환할 데이터의 칼럼 순서, 0)
예를 들어 “콩이”라는 이름으로 vlookup 검색을 하는 경우, 칼럼 별로 다음과 같이 함수를 기재해야 했고,
첫 번째 칼럼에는 =VLOOKUP($G$3, $A$2:$E$19,1,0)
두 번째 칼럼에는 = VLOOKUP($G$3, $A$2:$E$19,2,0)
세 번째 칼럼에는 =VLOOKUP($G$3, $A$2:$E$19,3,0)
네 번째 칼럼에는 =VLOOKUP($G$3, $A$2:$E$19,4,0)
다섯째 칼럼에는 =VLOOKUP($G$3, $A$2:$E$19,5,0)
Note: $G$3에 검색할 키워드가 지정된 곳이고, $A$2:$E$19가 데이터 원본이 있는 범위임.
함수식 끝의 0은 정확히 일치하는 결과만을 찾도록 지정하는 값이다.
각 칼럼 별로 다음과 같은 결과를 얻을 수 있었다.
참고) HLOOKUP은 수평방향으로 첫 행에서 키워드를 찾는 점을 제외하고는 VLOOKUP과 원리와 동작 형태가 동일하다.
그런데, 이러한 VLOOKUP이나 HLOOKUP에는 두 가지 문제가 있다.
1) 그 한 가지는 열병(또는 형별)로 함수를 일일이 지정해 주어야 한다는 것으로,
반환할 데이터의 종류가 많으면, 일일이 함숫값을 지정하는 것도 일이다.
2) 또 다른 단점은 검색할 키워드가 속한 열(또는 행)은 데이터 원본의 반드시 첫 번째 열 (또는 행)이어야 한다는 점이다. 즉 VLOOKUP의 경우 검색어가 속한 열보다 왼쪽에 있는 데이터 값은 반환이 불가능하다는 약점이 있다. (HLOOKUP의 경우 검색할 행보다 위쪽 행어 있는 데이터는 반환을 못함)
그런데, 비교적 최근 버전의 엑셀부터는 이러한 문제를 해결할 수 있는 xlookup이라는 함수가 새롭게 지원되고 있다. xlookup은 기본적 기능은 vlookup과 유사하나, 키워드를 검색할 범위와, 데이터를 반환할 배열을 완전히 독립적으로 지정할 수 있다는데 기존 vlookup과 큰 차이가 있다.
xlookup(검색어, 검색 범위, 반환할 데이터 범위, 0)
예를 들어 위와 같이 “콩이”라는 이름으로 xlookup 검색을 하는 경우, vlookup처럼 칼럼 별로 함숫값을 기재할 필요 없이 다음과 같이 함수를 하나만 지정하면 완료되므로, 무척 작업이 간단해진다.
즉 첫 번째 칼럼에만 “= XLOOKUP($G$3, $A$2:$A$19, $A$2:$E$19) “라고 기재하면 된다,.
Note: $G$3에 검색할 키워드가 지정된 곳이고, $A$2:$A$19가 검색어를 찾을 배열이다.
$A$2:$E$19는 검색어가 발견되었을 때 반환할 값을 찾을 범위의 값이다.
이 xlookup은 다음과 같이, 범위의 값을 반환하게 된다. 매 열 단위로 함숫값을 지정하지 않았음에도, 나머지 열에 속한 데이터 값들도 모두 반환받을 수 있었다. 이처럼 xlookup은 매 칼럼 별로 함수를 지정할 필요가 없으므로, vlookup보다 훨씬 간단하게 결과를 얻을 수 있다.
또한 xlookup은 검색어를 찾을 부분과, 반환 데이터를 찾을 부분을 전혀 독립적으로 지정하므로, 검색어가 데이터 원본의 중간부에 있더라도 자유롭게 지정이 가능한 장점이 있다.
xlookup(검색어, 검색 범위, 반환할 데이터 범위, 0)