엑셀 공부 : 다중 조건 VLOOKUP

다중 조건 VLOOKUP

4. 다중 조건 VLOOKUP

VLOOKUP은 기본적으로 단일 조건(하나의 값)만으로 데이터를 찾지만, 여러 조건을 결합하여 검색해야 할 때가 많습니다. 여기에서는 IF와 VLOOKUP의 결합, 복합 조건 검색 기법, 중첩 함수 활용 방법을 배워봅시다.

---

1. IF와 VLOOKUP 결합

IF 함수와 VLOOKUP 함수를 결합하면, 조건에 따라 다른 VLOOKUP 결과를 반환할 수 있습니다.

 

예시: 학생별 점수 확인

 

	A	B	C
1	이름	과목	점수
2	김철수	수학	90
3	김철수	영어	85
4	이영희	수학	95
5	이영희	영어	80

 

문제
김철수의 점수를 찾아야 하는데, 과목이 "수학"일 때와 "영어"일 때 점수를 다르게 표시하고 싶습니다.

 

풀이

1. IF 함수로 조건 설정: 과목이 "수학"이면 수학 점수를, "영어"면 영어 점수를 찾습니다.
2. VLOOKUP으로 점수 찾기: IF 조건에 따라 표에서 값을 검색합니다.

함수 입력:

=IF(A1="수학", VLOOKUP("김철수", A2:C5, 3, FALSE), VLOOKUP("김철수", A2:C5, 3, FALSE))

 

결과: 과목에 따라 다른 점수가 반환됩니다.

---

2. 복합 조건 검색 기법

VLOOKUP은 기본적으로 첫 번째 열에서만 값을 찾습니다. 하지만 여러 열을 기준으로 검색하려면 복합 조건을 만들어야 합니다.

방법 1: 새로운 열 추가

복합 조건으로 사용할 값을 새 열에 만들어 첫 번째 열로 활용합니다.

 

예시: 직원 근무 시간 확인

 

	A	B	C
1	이름	부서	근무시간
2	김철수	인사팀	9시간
3	김철수	재무팀	8시간
4	이영희	인사팀	10시간

 

문제
"김철수의 재무팀 근무 시간을 찾아보세요."

 

풀이

1. 복합 조건 열 추가:

   	A	B	C	D	F
   1	이름	부서	근무시간	조건	근무시간
   2	김철수	인사팀	9시간	김철수-인사팀	9시간
   3	김철수	재무팀	8시간	김철수-재무팀	8시간
   4	이영희	인사팀	10시간	이영희-인사팀	10시간

2. VLOOKUP으로 조건 검색:

=VLOOKUP("김철수-재무팀", D1:F4, 3, FALSE)

 

결과: 8시간

---

방법 2: 배열 수식 사용

별도의 열을 추가하지 않고 배열 수식으로 복합 조건을 처리합니다. (배열 수식은 복잡도가 높으므로 고급 사용자를 위한 옵션입니다.)

 

예시 함수:

=INDEX(C2:C4, MATCH(1, (A2:A4="김철수")*(B2:B4="재무팀"), 0))

결과: 8시간
※ 배열 수식을 사용하려면 Ctrl+Shift+Enter로 입력합니다.

---

3. 중첩 함수 활용

VLOOKUP 안에 다른 함수를 중첩하여 데이터를 동적으로 처리할 수 있습니다.

예시: 동적 범위 설정

표가 다른 시트에 있거나, 검색 조건이 변할 때 동적 범위를 설정할 수 있습니다.

 

예시: 학년별 학생 데이터

 

	A	B	C
1	학년	이름	점수
2	1학년	김철수	85
3	1학년	이영희	90
4	2학년	김철수	88
5	2학년	이영희	92

 

문제
"2학년 김철수의 점수를 찾아보세요."

 

풀이

1. 동적 범위 설정: IF 함수로 학년에 따라 검색 범위를 동적으로 설정합니다.

함수 입력:

=VLOOKUP("김철수", IF(A2:A5="2학년", B2:C5, ""), 2, FALSE)

---

실습 문제

문제 1: 복합 조건 열 추가

아래 데이터를 사용해 "김철수-서울"에 해당하는 값을 찾아보세요.

 

	A	B	C
1	이름	지역	판매량
2	김철수	서울	100개
3	김철수	부산	80개
4	이영희	서울	120개

 

풀이:

위의 예시들에서는 표의 오른쪽에 조건을 추가하고 구하고자 하는 판매량을 복사했는데

보통 이럴때는 제일 앞쪽에 조건을 넣어주는 것이 좋다.

	A	B	C	D
1	조건	이름	지역	판매량
2	김철수-서울	김철수	서울	100개
3	김철수-부산	김철수	부산	80개
4	이영희-서울	이영희	서울	120개

 

A 열을 한번에 선택해서 열 추가를 해서 조건을 넣어준다.

=B2&"-"&C2 -> 이런식으로 하면 김철수-서울  이렇게 된다.

