엑셀 vlookup 함수로 표에서 값 찾는 방법
얼마 전 상품 코드별 단가를 붙여야 하는 엑셀 파일을 받았는데, 주문 내역이 300줄이 넘었습니다. 코드를 하나씩 찾아 복사할 수도 있지만, 이런 일은 엑셀 vlookup 함수가 훨씬 빠릅니다. 처음 쓸 때 헷갈리는 부분은 수식 자체보다 ‘어느 열에서 찾고, 어느 열의 값을 가져올지’ 정하는 순서였습니다.
먼저 찾을 값과 원본 표의 위치를 확인하기
예를 들어 원본 표의 A열에는 상품 코드, B열에는 상품명, C열에는 단가가 있다고 해볼게요. 주문 내역의 E2 셀에 상품 코드가 있고, 그 코드에 맞는 단가를 F2 셀에 채우려는 상황입니다. 원본 표는 A2부터 C101까지입니다.
이때 가장 먼저 확인할 것은 원본 표의 맨 왼쪽 열입니다. VLOOKUP은 선택한 범위의 첫 번째 열에서만 값을 찾습니다. 상품 코드를 기준으로 찾는다면 범위를 A열부터 잡아야 합니다. B열부터 선택하면 상품명에서 코드를 찾게 되어 원하는 결과가 나오지 않습니다.
엑셀 vlookup 함수 수식 입력하는 방법
F2 셀에 =VLOOKUP(E2,$A$2:$C$101,3,FALSE)를 입력합니다. E2는 찾을 상품 코드, $A$2:$C$101은 원본 표, 3은 그 범위에서 가져올 값이 세 번째 열인 단가라는 뜻입니다. 마지막 FALSE는 상품 코드가 정확히 일치하는 행만 찾도록 지정합니다.
수식이 맞으면 F2의 오른쪽 아래 모서리를 아래로 끌어 나머지 주문 내역에도 적용하면 됩니다. 이때 원본 범위에 붙인 $ 기호가 중요합니다. E2는 아래 행으로 내려갈 때 E3, E4로 바뀌어야 하지만, 원본 표의 위치는 고정되어야 하니까요. $를 빼고 수식을 복사하면 검색 범위가 한 줄씩 밀려 일부 행에서 엉뚱한 값이 나올 수 있습니다.
열 번호 3은 시트 전체의 C열 번호를 뜻하는 게 아닙니다. 선택한 범위의 왼쪽부터 세는 번호입니다. 범위를 B2:D101로 잡았다면 B열이 1, C열이 2, D열이 3이 됩니다. 값을 가져올 열이 바뀌면 이 숫자도 함께 확인해야 합니다.
값이 안 나올 때 확인할 세 가지
가장 흔한 오류는 #N/A입니다. 먼저 E2의 코드가 원본 A열에 실제로 있는지 확인합니다. 눈에는 같아 보여도 한쪽에는 ‘0012’, 다른 쪽에는 ‘12’가 들어 있다면 서로 다른 코드일 수 있습니다. 코드 앞의 0이 의미를 가진다면 두 표 모두 문자 형식으로 맞추는 편이 안전합니다.
두 번째는 공백입니다. 복사해 온 상품 코드 끝에 빈칸이 붙어 있으면 일치하지 않을 수 있습니다. 입력값의 앞뒤 공백이 의심되면 =VLOOKUP(TRIM(E2),$A$2:$C$101,3,FALSE)처럼 확인할 수 있습니다. 다만 이 방법은 E2의 공백만 제거합니다. 원본 A열에도 공백이 섞여 있다면 원본 데이터도 함께 손봐야 합니다.
세 번째는 범위와 열 번호입니다. 단가가 C열인데 원본 범위를 A:B까지만 잡았다면 세 번째 열을 가져올 수 없어 #REF! 오류가 납니다. 원본 표에 상품 코드가 중복되어 있다면 VLOOKUP은 처음 발견한 행의 값을 가져옵니다. 같은 코드에 서로 다른 단가가 있는 표라면 수식을 고치기 전에 중복 행부터 확인하는 게 맞습니다.
정확히 찾기와 오류 표시를 실무에 맞게 쓰기
상품 코드나 사번처럼 하나의 값을 정확히 대응시킬 때는 마지막 인수에 FALSE를 넣는 습관이 좋습니다. 이 자리를 비우거나 TRUE로 지정하면 근삿값을 찾습니다. 근삿값 검색은 점수 구간별 등급처럼 기준값에 따라 결과를 나눌 때 쓸 수 있지만, 검색 범위의 첫 열이 오름차순으로 정렬되어 있어야 합니다. 코드 조회에 무심코 적용하면 그럴듯한 오답이 나올 수 있습니다.
조회 실패를 빈칸 대신 안내 문구로 표시하고 싶다면 =IFERROR(VLOOKUP(E2,$A$2:$C$101,3,FALSE),"코드 확인")을 쓸 수 있습니다. 다만 오류를 가렸다고 원인이 해결된 것은 아닙니다. 처음에는 원래 수식으로 누락 코드와 범위 문제를 점검하고, 표를 공유할 단계에서 안내 문구를 붙이는 편이 실수를 찾기 쉽습니다.
몇 줄만 처리할 때는 직접 찾는 편이 빨라 보이기도 합니다. 그런데 파일이 커지고 원본 단가가 바뀌면 수식의 장점이 분명해집니다. 찾을 코드가 원본 범위의 첫 열에 있는지, 가져올 열 번호가 맞는지, 마지막 인수가 FALSE인지 세 군데만 먼저 보면 대부분의 작업은 한 번에 풀립니다.
