엑셀 vlookup 함수 사용법 부품표로 익히는 방법
얼마 전 자동차 부품 재고표를 보는데, 부품 번호는 알지만 가격을 찾으려면 긴 목록을 계속 내려야 했습니다. 이런 표에서 번호 하나로 필요한 값을 가져올 때 쓰는 함수가 VLOOKUP입니다. 처음에는 수식의 숫자와 기호가 복잡해 보이지만, 실제로는 ‘어디에서 찾고, 몇 번째 열의 값을 가져올지’를 지정하는 방식입니다.
부품 번호로 가격을 찾는 수식
부품 목록의 A열에 부품 번호, B열에 부품명, C열에 단가가 있다고 해볼게요. 데이터는 2행부터 100행까지 있고, E2 셀에 찾을 부품 번호를 입력합니다. F2 셀에 =VLOOKUP(E2,$A$2:$C$100,3,FALSE)를 넣으면 해당 번호의 단가가 표시됩니다. 예를 들어 E2에 입력한 번호가 A17에 있다면, 수식은 같은 행의 C17 값을 가져옵니다.
수식의 네 부분을 차례로 읽으면 이해하기 쉽습니다. E2는 찾을 값, $A$2:$C$100은 검색할 표, 3은 표 범위 안에서 값을 가져올 열 번호입니다. A열부터 세면 A가 1, B가 2, C가 3이죠. 마지막 FALSE는 부품 번호와 정확히 일치하는 행을 찾으라는 뜻입니다. 부품 번호나 고객 번호처럼 한 글자만 달라도 다른 항목이 되는 데이터에는 보통 FALSE를 사용합니다.
표 범위와 열 번호에서 자주 틀리는 부분
VLOOKUP은 지정한 표 범위의 맨 왼쪽 열에서 검색합니다. 위 예시에서 부품 번호가 A열에 있으니 범위를 A열부터 잡은 겁니다. 만약 범위를 B2:C100으로 지정하면 E2의 번호를 B열의 부품명에서 찾게 됩니다. 표 전체를 선택했는지가 아니라, 찾을 값이 선택한 범위의 첫 열에 있는지가 중요합니다.
열 번호도 엑셀 시트 전체의 열 번호가 아닙니다. 예를 들어 검색 범위를 B2:D100으로 잡았다면 B열이 1, C열이 2, D열이 3입니다. 범위가 B열부터 시작하는데 D열 값을 가져온다며 4를 입력하면 오류가 납니다. 검색 범위를 먼저 확인하고, 그 안에서 왼쪽부터 열을 세면 헷갈릴 일이 줄어듭니다.
범위에 붙은 달러 기호도 눈여겨볼 만합니다. E2는 수식을 아래로 복사하면 E3, E4로 바뀌어야 여러 부품 번호를 차례로 조회할 수 있습니다. 반면 부품 목록의 위치는 고정돼야 하죠. $A$2:$C$100처럼 절대 참조를 쓰면 수식을 아래로 내려도 검색 범위가 밀리지 않습니다. 달러 기호를 빼고 복사하면 다음 행에서 범위가 A3:C101로 이동해 첫 번째 부품이 검색 대상에서 빠질 수 있습니다.
값이 있는데도 검색되지 않을 때
가장 흔한 결과는 #N/A입니다. 찾을 번호가 표에 없을 수도 있지만, 화면에서 똑같아 보이는 두 값의 저장 방식이 다른 경우도 많습니다. 한쪽은 숫자 123이고 다른 쪽은 문자로 저장된 ‘123’이면 일치하지 않을 수 있습니다. 특히 ‘00123’처럼 앞의 0이 의미 있는 부품 번호를 숫자로 바꾸면 0이 사라집니다. 이런 번호는 입력 단계부터 두 표에서 모두 문자 형식으로 관리하는 편이 안전합니다.
앞뒤 공백도 확인해 보세요. 외부 시스템에서 복사한 번호 뒤에 공백이 붙어 있으면 눈으로는 찾기 어렵습니다. 원본 데이터를 다듬을 수 있다면 불필요한 공백을 제거한 뒤 다시 조회하는 게 좋습니다. 반대로 #REF!가 보인다면 지정한 범위보다 큰 열 번호를 입력했는지 확인하세요. A2:C100은 세 열짜리 범위이므로 열 번호 4로는 값을 가져올 수 없습니다.
목록에 같은 부품 번호가 두 번 들어 있으면 VLOOKUP은 먼저 만난 행의 값을 반환합니다. 단가가 다른 중복 행이 있다면 수식이 정상 작동해도 원하는 가격과 다를 수 있죠. 검색 결과가 이상할 때는 수식만 고치기보다 원본 목록에서 번호의 중복 여부도 함께 확인해야 합니다.
정확히 일치와 비슷한 값 찾기의 차이
마지막 인수를 생략하거나 TRUE로 두면 정확히 같은 값이 없어도 가까운 값을 찾는 방식으로 작동합니다. 이 방식은 구간별 요금표처럼 별도 규칙에 따라 표를 구성한 경우에 쓸 수 있지만, 검색 열을 오름차순으로 배열해야 예상한 결과를 얻을 수 있습니다. 부품 번호 조회에서 이 설정을 실수로 쓰면 존재하지 않는 번호에 다른 부품의 가격이 표시될 수 있어 특히 조심해야 합니다.
검색 실패 화면을 깔끔하게 만들고 싶다면 =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"번호 확인")처럼 쓸 수 있습니다. 다만 오류 메시지를 바꾸기 전에 왜 오류가 났는지는 먼저 확인하는 게 좋습니다. 없는 번호, 형식 불일치, 잘못된 열 번호가 모두 같은 ‘번호 확인’으로 표시되면 원인을 놓치기 쉽거든요.
정비 견적서처럼 부품 번호를 입력할 때마다 이름과 단가를 불러오는 표라면, 처음 한 줄을 정확하게 만든 뒤 필요한 행까지 수식을 복사하면 됩니다. 저는 이런 표를 받을 때 수식부터 넣기보다 번호 열에 중복과 형식 차이가 없는지 먼저 봅니다. 원본이 가지런하면 VLOOKUP은 단순한 조회 함수지만, 원본이 어지러우면 그 차이가 결과에 그대로 드러납니다.
