VLOOKUP 공식은 =VLOOKUP(찾을값, 데이터 전체범위, 열번호, 0or1)로 여기서 찾을값 즉 조건은 1개밖에 들어갈 수밖에 없습니다. 만약 찾을값(조건)이 여러 개일 경우엔 어떻게 해야 할까요? 오늘은 그 응용법에 대해서 알아보겠습니다.
① VLOOKUP 다중조건 응용법
▶ 찾을값들을 &로 연결하여 1개로 만들어주기
A | B | C | |
1 | 부서 | 이름 | 급여 |
2 | 경영지원부 | 김시연 | 100 |
3 | 기획부 | 최성국 | 150 |
4 | 생산부 | 박하늘 | 200 |
실무에서 잘 쓰는 VLOOKUP함수를 이용해 기획부 최성국의 급여를 알아봅시다!
일단 부서에서 기획부를 찾아야 하고(찾을값1), 이름에서 최성국을 찾아야 함(찾을값2)
VLOOKUP 공식은 =VLOOKUP(찾을값, 데이터 전체범위, 열번호, 0or1)인데 여기서 찾을값은 1개밖에 들어가지 않음.
난 찾을값이 2개인데 VLOOKUP함수에는 찾을값이 1개밖에 들어가지 않는다면 어떻게 해아 할까?
A | B | C | D | |
1 | 새로운 열 추가 ▽ | 부서 | 이름 | 급여 |
2 | =B2&C2 =경영지원부김시연 |
경영지원부 | 김시연 | 100 |
3 | =B3&C3 =기획부최성국 |
기획부 | 최성국 | 150 |
4 | =B4&C4 =생산부박하늘 |
생산부 | 박하늘 | 200 |
기존 표에 새로운 열을 추가한 후 셀과 셀을 연결해 주는 &를 사용해 찾을 값들을 묶어버리자!
B2셀과 C2셀을 &로 연결하면 경영지원부김시연이 나오고 B3셀과 C3셀을 &로 연결하면 기획부최성국이 출력된다.
찾을값이 기획부와 최성국이었는데 이 두 찾을값을 1개로 연결시켜버리니 찾을값이 1개로 완성!
이때 새로 만든 찾을값은 표에서 가장 왼쪽에 위치해 있어야 한다.
그 이유는 VLOOKUP의 찾을값은 데이터 전체범위에서 가장 왼쪽에 위치해 있어야 하기 때문이다. (하나의 약속)
이 부분을 더 자세히 알고 싶다면 하단의 링크를 참고해 주시길! ▼
아무튼 이렇게 새로운 열을 추가하는 작업을 했다면 기존처럼 VLOOKUP함수를 이용해 주면 된다.
=VLOOKUP(찾을값, 데이터 전체범위, 열번호, 0or1)
=VLOOKUP(A3,A1:D4,4,0)
=150
A3셀 기획부최성국을 찾을 것이고
범위는 데이터 전체범위인 A1:D4
최종 출력은 급여이니 급여가 있는 열인 D열, 즉 4열에 위치해 있으므로 4 쓰기
0은 일치값, 1은 유사값인데 우리는 일치하는 값을 찾는 것이니 0
② VLOOKUP 다중조건 복잡한 표에서 활용해 보자!
A | B | C | D | E | F | G | H | |
1 | 거래처 | 사입명 | 제품군 | 상태 | 1월 1주차 | 1월 2주차 | 1월 3주차 | 1월 4주차 |
2 | 일본 | ABC | 지갑 | 재고 | 100 | 150 | 156 | 167 |
3 | 일본 | ABC | 지갑 | 도착 | 99 | 123 | 342 | 349 |
4 | 대만 | GFF | 신발 | 재고 | 43 | 231 | 48 | 132 |
5 | 대만 | GFF | 신발 | 도착 | 213 | 44 | 522 | 17 |
위의 표를 기준으로 아래의 표를 작성한다고 생각해 보자! ▼
L | M | N | O | P | Q | |
1 | 거래처 | 제품군 | 1월 1주차 | 1월 2주차 | 1월 3주차 | 1월 4주차 |
2 | 재고 | 도착 | 재고 | 도착 | ||
3 | 일본 | 지갑 | ||||
4 | 대만 | 신발 |
위 표를 보면 찾을값은 거래처(찾을값1), 제품군(찾을값2), 상태(찾을값3)가 된다.
조건이 여러 개일 때는 조건들을 &로 연결해 1개의 조건으로 만들어주는 게 포인트라고 배웠다!
A | B | C | D | E | F | G | H | I | |
1 | 새로운 열▼ | 거래처 | 사입명 | 제품군 | 상태 | 1월 1주차 | 1월 2주차 | 1월 3주차 | 1월 4주차 |
2 | =B2&D2&E2 =일본지갑재고 |
일본 | ABC | 지갑 | 재고 | 100 | 150 | 156 | 167 |
3 | =B3&D3&E3 =일본지갑도착 |
일본 | ABC | 지갑 | 도착 | 99 | 123 | 342 | 349 |
4 | =B4&D4&E4 =대만신발재고 |
대만 | GFF | 신발 | 재고 | 43 | 231 | 48 | 132 |
5 | =B5&D5&E5 =대만신발도착 |
대만 | GFF | 신발 | 도착 | 213 | 44 | 522 | 17 |
거래처&제품군&상태를 연결해 새로운 찾을값의 열을 완성!
여기서 찾을값 열은 앞서 말했지만 항시 표 가장 왼쪽에 위치시켜둔다는 것을 잊지 말자!
그럼 이제 N4에 넣을 수식을 작성해 보자!
=VLOOKUP(찾을값, 데이터 전체범위, 열번호, 0or1)
=VLOOKUP(L4&M4&N3,A1:I5,6,0)
=100
찾을값은 L4&M4&N3 = 일본지갑재고
데이터 전체범위는 새로운 찾을값 열을 포함한 A1:I5
최종 출력은 1월 1주차이니 1월1주차가 있는 열인 F열, 즉 6열에 위치해 있으므로 6 쓰기
0은 일치하는 값, 1은 유사값인데 우리는 일치하는 값을 찾는 것이니 0
만약 N3에 수식 작성 후 나머지 빈 공간들은 자동 채우기로 채우고 싶다면
=VLOOKUP(찾을값, 데이터 전체범위, 열번호, 0or1)
=VLOOKUP($L4&$M4&N$3,$A$1:$I$5,N$1,0)
$ 표시는 움직이지 않음 즉 고정해 둠이라는 표시로 찾을값 $L4&$M4&N$3를 보면
$L4는 L열은 움직이지 않겠소란 뜻으로 L4,L5,L6 이렇게만 움직임
$M5도 마찬가지로 M열은 움직이지 않겠다는 것이므로 M5,M6,M7 이렇게만 움직임
N$3은 3행은 그대로 고정하고 열만 움직이겠다란거니 N3,O3,P3 이렇게 움직임
데이터 범위는 움직이지 않기 때문에 $A$1:$I$5 $표시를 다 함. 보통 VLOOKUP 사용 시 데이터 범위는 고정하기 때문에 절대참조하는 편임. VLOOKUP 사용시 데이터범위 설정 후 F4 눌러 전체 고정시켜 주는 식으로 버릇을 들이면 편함.
출력값인 1월 1 ~ 4주차는 A1:I5 데이터에서 6,7,8,9열에 속하는데 이거를 표에 적어주면 자동 채우기 할 때 유용함.
1월 1주차는 6열이므로 N1, 1월 2주차는 7열이므로 O1, 1월 3주차는 8열이므로 P1, 1월 4주차는 9열이므로 Q1
즉 1행은 안 움직이고 열만 움직이므로 열번호 넣는 자리는 N$1로 적어준 거임.
이것으로 찾을값이 여러 개일 경우, VLOOKUP함수를 어떻게 응용해서 쓰면 되는지에 대해 알아봤습니다. 자주 쓰는 함수를 이리저리 응용하는 편이 그 함수와 친해질 수 있는 지름길이거니와 복잡한 함수 공식에서 벗어날 수 있는 방법이지 않을까요? 다음 포스팅도 유익한 정보로 찾아뵙겠습니다. 끝까지 읽어주셔서 감사드립니다.
[ 참고하면 좋은 글 ]
'엑셀 정보' 카테고리의 다른 글
엑셀실무 MD 매출분석 VLOOKUP INDEX MATCH LARGE함수 사용 (1) | 2023.05.03 |
---|---|
직장인 엑셀 INDEX MATCH 함수 사용법 (1) | 2023.03.10 |
VLOOKUP함수 중복값 찾기 COUNTIF 기억하자! (0) | 2023.02.13 |
엑셀실무 VLOOKUP INDEX MATCH 사용법 (1) | 2023.02.02 |
컴활 2급 합격방법 책 없이 2주 독학으로 자격증 따기! (0) | 2022.11.20 |
댓글