위의 표시형식은 매우 자주 쓰인다. = 을 넣어서 함수 혹은 수식이라고 이야기 해주고

=셀&"-"&셀  -> & 표기는 셀과 문자 혹은 셀과 셀을 이어서 나타내는 연결코드? 라고 생각하면 편하다.

그리고 문자열은 쌍따옴표 안에 넣으면 문자를 표기 할 수 있다. 이것은 '=' 을 선언했기 때문에 쌍 따옴표 안에 넣고,그렇지 않을경우는 그냥 적어도 된다.이게 ... 말로 하면 굉장히 쉽게 설명할 수 있는게 글이라서 쉽지가 않다 ㅎㅎ..무튼 표를 저렇게 만들고, 우리가 구하고자 했던 "김철수-서울" 을 정확하게 찾아서 판매량을 찾으려면

 

빈 셀에

 

=VLOOKUP("김철수-서울",A1:D4,FALSE)

 

위와같이 적으면 100개를 구할 수 있다.

지금은 하나씩 찾기에 절대참조를 안넣었지만. 나중에 여러가지를 구할 때는 꼭 절대참조를 넣어야한다. ($ 표시 )절대 참조 역시... 글로 설명하기에는 매우 어렵다.

VLOOKUP 은 지난 시간에도 말 했는데, 함수를 적는것 보다는 데이터를 어떻게 갖추고 있느냐가 제일 중요하다.

 

 

---

문제 2: IF와 VLOOKUP 결합

다음 데이터를 사용해 "김철수"가 부서가 "영업팀"일 때의 근무 시간을 찾으세요.

 

	A	B	C
1	이름	부서	근무시간
2	김철수	인사팀	8시간
3	김철수	영업팀	9시간
4	이영희	인사팀	10시간

 

풀이:

적절한 예시 문제는 아닌 것 같지만 풀어보면 일단 표를 보고 영업팀을 거르고 김철수를 VLOOKUP 해서 근무시간을 찾는방식으로 작성한다.

=VLOOKUP("김철수", IF(B2:B4 = "영업팀",A2:C4,""),3,FALSE)

 

B2:B4 안에 영업팀이 있으면 "김철수"를 찾아서 3번째 근무시간을 구한다.

 

VLOOKUP 을 많이 쓰는 나로서는 조금 생소한 방식이기는 하지만 값이 구해졌다..

 

사실 간단한 표의 경우 눈으로 찾는것이 훨씬 빠르다. 일명.. 가끔은 함수를 머리써서 적용하고 쓰는것 보다는 노가다가 빠를때가 많다..

다른 방식으로는 아까 위의 문제처럼 A열에 조건을 추가해서 구하는 방식이 있겠다.

 

	A	B	C	D
1	조건	이름	부서	근무시간
2	김철수-인사팀	김철수	인사팀	8시간
3	김철수-영업팀	김철수	영업팀	9시간
4	이영희-인사팀	이영희	인사팀	10시간

 

 

이런식으로 A 열에 조건을 새로 만든 후

 

=VLOOKUP("김철수-영업팀",A1:D4,4,FALSE)

 

이런식으로 찾는게 머리를 단순하게 쓰면서 빨리 찾는 방법일 것이다.

다만 중복이 나오는 경우는 위에서 제일 가까운 것이 찾아지고 2번째 것은 찾을 수 없다.

 

INDEX/MATCH 함수를 써도 되는데, VLOOKUP 만 공부하는 곳이니 VLOOKUP 만 적용하였다.

---

헷갈릴 것이다.. VLOOKUP 사용법은 계속 되뇌이고, 되뇌여야 외워진다.

VLOOKUP ( 찾을 값 , 찾을 표(범위) , 몇번째 있지? , 정확하게? (FALSE / TRUE) )

1번째 찾을 값은 문자면 쌍따옴표 안에 넣어준다. 숫자면 그냥 써도 된다. 다만 찾는 표(범위) 에서 숫자로 있는지 문자로 있는지도 알아야 한다.

2번째 찾을 표(범위) 는 항상 내가 찾고자 하는 값이 제일 왼쪽에 있어야 한다.

3번째 몇번째 있지? , 내가 찾을 값이 항상 표에서 1번째가 된다. 그곳부터 1 이다 1,2,3,4 이런식으로 속으로 새면 된다.

(필자도 그렇다 ㅎ)

4번째 FALSE / TRUE 의 경우 0 과 1 로 써도 된다. 0 = FALSE , 1 = TRUE 

 

나중에 절대 참조를 이용해서 VLOOKUP 을 쓰게 되면 범위의 몇번째 인지를 셀에 표기해두고 사용해도 된다.

이건 나중에...

 

2024.12.05 - [엑셀] - 엑셀 공부 : VLOOKUP 오류 해결

엑셀 공부 : VLOOKUP 오류 해결

2024.12.02 - [엑셀] - 엑셀 공부 : VLOOKUP 기본 사용법

엑셀 공부 : VLOOKUP 기본 사용법