[엑셀공부] VLOOKUP 함수 사용방법 ~상품 및 단가를 자동산출 해보자

이번글에서는 실무에서 자주 사용되는

엑셀의 주요기능 중 하나인 VLOOKUP의 사용방법에 대해

알아보도록 하겠습니다.

VLOOKUP 함수는 특정한 값을

정해진 범위의 표에서 해당하는 행을 찾아서

원하는 열의 값을 찾아서 표시해주는 함수입니다.

말이 조금 어렵죠? 예제를 통해서 조금 쉽게 풀어보도록 하겠습니다.

『 VLOOKUP 판매목록 엑셀 설명 』

"판매목록" 시트에는

판매일자, 상품코드, 상품명, 개수, 금액의 정보를 가지고 있는

판매정보가 들어있습니다.

두번째 시트인 "단가표"시트에는

상품코드와 상품코드에 해당하는 상품의 이름, 단가의

정보가 들어있습니다.

그럼 VLOOKUP 함수를 사용해서

판매목록시트의 상품명, 단가가 자동으로

채워지도록 만들어봅시다.

『 VLOOKUP 함수 설명 』

수식 > 함수삽입 메뉴를 클릭하면

엑셀에서 사용하는 함수들의 정보를 볼 수 있습니다.

여기서 VLOOKUP에 대한 정보를 확인해보겠습니다.

VLOOKUP 함수에 필요한 인수는 4개입니다.

Lookup_value

찾으려는 값입니다.

여기서는 판매목록 시트의 상품코드가 됩니다.

Table_array

찾으려는 값이 들어있는 데이터 범위입니다.

여기서는 단가표에 시트가 됩니다.

※ Lookup_value에 해당하는 값은 꼭 Table_array의

첫번째 열에 존재해야합니다.

Col_index_num

찾으려는 값에 매핑되는 컬럼번호입니다.

상품코드가 1인 경우 상품명은 2, 단가는 3이 됩니다.

Range_lookup

true / false 값이 들어갑니다.

값이 없거나 true인 경우 비슷한 값이라도 찾게되고

false경우 정확하게 일치되는 값을 찾습니다.

『 VLOOKUP 함수 적용 』

그럼 실제로 VLOOKUP을 적용해보도록 하겠습니다.

상품코드에 해당하는 상품명을 가지고 오겠습니다.

<Lookup_value 세팅>

먼저 Lookup_value를 세팅해야합니다.

D3셀(상품명)을 선택 한 후 수식 > 함수삽입 버튼을 클릭해서

VLOOKUP 함수를 검색한 후 더블클릭합니다.

Lookup_value의 입력칸을 선택한 상태에서

판매목록 시트의 C3셀(상품코드)를 클릭합니다.

<Table_array 세팅>

Table_array는 위의 그림처럼 입력칸을 선택한 후

단가표 시트로 이동해서 단가표를 선택해줍니다.

선택하면 단가표!B2:D7 값이 정상적으로 세팅되며,

B2사이에 마우스 커서를 이동하고 F4키를 누르면 $B$2같은 형식의

절대주소로 변경됩니다.

※ 절대주소 : 드래그를 통해 자동채우기를 할 때 변경되지 않는 주소

B2와 D7 모두 변경하면 Table_array는 아래와 같이 세팅됩니다.

단가표!$B$2:$D$7

<Col_index_num 세팅>

Col_index는 세팅한 Table_array에서 몇번째의

값을 추출할지를 설정하는 값입니다.

단가표에서 설정한 Table_array에서는

Col_index가 1 : 상품코드, 2 : 상품명, 3 : 단가가 됩니다.

여기서는 상품명이 필요하므로 2의 값을 세팅하도록 하겠습니다.

<Range_lookup 세팅>

True / False의 값을 세팅할 수 있는 인수입니다.

True인 경우에는 유사한 값

False인 경우에는 일치하는 값을 표시해줍니다.

여기서는 정확한 값이 필요하기 때문에

False의 값을 세팅해주도록 하겠습니다.

완성된 수식

=VLOOKUP(C3,단가표!$B$2:$D$7,2,FALSE)

<VLOOKUP 결과>

모든 인수의 세팅이 끝나면 확인 버튼을 클릭하고

결과를 확인해보도록 합시다.

D3셀에 "배"값이 세팅되었습니다.

단가표에 A-01-02 상품코드의 값과 동일한지 확인해 볼까요?

단가표 시트에도 A-01-02의 상품코드는 "배"로 세팅되어 있는 것을

