엑셀 vlookup 쓰는방법 견적표에서 값 찾기
조립PC 견적표를 만들다 보면 같은 부품 가격을 여러 번 입력하게 됩니다. SSD 모델명 하나를 바꿨는데 주문표의 가격은 예전 값 그대로인 경우도 있었고요. 이런 반복 입력을 줄일 때 엑셀 VLOOKUP이 꽤 쓸 만합니다. 상품 코드를 기준으로 다른 표에서 가격을 찾아오는 방식이라, 한 번 구조를 잡아두면 행이 늘어나도 수식을 복사해서 쓸 수 있습니다.
상품 코드로 가격을 가져오는 기본 수식
예를 들어 주문표 A2셀에 SSD 상품 코드가 있고, ‘상품목록’ 시트의 A열에는 상품 코드, B열에는 제품명, C열에는 가격이 있다고 해보죠. 주문표에서 가격을 표시할 셀에 아래 수식을 넣습니다.
=VLOOKUP(A2,상품목록!$A$2:$C$100,3,FALSE)
뜻은 순서대로 읽으면 쉽습니다. A2의 코드를 상품목록 A2:C100 범위의 맨 왼쪽 열에서 찾고, 일치하는 행의 세 번째 열 값을 가져옵니다. 여기서 세 번째 열은 시트 전체의 C열을 뜻하는 게 아닙니다. 선택한 범위 A:C 안에서 세 번째라는 뜻입니다. 검색 범위를 B:D로 바꾸면 세 번째 열은 D열이 됩니다.
마지막의 FALSE는 정확히 같은 코드만 찾겠다는 뜻입니다. 견적표처럼 상품 코드, 부품 번호, 고객 번호를 대조할 때는 보통 FALSE가 맞습니다. 이 자리를 비워두면 근삿값 검색이 적용될 수 있어, 찾는 코드가 없는데도 엉뚱한 행의 값이 나오는 문제가 생깁니다.
수식을 아래로 복사할 때 범위를 고정하는 이유
위 수식에서 A2에는 달러 표시가 없고 상품목록의 범위에는 달러 표시가 있습니다. 수식을 한 행 아래로 복사하면 찾을 코드는 A2에서 A3으로 바뀌어야 합니다. 반면 상품목록의 위치는 계속 A2:C100이어야 하죠. 그래서 검색 범위를 $A$2:$C$100처럼 고정합니다.
달러 표시 없이 A2:C100만 넣고 수식을 아래로 끌면 다음 행에서는 A3:C101, 그다음 행에서는 A4:C102를 검색합니다. 처음 몇 줄은 멀쩡해 보여도 표 윗부분에 있는 상품이 검색 범위에서 빠지면서 갑자기 #N/A가 뜰 수 있습니다. 견적 항목이 많을수록 발견이 늦어지는 오류입니다.
실제로 작업할 때는 첫 행에서 코드 하나와 가격 하나를 눈으로 대조한 뒤 수식을 복사합니다. 가격 열의 서식도 함께 확인합니다. VLOOKUP은 원본 값을 가져오지만, 결과 셀에 원화 표시나 천 단위 구분 기호가 자동으로 맞춰지는 것은 아니기 때문입니다.
#N/A가 뜨거나 다른 값이 나오면
#N/A는 대부분 찾는 값을 검색 범위의 첫 번째 열에서 발견하지 못했다는 뜻입니다. 먼저 주문표의 코드가 상품목록에 실제로 있는지 확인하세요. 그다음 검색 범위가 코드 열부터 시작하는지 봅니다. 상품 코드가 상품목록 B열에 있는데 A:C를 범위로 지정했다면, VLOOKUP은 A열에서 코드를 찾으려 합니다.
겉으로 똑같아 보이는 코드도 저장된 형태가 다르면 일치하지 않을 수 있습니다. 한쪽은 숫자 10024이고 다른 쪽은 문자로 저장된 10024인 경우가 대표적입니다. 복사해 온 자료라면 앞뒤 공백도 확인할 만합니다. 표시 형식만 바꾸는 것으로 해결되지 않을 수 있으니, 양쪽 코드 열의 실제 데이터 형식을 맞추는 편이 확실합니다.
값은 나오는데 가격이 틀리다면 세 번째 인수인 열 번호를 확인합니다. 예를 들어 A열이 코드, B열이 제품명, C열이 공급가, D열이 판매가인데 검색 범위를 A:D로 잡고 3을 넣으면 공급가를 가져옵니다. 판매가가 필요하면 4를 넣어야 합니다. 같은 코드가 상품목록에 여러 번 들어 있다면 VLOOKUP은 먼저 만난 행의 값을 반환하므로 중복 코드도 살펴봐야 합니다.
VLOOKUP이 맞지 않는 표도 있다
VLOOKUP은 지정한 범위의 맨 왼쪽 열에서 값을 찾고 오른쪽 열의 값을 가져옵니다. 가격으로 상품 코드를 역으로 찾는 식의 작업에는 표의 열 순서를 바꾸거나 다른 함수를 써야 합니다. 데이터가 계속 추가된다면 검색 범위를 넉넉하게 잡는 방법도 있지만, 원본을 엑셀 표로 만들어 범위가 늘어나도록 관리하는 편이 실수할 여지가 적습니다.
상품 코드가 없을 때 화면을 깔끔하게 보이려고 IFERROR로 #N/A를 숨기기도 합니다. 다만 견적 금액을 확인하는 단계라면 오류를 바로 가리지 않는 쪽을 권합니다. 누락된 부품 코드와 잘못 입력한 코드가 보이지 않으면 합계만 그럴듯하게 남을 수 있으니까요.
제가 견적표에서 가장 먼저 확인하는 건 수식의 길이가 아니라 코드 열의 상태입니다. 코드가 한 형식으로 관리되고 검색 범위가 고정돼 있으면 VLOOKUP 수식 자체는 단순합니다. 처음 몇 행의 코드와 가격을 직접 대조하는 습관이, 나중에 수십 행을 다시 확인하는 시간을 줄여줍니다.
