VLOOKUP 함수는 복잡한 표에서 원하는 데이터만을 찾기위한 대표적인 방법입니다.
물론 정렬이나 찾기 기능을 통해서 원하는 데이터를 찾을 수도 있겠지만 VLOOKUP함수를 이용하면 문서를 자동화 할 수 있어 찾을 값만을 변경하여 원하는 데이터를 손쉽게 찾아볼 수 있습니다.
예제 파일을 보시면 재고 현황에 대한 데이터 표가 있습니다.
여기서 어떤 제품의 수량만 알고 싶다면 나머지 데이터는 수량을 찾는데 방해 요소만 됩니다. 제품명과 수량만 있으면 좀더 단순하게 알 수 있을 겁니다.
그리고 제품코드는 뭔가라고 생각하실수 있을것 같아 간단하게 설명드리자면 제품의 고유한 명칭이라 생각하시면 되겠습니다. 우리가 어떤 쇼핑몰에 회원 가입을 할때 고유한 ID를 만듭니다. 왜냐하면 이름이 동명인 사람이 있을 수 있기 때문이죠. 그래서 나만의 고유한 ID가 필요합니다. 내가 주문한 상품이 동명의 다른 사람에게 가면 안돼기 때문이죠.
마찮가지로 같은 제품명을 가지고 있을 경우 그 제품명으로는 재고 수량을 파악할 수 없습니다. 물론 제품명이 하나라면 가능하겠지만 항상 만일의 경우는 대비해야 되기 때문입니다. 이런 이유로 대부분의 제품들은 제품명과 함께 제품코드가 같이 존재합니다. 만약 제품코드나 제품번호가 없다면 쇼핑몰 같은 곳에서는 회원의 ID를 만들듯이 강제로라도 만들어야 됩니다.
위 표에서 특정 제품코드에 제품명과 수량만을 보고 싶다면 VLOOKUP함수를 이용해야 합니다.
VLOOKUP
- lookup_value - 조회하려는 값 또는 셀 참조(기준이되는 제품코드, 부품번호, ID 등)
의미 : lookup은 조회, value는 값을 의미 함으로 => 찾을 값 - table_array - 조회하려는 표의 범위로 최소한 찾고자하는 열 데이터까지 선택해 주어야 합니다.
의미 : table은 표를, array는 배열(범위) => 표에서 범위를 선택
주의 : 반드시 첫번째 인수인 lookup_value를 포함한 열을 좌측 첫번째 열로 설정해야 합니다. - col_index_num - 두번째 인수인 table_array로 설정한 범위의 열 번호(왼쪽 열 부터 1,2,3,~)
의미 : col은 열, index는 순서, num은 숫자 => 열의 순서를 좌측열부터 숫자로 반환 - [range_lookup] - 논리값으로 TRUE(1)는 유사일치, FALSE(0)은 정확히 일치
의미 : range는 범위, lookup은 조회 => 범위의 조회 방식 선택
주의 : 선택항목이지만 설정하지 않을 경우 TRUE 적용하기 때문에 반드시 확인하고 사용(많은 경우 FALSE 즉 정확히 일치를 사용합니다)
- 먼저 VLOOKUP을 실행할 셀(I5)를 선택합니다.
- 수식입력줄에서 함수를 작성 => =VLOOKUP(
- 첫번째 인수 lookup_value를 참조하기위해 H5셀을 선택 => =VLOOKUP(H5
- 콤마를 입력후, 두번째 인수 table_array를 참조하기위해 B5:C19 범위를 선택 => VLOOKUP(H5,B5:C19
- 콤마 입력후, 세번째 인수 col_index_num를 2(table_array로 선택한 열의 두번째 열)로 입력 => =VLOOKUP(H5,B5:C19,2
- 콤마 입력후, 네번째 인수 range_lookup 방식 선택(수식입력줄 에서 FALSE를 탭으 선택 또는 0 입력)
=> =VLOOKUP(H5,B5:C19,2,FALSE - 마지막 인수까지 설정했으니 엔터를 입력해서 자동 괄호 닫기와 함께 마무리합니다.
=> =VLOOKUP(H5,B5:C19,2,FALSE)
결과
제품코드 JD-005 의 제품명이 정상 출력됩니다.
이후 I5셀은 VLOOKUP으로 자동화 되었기 때문에 H5셀 값을 JD-005에서 FQ-003처러 변경을 하더라도 제품명이 정상 출력됨이 확인됩니다. 즉 제품코드 내의 어떠한 값을 넣더라도 제품명이 자동 검색되서 나오게 되는 것이죠
이제 I5셀이 제품코드를 기준으로 제품명을 반환하도록 만들었으니 I9셀 까지 자동채우기 하면 I열의 설정이 끝납니다.
그런데 여기에서 한가지 문제가 생깁니다. 우리가 두번째 인수인 table_array는 상대참조로 선택 했기때문에 자동채우기시에 table_array도 한칸씩 내려간 범위로 바뀌게 됩니다.
I6셀을 더블 클릭하면 그림처럼 확인됩니다. 마찮가지 I7셀을 더블 클릭하면 table_array의 범위가 B7:C21로 변경되는 것이 확인 될 것입니다. 이미 이런 경우를 LARGE 함수를 하면서 한번 경험 했었습니다. 다행히 현재 표에서는 첫번째 인수인 lookup_value가 두번째 인수인 table_array 안에 있으니 오류가 나지 않았지만 만약 H5셀의 값이 FQ-003이라면 오류가 발생했을 것입니다.
그럼 어떻게 해야할 까요? 그렇습니다. 절대참조를 사용하면 됩니다.
이렇게 범위를 인수로 사용할 경우 자동채우기를 해야 한다면 반드시 범위를 절대참조로 바꾸어야 됩니다. 꼭 기억해 두세요
1. 먼저 변경할 셀(I5)를 클릭하여 선택합니다.
2. 수식입력줄에 table_array 인수 부분을 드래그 선택하신 뒤,
3. f4키를 눌러 절대참조로 변경 한후 enter로 수식을 완성합니다.
4. 완성된 I5셀을 I9셀까지 다시 자동채우기해서 마무리합니다.
제품명은 끝났습니다. 수량도 동일한 방법으로 하시면 됩니다. 다만 table_array의 범위가 수량까지 확대되어야 할 것이고 col_index_num는 lookup_value를 찾을 열인 제품코드 열에서 4번째 열이므로 4가 되게 됩니다.
그래서 수식을 확인하면
=VLOOKUP(H5,$B$5:$E$19,4,FALSE)
이렇게 됩니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
VLOOKUP 사용시 주의하실 사항이 있습니다. 매우 매우 중요합니다.
첫번째 인수 lookup_value를 찾을 열이 두번째 인수 table_array를 지정할때 반드시 첫번째 열로 지정되어 있어야 합니다.
가령
그림처럼 마트코너라는 열이 제품코드 좌측에 있을 경우 table_array를 위와 같이 선택하면 안됩니다. 첫번째 인수 lookup_value를 찾을 열이 2번째 열이 되어 버렸기 때문입니다. 위의 경우라면
이렇게 지정해야 합니다. 만약 제품코드별로 마트코너을 VLOOKUP으로 찾고 싶다면 위의 표로는 도저히 찾을 수 없습니다. 해결방법은 마트코너열을 제품코너 오른쪽으로 이동해야만 가능한 것이죠
위 그림처럼 표 자체를 수정한 후에 VLOOKUP을 실행해야 합니다.
이러한 이유로 VLOOKUP을 대체할 XLOOKUP이라는 함수가 현재 나와있는 상태이기는 합니다. 다만 아쉽게도 엑셀 2019 이전 버전은 XLOOKUP을 사용할 수 없습니다. 그리고 현 시점에서 ITQ 시험도 2016버전을 사용합니다. 회사에서도 아직 구버전의 엑셀을 사용하는 경우가 많아서 일단은 XLOOKUP에 대해서는 넘어가려고 합니다.
다소 불편하더라도 표의 열을 변경하고 사용하는 VLOOKUP 사용을 기본으로 알고 있기를 바랍니다.
간단하게 XLOOKUP을 알려드리자면 위처럼 표의 위치를 변경하지 않아도 다른 방식으로 검색이 가능한 함수다라고 생각하시면 될 것 같습니다. 매력적이긴 하죠...
'강좌 및 설정 > 엑셀 기초' 카테고리의 다른 글
| 데이터 유효성 검사와 VLOOKUP을 통한 자동화 3(데이터 유효성 검사) (0) | 2023.06.23 |
|---|---|
| 데이터 유효성 검사와 VLOOKUP을 통한 자동화 2(엑셀 함수 - IFERROR) (0) | 2023.06.23 |
| 엑셀 함수 - 비교관련(MAX, MIN, LARGE, SMALL) (0) | 2023.06.15 |
| 엑셀 함수 - 집계,계산관련(COUNT,COUNTA, AVERAGE, SUM) (0) | 2023.06.15 |
| 엑셀 함수 - 도움말 활용 및 영문 인수 확인하기 (0) | 2023.06.15 |