엑셀이나 구글 스프레드시트를 오래 사용하다 보면 반드시 'VLOOKUP'이라는 용어를 접하게 됩니다. VLOOKUP은 정확히 무엇이고 어떤 역할을 할까요? 이 가이드에서는 VLOOKUP의 기능과 이를 활용해 업무 효율을 높이는 방법을 살펴보고, 엑셀과 구글 스프레드시트에서 최적으로 활용하는 방법까지 단계별로 안내합니다.
또한 VLOOKUP에 관한 다양한 궁금증과 함께, 함수를 사용할 때 흔히 겪는 실수와 함정도 짚어보겠습니다.
VLOOKUP이란?
VLOOKUP은 '세로 방향 조회(vertical lookup)'의 줄임말로, 마이크로소프트 엑셀에서 처음 등장한 함수입니다. 특정 열에서 원하는 값을 검색한 뒤, 그 정보를 이용해 같은 행에 있는 다른 값을 불러오는 기능을 합니다.
예를 들어 '이름', '전화번호', '주소'라는 세 개의 열에 정보가 채워져 있다고 가정해 보겠습니다. VLOOKUP을 사용하면 '이름' 열에서 특정 이름을 검색하고, 해당 이름과 같은 행에 위치한 전화번호나 주소를 자동으로 표시할 수 있습니다. 단, VLOOKUP은 대소문자를 구분하지 않는다는 점에 유의하세요.
위 예시처럼 데이터 양이 적을 때는 크게 와닿지 않을 수 있지만, 시트에 방대한 정보가 있고 그중 일부 값을 다른 곳에서 활용해야 할 때 VLOOKUP은 매우 강력한 도구가 됩니다.
예를 들어 한 시트에 마스터 정보 목록을 만들어 두고, 이후 시트들에서는 VLOOKUP만으로 마스터 목록에서 데이터를 가져올 수 있습니다. 이렇게 하면 한 시트만 업데이트하면 나머지 시트의 값들이 자동으로 따라갑니다.
VLOOKUP 구문의 기본 구조는 다음과 같습니다.
=VLOOKUP(
찾으려는 값,
값을 검색할 셀 범위,
표시하려는 값이 있는 열 번호,
정확히 일치 또는 유사 일치 여부
)
엑셀과 구글 스프레드시트에서 VLOOKUP 사용하기
VLOOKUP 구문은 언뜻 복잡해 보이지만 실제로는 생각보다 훨씬 간단합니다. 아래의 상세 설명을 따라 해 보세요.
- 먼저 데이터를 담은 표가 필요합니다. 앞서 소개한 예시와 동일하게 '이름(Name)', '주소(Address)', '전화번호(Phone Number)' 세 개의 열에 정보를 입력했습니다.
- 이제 특정 필드에 입력된 이름에 해당하는 전화번호를 불러오려 합니다. 여기서는 "Iris Watson"의 전화번호를 찾아보겠습니다.
- 빈 셀을 더블클릭하고
=VLOOKUP(을 입력해 수식을 시작합니다. 첫 번째로 요구되는 값은 'lookup_value(찾을 값)'로, 전화번호를 검색하는 데 사용할 정보입니다.
- VLOOKUP 바로 앞 셀에 이미 "Iris Watson"이라는 이름을 입력해 두었으므로, 그 셀을 lookup_value로 사용하면 됩니다. 이 시점의 수식은 다음과 같은 형태입니다.
=VLOOKUP(e12,
- 다음은 table_array입니다. 데이터를 가져올 표 전체를 의미하며, 데이터가 있는 표 범위를 드래그하여 선택하면 됩니다. 이 단계에서 수식은 다음과 비슷한 모양이 됩니다.
=VLOOKUP(e12,A1:C5,
- 수식의 세 번째 값은 'col_index_number(열 색인 번호)'입니다. 정보를 추출하려는 열의 번호를 뜻하며, 선택한 표 범위에서 '이름'은 1번, '주소'는 2번, '전화번호'는 3번에 해당합니다. 전화번호를 찾으려 하므로 수식은 다음과 같습니다.
=VLOOKUP(e12,A1:C5,3,
- 마지막 부분은 검색값과 정확히 일치하는지, 아니면 유사하게 일치하는지 설정하는 항목입니다. 유사 일치는 TRUE, 정확한 일치는 FALSE를 입력합니다. 여기서는 정확한 일치를 선택했으며, 최종 수식은
=VLOOKUP(E12,A1:C5,3,FALSE)가 됩니다.
- 결과를 확인해 보면 VLOOKUP이 성공적으로 "Iris Watson"의 전화번호를 불러온 것을 볼 수 있습니다.
참고: 이 가이드는 마이크로소프트 엑셀을 기준으로 VLOOKUP을 실행하지만, 동일한 방법을 구글 스프레드시트에서도 그대로 사용할 수 있습니다.
여러 조건으로 데이터 필터링하기
VLOOKUP은 기본적으로 하나의 값만 불러오도록 설계되었고, 여러 정보를 조회하는 데에는 다른 함수들이 존재합니다. 하지만 그렇다고 VLOOKUP으로 여러 조건을 검색하는 것이 불가능한 것은 아닙니다. 도우미 열(helper column)을 활용하면 여러 셀의 정보를 결합한 고유 식별자를 만들 수 있고, VLOOKUP을 조금 수정해 이 고유 식별자를 검색하도록 만들 수 있습니다.
이 방법은 여러 셀에 동일한 값이 존재할 때 특히 유용합니다. 예를 들어 표에 "Iris Watson"이라는 이름이 여러 개 있다고 가정해 보겠습니다. 평소라면 VLOOKUP은 목록에서 처음 발견한 "Iris Watson"만 불러오지만, 우리가 찾으려는 것은 다른 사람일 수 있습니다.
도우미 열을 사용하면 고유 식별자를 통해 시트 속 서로 다른 "Iris Watson"들을 구분할 수 있습니다.
이번 예시에서는 전화번호 대신 주소를 표시하도록 VLOOKUP을 사용합니다. 또한 주소와 전화번호가 다른 두 번째 "Iris Watson"을 시트에 추가했습니다.
- 가장 먼저 '이름'과 '전화번호' 셀을 결합해 고유 식별자를 만드는 도우미 열을 생성했습니다. 여기에는 서로 다른 셀의 문자열을 단순히 합쳐 주는 CONCATENATE 함수를 사용했습니다. 수식은 다음과 같은 형태입니다.
=CONCATENATE(B2," | ",D2)이름과 전화번호 사이에 파이프("|") 기호와 공백을 넣어 가독성을 높였습니다. 이 함수에 대해 더 알고 싶다면 CONCATENATE 사용법 관련 글을 참고하세요.
- CONCATENATE가 정상적으로 적용되었다면, 해당 셀의 오른쪽 아래 모서리를 클릭한 뒤 도우미 열의 나머지 셀들까지 드래그합니다. 그러면 같은 결합식이 모든 셀에 적용됩니다.
- VLOOKUP에는 두 개의 검색 필드가 필요합니다. '이름'과 '전화번호' 검색 필드를 추가하고, VLOOKUP 결과를 표시할 '주소' 필드도 함께 만들었습니다.
- 이제 VLOOKUP이 '이름'과 '전화번호' 검색 필드에 입력된 정보를 결합하되, 도우미 열에서 사용한 것과 동일한 형식이 되도록 만들어야 합니다. 그래야 VLOOKUP이 도우미 열의 고유 식별자를 찾아 해당 주소를 불러올 수 있습니다.
처음 작성한 수식은 다음과 같습니다. =VLOOKUP(F9&" | "&F10,
F9와 F10은 각각 '이름'과 '전화번호' 검색 필드이며, & 기호는 CONCATENATE처럼 두 필드를 하나로 연결하는 역할을 합니다. 수식 중간의 " | "는 도우미 열에서 결합할 때 사용한 것과 동일한 구분 기호입니다.
- 이후에는 VLOOKUP 수식의 나머지 부분만 완성하면 됩니다. 표 범위를 선택하고, 열 색인 번호를 입력한 뒤, 정확한 일치(EXACT)로 설정합니다. 최종 수식은 다음과 같습니다.
=VLOOKUP(F9&" | "&F10,A1:D6,3,FALSE)
- 검색 필드에 이름이나 전화번호 중 하나만 입력해서는 결과가 나오지 않는다는 점을 확인할 수 있습니다. 조회가 성공하려면 두 필드를 모두 채워야 합니다. 아래에서는 두 명의 서로 다른 "Iris Watson"이 있기 때문에 두 가지 VLOOKUP 결과를 확인할 수 있으며, 두 번째 검색 필드에 입력하는 전화번호에 따라 결과가 달라집니다.
VLOOKUP이 INDEX-MATCH보다 나을까?
이 질문은 마이크로소프트 엑셀 초기부터 지금까지 끊임없이 논쟁이 되어 온 주제입니다. 답을 내리기에 앞서 INDEX-MATCH가 무엇인지 이해할 필요가 있습니다. INDEX와 MATCH는 각각 독립된 함수로, 두 가지를 조합하면 VLOOKUP보다 훨씬 유연한 조회 시스템을 만들 수 있습니다.
MATCH 함수는 특정 값이 셀 범위 내에서 몇 번째 위치에 있는지 그 순번을 찾을 때 사용합니다. 반면 INDEX 함수는 두 가지 형식 중 하나를 사용해 표 배열이나 셀 범위에서 값을 표시합니다. 두 함수를 조합하면 MATCH로 정보의 위치 번호를 파악한 뒤, INDEX가 그 위치 번호를 이용해 값을 반환하도록 만들 수 있습니다.
어느 쪽이 더 나은지는 결국 사용자에 따라 달라집니다. VLOOKUP은 설정과 활용이 훨씬 쉽기 때문에 초보자나 중급 수준의 엑셀·구글 스프레드시트 사용자에게 훨씬 친숙합니다. 반면 INDEX-MATCH는 훨씬 더 유연해서 다양한 상황에 폭넓게 적용할 수 있습니다.
결국 많은 사람이 INDEX-MATCH보다 VLOOKUP 사용법을 알고 있으므로, 시트를 고급 사용자가 아닌 여러 사람이 함께 사용할 경우라면 VLOOKUP이 더 나은 선택일 가능성이 높습니다. 반대로 시트가 전문가용이라면 INDEX-MATCH를 사용하는 편이 좋습니다.
VLOOKUP 사용 시 알아두어야 할 주의사항
VLOOKUP을 처음 배울 때 몇 가지 실수를 하는 것은 전혀 부끄러운 일이 아닙니다. 대부분의 엑셀 고수들도 한때는 같은 과정을 거쳤습니다. VLOOKUP을 시도할 때 다음 사항들을 기억하세요.
1. 반드시 Lookup_Value가 표의 첫 번째 열에 있도록 하세요.
VLOOKUP은 lookup_value가 표 배열의 첫 번째 열에 있다고 가정하고 작동합니다. 해당 값이 같은 행의 이후 셀에 있을 때만 정보를 표시할 수 있습니다. lookup_value를 시작 위치가 아닌 곳에 두면 함수가 제대로 작동하지 않습니다.
2. 정확한 일치에는 FALSE를 잊지 마세요.
VLOOKUP 수식의 마지막 부분에서 정확한 일치는 FALSE, 부분 일치는 TRUE로 지정합니다. 많은 사용자가 TRUE를 잘못 사용해 부정확한 결과를 얻거나, 아예 값을 설정하는 것을 잊곤 합니다.
3. 반드시 열 색인 번호를 다시 확인하세요.
VLOOKUP이 무엇을 표시하는지는 수식을 입력할 때 올바른 'col_index_num'을 설정했는지에 크게 좌우됩니다. 'col_index_num', 즉 열 색인 번호는 표 배열의 각 열에 부여된 숫자로, 첫 번째 열은 1, 두 번째 열은 2, 이런 식으로 매겨집니다. 잘못된 'col_index_num' 값을 입력하면 함수가 전혀 다른 결과를 보여줍니다.
4. 수식을 다른 셀로 복사할 때는 F4를 활용하세요.
VLOOKUP의 가장 유용한 점 중 하나는 수식을 드래그해 여러 셀에 복사할 수 있다는 것입니다. 문제는 수식 문자열에 지정된 값들도 함께 아래로 밀려나면서 수식 전체가 망가진다는 점입니다. 이를 방지하려면 커서를 수식의 값 위에 놓고 F4 키를 누르세요. 그러면 해당 값들이 절대 참조로 변환되어 수식을 복사해도 이동하지 않습니다.
자주 발생하는 VLOOKUP 오류와 해결 방법
VLOOKUP을 사용할 때 가장 흔하게 마주치는 오류는 '#NA' 오류입니다. 다만 이 오류가 나타나는 원인은 매우 다양하다는 점에 유의해야 합니다.
1. Lookup_value가 표 배열의 첫 번째 열에 없는 경우
VLOOKUP의 가장 큰 한계 중 하나는 표 배열의 맨 첫 번째 열에서만 값을 검색할 수 있다는 것입니다. lookup_value가 그곳에 없으면 #NA 오류가 발생합니다. 이를 해결하려면 수식이 다른 열을 참조하도록 수정하거나, 열의 순서를 조정해 lookup_value를 올바른 위치로 옮겨야 합니다.
2. VLOOKUP이 정확한 일치를 찾지 못하는 경우
VLOOKUP 수식의 마지막 값은 range_lookup 인수로, 유사 일치는 TRUE, 정확한 일치는 FALSE로 설정합니다. 이 인수를 FALSE로 지정했는데 VLOOKUP이 정확한 일치를 찾지 못하면 #NA 오류가 발생합니다.
lookup_value에 분명 일치하는 값이 있어야 한다면, 표 배열의 데이터를 다시 확인해 모든 정보가 올바르게 입력되었는지, 불필요한 공백이 없는지 점검하세요. 눈에 보이지 않는 제어 문자 역시 VLOOKUP이 항목을 제대로 찾지 못하게 만드는 원인이 될 수 있습니다.
3. 소수점 자릿수가 지나치게 많은 부동 소수점 숫자
부동 소수점 숫자는 쉽게 말해 소수점을 가진 숫자입니다. VLOOKUP에서 소수점 뒤 자릿수가 너무 많은 수치가 있으면 #NA 오류가 발생할 수 있습니다. 해결 방법은 의외로 간단합니다. 소수점 이하 최대 다섯 자리까지만 반올림하면 정상적으로 작동합니다. 이때 ROUND 함수를 활용하면 됩니다.
자주 묻는 질문
1. range_lookup 인수를 비워두면 어떻게 되나요?
VLOOKUP 수식의 네 번째 인수는 'TRUE' 또는 'FALSE'로 설정해야 하지만, 선택 사항으로 간주됩니다. 'TRUE'로 설정하면 VLOOKUP이 유사 일치를 검색하고, 'FALSE'는 정확한 일치를 요구합니다. 문제는 이 인수를 비워두면 VLOOKUP이 자동으로 'TRUE'로 처리한다는 점입니다. 이로 인해 원하는 결과와 다른 값이 나올 수 있으니 주의하세요.
2. VLOOKUP 대신 사용할 수 있는 대안은 무엇인가요?
VLOOKUP의 가장 좋은 대안은 INDEX-MATCH 조합입니다. 다만 이 조합은 두 개의 서로 다른 함수를 모두 익혀야 하고, 두 함수가 서로 올바르게 작동하도록 만들어야 하므로 학습 난이도가 다소 높다는 점을 감안해야 합니다.
3. 열 대신 행으로 값을 검색할 수 있나요?
네, 가능합니다. 엑셀과 구글 스프레드시트에서 HLOOKUP, 즉 '가로 방향 조회(horizontal lookup)'를 사용하면 됩니다. 이 함수는 특정 행에서 값을 찾은 뒤, 같은 열에 있는 다른 행의 값을 표시합니다.
검색 범위를 하나의 행이나 열로 한정하고 싶다면 LOOKUP 함수를 사용할 수도 있습니다.