피벗에 이어 가장 많이 사용하는 VLOOKUP 함수에 대해서 설명하고자 합니다.
매우 유용한 함수이지만, 입력해야하는 정보가 많아 자주 사용하지 않으면 사용범을 잊어버리게 되는 경우가 많습니다.
제가 주로 VLOOKUP 함수를 사용하는 작업은 아래와 같습니다.
첫째, 기준정보 맵핑
둘째, 리스트 등록여부 점검
셋째, 데이타 가져오기(비교) 등
VLOOKUP 함수는 두 개 이상의 중복 된 데이타에 대해서는 전부 출력하지 못합니다.
이에 Key값과 같이 주로 1:1로 맵핑되는 단일값을 가지는 항목에 사용합니다.
(만약 중복 된 값을 원하신다면 filter 함수를 사용하시면 되겠습니다.)
VLOOKUP 함수 사용법에 대해서 설명 드리도록 하겠습니다.
1. VLOOKUP 이란
찾고자 하는 값(lookup_value)을 약속 된 표(table_array)에서 찾아 지정한 순서(col_index_num)의 값을 가져오는 함수입니다.
하나의 수식을 만들고 지정한 순서(col_index_num)만 변경한다면 1개의 값에 해당하는 여러 다른 값을 가져 올 수 있는 함수입니다.
2. VLOOKUP 구조 및 예시
VLOOKUP 함수는 네 가지 값을 입력해야 합니다.
① lookup_value : 찾고자 하는 값
- 아래 마트에 따른 기준정보를 가져오고자 하기에 'A마트'의 셀 No.를 입력하면 됩니다.
② table_array : 기준이 될 테이블, 참고값
- 여기서 중요한 것이 기준테이블의 첫행에 1번의 값이 있어야 합니다.
③ col_index_num : 기준 테이블의 첫 행을 1번으로 하여 가져오가자 하는 데이터의 행 넘버
- 아래 수식을 보시면 'A2:D6'으로 A행이 1번, B행이 2번, C행이 3번, D행이 4번으로 진행 됩니다.
- col_index_num의 숫자를 다르게 하였을 경우 H열과 같은 결과 값이 나타납니다.
④ range_lookup : 데이타의 완전 일치(0, false)를 입력하시면 됩니다.
- 유사 일치(1, true)도 있는데, 말 처럼 유사한 값을 가져오는 것이 아니오니 완전 일치를 사용하셔야 합니다.
3. VLOOKUP 사용 시 Tip
① 셀 위치 고정 : $F$3,$A$2:$D$6
- 수식 작성 시 셀 값에서 F4를 누르면 앞에 '$'표시가 생기면서 셀을 복사하여도 가져오는 값의 위치는 변하지 않습니다. 많은 작업을 위해 복사를 할 때 유용합니다.
② 행 테이블 지정 : $A:$D
- 행으로 '기준 테이블'을 지정하게 되면(행에 있는 영어를 눌러서 영역 설정) 향후 마스터가 추가 되었을 때 수식을 변경해주지 않아도 됩니다.
- 마스터에 'E마트'가 추가하고자 할 때 'D마트' 아래에 정보를 입력해주면 되고, ①의 경우는 '$A$2:$D$6' ->'$A$2:$D$7' 로 변경해주어야 하고, ②의 경우는 수정하지 않아도 됩니다.
③ range_lookup : FALSE 입력(0 입력)
- 우리가 알고 있는 유사도와 다른 개념이다. 아래와 같이 False 입력 시 '완전일치'로 원하는값을 얻을 수 있지만, '유사일치'인 True(1)을 입력하게 되면 일치하는 값임에도 오류가 발생합니다. 그러니 반드시 FALSE(0)를 입력하셔야 합니다.
4. VLOOKUP 사용 시 주의할 점
마스터에 행을 추가(①)해야 할 때가 있습니다.
그럴경우 나도 모르게 수식이 꼬이게(③) 됩니다. 전체 수식이 전부 잘못되는게 아니고 아래와 같이 행을 추가한 뒷부분 데이터만 수식이 꼬입니다.
수식이 잘못되는 이유는 엑셀이 삽입 된 행은 수정 사항에 대해 반영(②)을 하지만 col_index_num는 자동으로 반영을 못해 주어서 입니다.(한칸씩 숫자가 밀림)
③을 수정하는 방법은 col_index_num를 3->4, 4->5로 +1씩 해주신다면 원하시는 값이 출력됩니다.
이와 같이, VLOOKUP 함수의 오류의 90% 이상은 영역 지정을 잘못해서라 보시면 되겠습니다.
나도 모르게 영역이 변경 되는 걸 방지하기 위해서는 중요 정보를 추가할 때 중간에 행삽입을 하는 것이 아닌 가장 끝(할인카드 오른쪽)에 해주시면 되고, 영역 지정(②)을 여유롭게 하셨으면 합니다.
그래도 가능하면 마스터 수정은 최소화하는게 필요하고, 불가피하게 수정 시 전체 항목에 대해서 점검 or 새로 함수 지정을 하셔야 실수를 줄일 수 있습니다.
충분하게 설명이 되었는지 모르겠네요.
적용 할 수 있는 곳이 엄청 많은 함수이오니 많이 사용하여 익숙해지셨으면 합니다.
특히, key값이라는 개념이 추가되면 유용성이 배가 됩니다.
기타 유사한 함수로는 HLOOKUP(위에서부터 값 찾기), XLOOKUP(첫행부터가 아닌, 행 대 행으로 값 찾기) 등이 있습니다.
XLOOKUP의 경우 엑셀365에서 추가된 수식으로 SUMIF 함수와 구조가 유사하오니 참고 하셨으면 합니다.
제가 VLOOKUP 함수를 활용하는 방법을 추가하였으니 참고하세요.
2021.07.06 - [엑셀 초보 강의] - 내가 vlookup 함수 실무 적용하는 방법
나에게 맞는 함수를 찾아 누구보다 빠르고 쉽게 처리하세요.
적게 일하고 많이 버세요~
'엑셀 초보 강의' 카테고리의 다른 글
| 엑셀 IF 함수 사용법 (0) | 2021.07.15 |
|---|---|
| SUMIF, SUMIFS 함수 사용법 (0) | 2021.07.12 |
| 내가 vlookup 함수 실무 적용하는 방법 (0) | 2021.07.06 |
| 엑셀의 꽃 피벗테이블 사용법(초보용 PIVOT) (0) | 2021.06.26 |
| 엑셀에 대하여 (0) | 2021.06.15 |