확인할 수 있습니다.

그럼 나머지 셀의 값도 세팅을 해줘야겠죠?

셀의 오른쪽 아래의 네모부분에 마우스 포인터를 올리면

커서의 모양이 "┼" 모양으로 변하게됩니다.

이때 더블클릭을 해주면 수식이 자동으로 채워지게 됩니다.

또 다른 방법은 "┼"모양으로 변한 상태에서

아래의 그림과 같이 드래그를 해주시면 됩니다.

그럼 단가는 어떻게 채워넣어야 할까요??

수식을 그대로 복사할 경우 "#N/A"라는 값이 표시되게 됩니다.

왜 이런 문제가 발생할까요?

다시한번 수식을 살펴보면 첫번째 인수인 Lookup_value의 값이

C3에서 D3으로 변경된 것을 보실 수 있습니다.

Lookup_value에는 절대주소를 설정하지 않아서 발생한 문제입니다.

수식을 아래와 같이 변경해봅시다.

=VLOOKUP($C3,단가표!$B$2:$D$7,2,FALSE)

C3 부분을 "$C3"으로 변경했습니다.

변경하는 방법은 F4를 연속으로 누르거나

직접 작성하시면 됩니다.

이런 값을 혼합주소라고 하며, 위와 같이 세팅한 경우

C는 변경되지 않고 숫자부분만 자동으로 변경되게 됩니다.

이 상태에서 E3셀에 수식을 복사해봅시다.

원하는 결과가 세팅 됐나요?

수식은 정상적으로 적용됐지만 원하는 단가가 표시되지 않았습니다.

이정도쯤 되면 이제 단가를 어떻게 가져오시는지 아시겠나요?

Col_index_num 값이 아직 2로 남아있는 것을 보실 수 있습니다.

마지막으로 수식을 아래와 같이 변경해주도록 합시다.

=VLOOKUP($C3,단가표!$B$2:$D$7,3,FALSE)

수식을 변경하고 채우기까지 완료한 결과입니다.

단가표와 비교해봐도 결과가 동일한 것을 보실 수 있습니다.

이것으로 VLOOKUP에 대한 설명이 모두 끝났습니다.

그러나 VLOOKUP을 사용하다보면 한가지 걸리는 게 있습니다.

바로 단가표에 상품코드에 해당하는 상품이 없는 경우

"#N/A"라는 의미불명의 문구가 표시되는 거죠.

"#N/A"의 의미는 applicable 또는 not available 의 약자로

"해당 없음"이라는 뜻을 가지고 있습니다. 찾는게 없다는 뜻이죠.

단가표에 없었으니 맞는 말이긴 하지만

출력을 해야하는데 이렇게 뜨면 조금 난감하죠.

그래서 해당없음(#N/A)상태일때는 해당 셀을

"상품없음"으로 표시하는 방법으로 이번글을 마무리 하도록 하겠습니다.

『 #N/A 시 공백으로 표시 방법 』

방법은 간단합니다. VLOOKUP을 "IFERROR"이라는 함수로

감싸주기만 하면 됩니다.

IFERROR 함수는 Value와 Value_if_error 두개의 인수를 가지고 있습니다.

Value

식이나 참조값이 들어갑니다.

Value_if_error

Value의 값이  #N/A, #VALUE!, #DIV/0!, #NULL! 인 경우에

표시되는 값입니다.

그럼 수식을 아래와 같이 변경해보도록 합시다.

=IFERROR(VLOOKUP($C3,단가표!$B$2:$D$7,2,FALSE), "상품없음")

단가표에는 A-01-06이라는 상품이 존재하지 않기 때문에

원래라면 "#N/A"값이 표시되어야 하지만

IFERROR을 사용하여 "상품없음"이라는 문구가 표시됩니다.

아무것도 표시하고 싶지않다면 "상품없음"을 ""로 변경하시면 됩니다.

이번글에서는 실무 엑셀에서 많이 사용되는

VLOOKUP 함수의 사용법과 #N/A값이 표시될 경우의

대처방법에 대해 알아봤습니다.

이해가 안가거나 궁금하신점이 있으시다면

댓글로 남겨주시면 감사하겠습니다.

그리고 글이 도움이 많이 됐다 싶으신 분은 공감버튼도

한번정도 눌러주시면 감사하겠습니다.

VLOOKUP 샘플파일은 다운로드 받을 수 있도록 올려놓겠습니다.

VLOOKUP 샘플.xlsx