엑셀 vlookup 사용법 자동차 부품표로 익히는 방법
얼마 전 자동차 부품 가격표에서 품번 하나를 찾느라 수십 행을 훑는 모습을 봤습니다. 품번을 입력하면 부품명과 가격이 바로 나오도록 만들면 훨씬 편하죠. 엑셀 vlookup 사용법은 이처럼 한 열에서 값을 찾고, 같은 행의 다른 열에 있는 정보를 가져오는 데서 시작합니다.
부품표를 놓고 수식부터 써보기
예를 들어 A열에는 품번, B열에는 부품명, C열에는 가격이 있다고 하겠습니다. A2에는 AB101, B2에는 에어필터, C2에는 18000을 넣습니다. A3에는 BR205, B3에는 브레이크 패드, C3에는 65000을 넣고, A4에는 OL310, B4에는 엔진오일, C4에는 42000을 넣어보죠.
E2에 찾을 품번인 BR205를 입력하고, F2에 =VLOOKUP(E2,A2:C4,2,FALSE)를 쓰면 ‘브레이크 패드’가 표시됩니다. 가격을 가져오려면 세 번째 인수인 2를 3으로 바꿔 =VLOOKUP(E2,A2:C4,3,FALSE)를 사용하면 됩니다. 같은 품번을 찾되 어느 열의 값을 돌려받을지만 바꾼 셈입니다.
네 가지 인수에서 가장 헷갈리는 부분
VLOOKUP의 기본 형태는 =VLOOKUP(찾을값,표범위,열번호,일치방식)입니다. 첫 번째 인수 E2는 검색할 품번이 들어 있는 셀입니다. 두 번째 인수 A2:C4는 품번과 가져올 정보가 함께 들어 있는 범위입니다.
열번호는 시트 전체의 열 번호가 아니라 지정한 범위 안에서 셉니다. A2:C4라면 A열이 1, B열이 2, C열이 3입니다. 범위를 B2:D4로 잡았다면 B열부터 1로 세야 합니다. 가격을 가져오려는데 부품명이 나온다면 이 숫자를 먼저 확인하는 게 빠릅니다.
마지막 인수 FALSE는 정확히 일치하는 값만 찾겠다는 뜻입니다. 품번, 고객번호, 차량 관리번호처럼 한 글자만 달라도 다른 항목인 데이터에는 보통 FALSE를 씁니다. 이 인수를 생략하면 기본값이 TRUE라서 유사한 값을 찾습니다. TRUE를 쓸 때는 검색 범위의 첫 열이 오름차순으로 정렬되어 있어야 예상한 결과를 얻을 수 있습니다. 부품표에서 정확한 품번을 찾는다면 FALSE를 명시해 두는 편이 안전합니다.
수식을 아래로 복사할 때 범위 고정하기
여러 품번을 한꺼번에 조회할 때는 E2부터 품번을 적고 F2의 수식을 아래로 채웁니다. 이때 =VLOOKUP(E2,A2:C4,2,FALSE)를 그대로 복사하면 다음 행에서 검색 범위가 A3:C5로 밀립니다. 표의 첫 행이 빠져 조회에 실패할 수 있죠.
F2 수식을 =VLOOKUP(E2,$A$2:$C$4,2,FALSE)로 바꾸면 검색 범위는 고정되고, 찾을값 E2만 E3, E4로 바뀝니다. 실제 부품표가 500행까지 있다면 범위를 $A$2:$C$500처럼 잡으면 됩니다. 수식 입력 중 범위 부분을 선택해 F4 키로 절대참조를 적용할 수도 있습니다.
원본 표에서 품번은 지정한 범위의 맨 왼쪽 열에 있어야 합니다. 예를 들어 부품명으로 품번을 찾고 싶은데 품번이 부품명 왼쪽에 있다면, 현재 배열 그대로는 VLOOKUP으로 왼쪽 값을 가져올 수 없습니다. 열 순서를 바꾸거나, 사용 중인 엑셀에서 지원한다면 양방향 조회가 가능한 XLOOKUP을 고려할 만합니다.
오류가 뜨거나 엉뚱한 값이 나올 때
#N/A는 정확히 일치하는 값을 찾지 못했다는 뜻입니다. 우선 입력한 품번이 원본 표에 실제로 있는지 확인하세요. 화면에는 같은 101로 보여도 한쪽은 숫자, 다른 쪽은 문자로 저장돼 있으면 일치하지 않을 수 있습니다. 앞뒤 공백도 확인할 부분입니다. 복사해 온 데이터에 불필요한 공백이 섞였다면 TRIM 함수로 손질할 수 있습니다.
#REF!가 뜬다면 지정한 열번호가 범위의 열 수보다 큰지 보세요. A2:C4는 세 열뿐인데 열번호에 4를 넣으면 가져올 열이 없습니다. 반대로 오류 없이 다른 부품이 표시된다면 검색 범위의 첫 열, FALSE 입력 여부, 중복 품번을 확인해야 합니다. VLOOKUP은 일치하는 항목이 여러 개일 때 처음 만난 행의 값을 가져옵니다.
조회 실패를 빈칸으로 표시해야 하는 보고서라면 =IFNA(VLOOKUP(E2,$A$2:$C$4,2,FALSE),"품번 없음")처럼 적을 수 있습니다. 다만 오류를 감추기 전에 원본 품번과 데이터 형식을 먼저 확인하는 게 좋습니다. 부품 가격표에서는 조용히 비어 있는 칸보다 잘못 입력된 품번을 발견하는 일이 더 중요할 때가 많으니까요.
