엑셀 VLOOKUP 함수는 참조 범위 내에서 가장 왼쪽에 있는 열을 기준으로, 같은 행에 위치한 다른 열의 값을 찾아주는 함수입니다. 이 글에서는 기초 수식 원리부터 실전 예제, 그리고 자주 마주치는 #N/A 같은 오류 해결법까지 한 번에 정리했습니다.
VLOOKUP 원리와 수식
브이룩업 함수는 지정된 영역을 참조해 원하는 결과값을 찾아주는 함수로, 아래와 같은 원리로 움직입니다.
=VLOOKUP(①, ②, ③, ④)
- ① 기준 셀
- ② 참조 범위
- ③ 호출할 열 번호
- ④ 일치 옵션(유사도)
글로 풀어보면, 먼저 검색할 값이 입력된 셀을 지정한 후 참조할 전체 범위를 잡고, 해당 범위에서 출력할 열의 위치를 번호로 입력한 다음, 찾을 데이터의 일치 옵션을 결정해주면 됩니다.
다만 이렇게만 보면 여전히 어렵게 느껴지실 수 있습니다. 옵션이 안 맞을 수도 있고, #N/A, #REF, #NAME 같은 오류가 뜨면 당황스럽기도 하죠. 이해를 돕기 위해 예시 데이터로 직접 적용해보겠습니다.
브이룩업 함수 실전 예제
엑셀 프로그램에서 아래와 같은 예시 문서를 가져왔습니다. 망고가게 안에서 품목(이름)에 따른 종류(과일 또는 채소)를 도출해보려고 합니다.
여기서 표를 작성할 때 중요한 건, 기준이 될 찾을 값이 들어가는 열이 참조할 전체 범위에서 가장 왼쪽에 위치해야 한다는 점입니다.
실제로 저는 처음 문서를 작성할 땐 기준이 될 [품목]을 B열에 놓고 함수를 진행했었는데, 완료하고 나니 오류 값이 나오더라고요. 원인을 찾다가 참조 범위 안 [품목]을 A열로 바꾸면서 오류를 해결했습니다.
함수식 시작
G열에 찾을 값을 입력해놓고 H열에서 브이룩업 함수를 진행해보겠습니다. 먼저 결과가 나오길 원하는 셀을 활성화한 후 =vlookup(을 입력해줍니다. 괄호 열기까지 진행하면 마우스 커서만으로 수식 입력이 가능해집니다.
① 기준 셀
수식의 첫 번째는 검색의 기준이 될 단어입니다. 따로 작성한 일치하는 명칭이 있는 셀을 클릭해 위치를 정해준 후 쉼표(,)를 입력해 마무리합니다. 여기서는 [품목]이겠죠? 저는 사과가 적힌 셀을 지정해줬습니다.
② 참조 범위
두 번째는 검색할 영역인 참고할 테이블 전체 영역을 드래그를 통해 잡아줍니다. A3부터 D14까지 전체 데이터를 잡았기에 A3:D14가 입력됐습니다.
해당 영역은 수식을 복사 붙여넣기 하더라도 변하지 않는 고정된 영역이기에, 대체로 절대값으로 지정해줍니다. 절대값은 단축키 F4를 누르거나 $을 직접 기재해 설정할 수 있습니다. 여기까지 마무리됐다면 다시 쉼표(,)를 넣어 두 번째 항목을 마무리해줍니다.
③ 호출할 열 번호
세 번째는 참조 범위에서 일치하는 데이터가 있는 열의 위치를 번호로 넣어주는 단계입니다. 번호 순서는 가장 좌측이 1번이며, 저는 [종류]를 불러올 것이므로 [2]를 입력한 후 쉼표를 찍어 마무리했습니다. 만약 종류가 아닌 수량을 가져오고 싶었다면 숫자 [4]를 넣으면 됩니다.
④ 유사도
마지막 네 번째는 찾을 값의 일치 유사도를 판단하는 항목입니다. TRUE(유사 일치)와 FALSE(정확히 일치)가 있으며, 유사는 숫자 [1]을, 정확히 일치는 숫자 [0]을 넣어 설정할 수 있습니다.
유사 일치(1)의 경우 찾는 값보다 작거나 같은 값 중 최댓값을 조회하기 때문에, 데이터를 작성할 때 첫 번째 열은 반드시 오름차순으로 정렬되어 있어야 하는 번거로움이 있습니다. 그렇기에 실제로 [FALSE] 또는 [0]을 넣어 정확한 결과값을 도출하는 것이 일반적입니다. 저 역시 정확한 글자를 찾아 가져오기 위해 [0]을 기재 후 괄호를 닫았습니다.
엑셀 VLOOKUP 수식이 완성됐습니다. 이제 엔터를 쳐볼까요?
차례대로 아래까지 채워보겠습니다. 앞서 두 번째 영역인 참조 테이블은 절대값으로 고정해줬는데요. 수식을 그대로 아래로 끌고 와 복사하는 경우 절대값을 설정하면 그 범위는 변하지 않고 다른 셀 값만 변하기 때문에, VLOOKUP 함수를 활용할 땐 ②번만은 절대참조($)로 해주는 것이 좋습니다.
VLOOKUP 자주 나는 오류와 해결법
VLOOKUP을 쓰다 보면 아래 세 가지 오류를 특히 자주 만나게 됩니다.
- #N/A 오류: 찾을 값이 참조 범위 안에 없거나, 찾을 값과 참조 범위 첫 열의 데이터 형식(텍스트/숫자)이 다를 때 발생합니다. 셀 서식을 통일하거나 오타를 확인하면 대부분 해결됩니다.
- #REF! 오류: 참조 범위에서 열이나 행을 삭제해 수식이 가리키던 위치가 사라졌을 때 발생합니다. 열 번호를 다시 확인하고 수식을 재입력해야 합니다.
- #NAME? 오류: 함수 이름을 잘못 입력했거나, 따옴표 같은 문법 요소가 빠졌을 때 발생합니다.
=VLOOKUP(처럼 함수명 철자와 괄호를 다시 확인해보세요.
열 번호를 직접 세지 않는 법
앞서 ③ 단계에서 호출할 열 번호를 직접 세서 입력했는데, 표에 열이 추가되거나 순서가 바뀌면 이 번호도 다시 세야 하는 번거로움이 있습니다. 이럴 때 MATCH 함수를 조합하면 열 번호를 자동으로 찾아주기 때문에 훨씬 안정적인 수식을 만들 수 있습니다.
예를 들어 아래와 같은 데이터에서 “사과”를 찾아 “가격”을 출력하려면 다음과 같이 작성합니다.
=VLOOKUP("사과", A1:D11, MATCH("가격", A1:D1, 0), 0)이렇게 하면 “가격” 열이 몇 번째에 있든 상관없이 자동으로 해당 열 번호를 찾아 출력해주기 때문에, 표 구조가 자주 바뀌는 업무에서 특히 유용합니다.
HLOOKUP과의 차이, 그리고 XLOOKUP
지금까지 알아본 함수는 일반적으로 많이 쓰이는 세로 방향 자료에서 쓰이는 VLOOKUP입니다. 만약 데이터가 가로로 입력되어 있다면 VLOOKUP이 아닌 HLOOKUP 함수를 적용해야 합니다. 원리는 동일하지만 열 대신 행을 기준으로 검색한다는 점만 다릅니다.
참고로 최근 엑셀 버전(엑셀 2021, Microsoft 365)에서는 VLOOKUP의 개선판인 XLOOKUP 함수도 사용할 수 있습니다. XLOOKUP은 왼쪽/오른쪽 어느 방향으로든 검색이 가능하고, 기본값으로 정확한 일치를 반환해 오류 걱정이 덜한 것이 장점입니다. 다만 구버전 엑셀에서는 지원되지 않으니, 파일을 여러 사람과 공유해야 한다면 여전히 VLOOKUP이 호환성 면에서 더 안전한 선택입니다.
자주 묻는 질문(FAQ)
Q1. VLOOKUP과 XLOOKUP 중 뭘 써야 하나요?
최신 버전의 엑셀만 쓴다면 XLOOKUP이 더 편리하지만, 구버전 엑셀 사용자와 파일을 공유해야 한다면 호환성이 좋은 VLOOKUP을 사용하는 것이 안전합니다.
Q2. #N/A 오류는 왜 뜨나요?
찾을 값이 참조 범위 안에 실제로 없거나, 찾을 값과 참조 범위의 데이터 형식(텍스트/숫자)이 서로 다를 때 주로 발생합니다.
Q3. 다른 시트에 있는 데이터도 참조할 수 있나요?
네, 참조 범위를 지정할 때 ‘시트이름!범위’ 형태로 입력하면 다른 시트의 데이터도 그대로 참조할 수 있습니다.
Q4. 열 순서를 바꾸면 수식이 깨지나요?
번호를 직접 입력한 경우 열 순서가 바뀌면 결과가 달라지거나 오류가 날 수 있습니다. 이런 문제를 피하려면 앞서 소개한 MATCH 함수와 조합하는 방법을 추천드립니다.