이전 강좌에서 VLOOKUP 함수의 전반적인 사용법을 알아보았습니다. 인수의 이해가 조금 어렵긴 하지만 몇 번 사용해 보면 금방 하실 수 있을 것으로 생각합니다. 자동화 관점으로 보자면 꼭 알아야 하는 함수이니 사용법을 터득하시길 바랍니다. 더불어 꼭 아셔야 하는 사항이 하나 더 있습니다. VLOOKUP 함수는 한계가 있다는 것입니다. 그 한계는 기준값으로부터 찾고자 하는 항목을 찾을 수 없는 경우가 있을 수 있다는 것입니다. 이번 강좌는 VLOOKUP으로 특정 값을 찾을 수 없는 경우를 확인하고 해결 방법을 알아보겠습니다.
이 글로 바로 들어오신 분들은 아래 강좌를 꼭 보신 후 돌아와 주세요.
예제 파일은 이전 강좌에서 사용한 예제처럼 보이지만 모델명과 제품명의 열이 바뀐 상태입니다. 다른 형태죠.
결론부터 말씀드리면 위의 경우 '제품명'은 찾을 수 없습니다. 찾고자 하는 항목인 '제품명'이 기준값을 갖는 항목인 '모델명' 좌측에 있기 때문이죠. 이 경우 찾을 수 있는 값은 기준값 항목인 '모델명' 우측에 있는 '제조사, 보유 수량, 금액'입니다.
VLOOKUP 함수의 lookup_value와 table_array 인수를 잘 생각해 보시면 이유를 알 수 있습니다.
table_array을 만들 때 기준은 lookup_value 값을 갖는 항목부터입니다. 이 얘기는 table_array의 첫 열에서 lookup_value 값을 찾는다는 것이죠. 만약 제품명으로부터 모델명까지를 table_array로 설정한다면 첫 열의 항목은 '제품명'이므로 lookup_value를 '모델명'이 아닌 '제품명'에서 찾는다는 얘기가 됩니다. 당연히 찾을 수 없죠.
그래서 lookup_value(기준값)을 갖는 항목은 첫 열로, 찾고자 하는 항목은 반드시 lookup_value(기준값) 항목의 우측에 있어야 합니다.
즉, 그림처럼 lookup_value을 찾을 수 있는 항목인 '모델명' 우측의 '제조사, 보유수량, 금액'만을 찾을 수 있는 것이죠. '제품명'을 찾을 수 없습니다.
만약 '제조사'가 모델명 좌측에 있다면 '제조사' 역시 찾을 수 없습니다. VLOOKUP 함수의 분명한 한계입니다.
이 문제를 해결할 가장 좋은 방법은, VLOOKUP 사용이 예상된다면 아예 처음 원본을 작성할 때 lookup_value(모델명)를 미리 판단하여 표의 첫 열에 작성하는 것입니다. 하지만 지금처럼 이미 원본을 작성한 경우라면 해결 방법은 모델명과 제품명의 위치를 바꾸는 것밖에 없습니다.
그림처럼 이동할 범위 전체를 선택하고 마우스를 초록색 테두리로 이동한 뒤 마우스의 모양이
가 되는 것을 확인하고 shift키를 누른 상태에서 이동 삽입할 위치까지 클릭 드래그합니다. 이 방법은 이미 기초 강좌에서 설명했습니다. 잘 안된다면 아래 강좌를 확인하시고 처리해 주세요.
해결 방법은 찾았지만 좀 찝찝하죠. VLOOKUP 함수만 사용해야 한다면 별문제가 없겠지만, 만약 이미 "모델명"과 "제품명"을 참조한 수식이 있다면 경우에 따라 엄청난 에러가 발생할 수 있습니다. 이런 이유로 사실 원본의 항목 위치를 바꾸는 작업은 가능한 한 하지 않는 것이 좋습니다. 그래서 원본을 처음 작성할 때부터 항목의 순서를 고려하는 것이고요.
어쨌든 VLOOKUP으로 값을 찾는 방법은 매우 위험하지만, 이것밖에 없습니다. 대신 다른 방법을 사용하면 원본 항목을 바꾸지 않더라고 table_array 좌측에 있는 값도 찾을 수 있습니다.
가장 확실한 방법은 INDEX 함수와 MATCH를 사용하는 방법입니다. 두 함수의 사용법은 나중에 설명하겠습니다. 지금은 다른 방법으로 찾을 수 있다는 정도만 기억해 주시기 바랍니다. 그리고 위 두 함수보다 사용이 편한 함수가 있는데, 바로 XLOOKUP이라는 함수입니다. VLOOKUP과 이름도 비슷하죠. 사실 XLOOKUP은 INDEX, MATCH를 짬뽕한 방식입니다. 하지만 사용이 직관적이라 INDEX, MATCH를 사용하는 것보다, VLOOKUP을 사용하는 것보다 훨씬 간편하다는 장점이 있습니다. 문제는 모든 엑셀에서 사용할 수 없다는 점입니다. XLOOKUP 함수는 엑셀 365 버전 또는 엑셀 2021버전부터 사용할 수 있습니다. 불행히도 이전 버전에서는 사용할 수 없습니다. 이 부분은 조금 더 설명해야 겠네요.
엑셀은 굉장히 오래된 프로그램입니다. 1980년대에 만들어지고 1985년에 출시되었다고 합니다. 어느새 40년이 넘었습니다.
어떤 프로그램이든지 이렇게 오랜 기간 사용하다 보면 사용자들에겐 기능상 새로운 요구가 생깁니다. 그럼, 프로그램 개발자들은 그걸 반영하여 새로운 버전을 내놓죠. 엑셀도 마찬가지입니다. 해가 지날 때마다 오류를 수정하고 새 기능을 추가하여 새 버전을 내놓고 있습니다. 그러다 보니 자연스럽게 잘 쓰지 않거나 문제가 되는 기능들은 퇴화하여 사용할 수 없게됩니다. 예로 RANK라는 함수가 그렇습니다. RANK.EQ라는 함수가 그 자리를 대치했죠. VLOOKUP도 같습니다. 위와 같은 한계가 존재하기 때문에 한계를 극복하고자 하는 요구에 따라 새로운 함수 XLOOKUP이 생긴 것일 테죠. 아마도 시간에 지나면 사용하지 않는 함수가 될 것으로 생각됩니다.
하지만 사라질 수는 없습니다. 사용하지 않더라도 사라질 순 없습니다. 왜냐하면 VLOOKUP으로 이미 만들어진 엑셀 문서들이 존재하기 때문입니다. 사라진다면 VLOOKUP으로 만든 문서는 더 이상 읽은 수 없다는 얘기가 되기 때문에 어쨌든 존재하여 최소한 읽을 수는 있게 해 두어야 합니다.
다른 얘기로 좀 빠진 것 같지만, 여기서 하고 싶은 얘기는 2가지입니다.
첫째, VLOOKUP은 한계가 있고 대체할 함수도 있지만 현시점에서 반드시 사용할 수 있어야 한다는 것이고,
둘째, 그 한계를 극복한 INDEX, MATCH 함수와 XLOOKUP 함수 사용법을 익혀야 한다는 것입니다.
참고로 말씀드리면 INDEX, MATCH 함수 또한 개념적으로 쉬운 함수는 아닙니다. 두 함수를 섞는 것은 더 어렵습니다. 그래서 이 함수들은 좀 더 쉬운 함수들을 알아본 뒤 다시 설명하겠습니다. 그리고 XLOOKUP 함수는 INDEX, MATCH 함수를 이해하면 쉽게 이해할 수 있는 함수이니, 순서상 INDEX, MATCH 함수 뒤에 설명하겠습니다.
의견글 입니다.
()
