[excel]엑셀 vlookup, hlookup함수 다중 조건 값 추출

vlookup과 hlookup함수는 설정해준 범위에서 n번째 열(vlookup)이나 행(hlookup)의 값을 반환하는 함수이다.

따라서 엑셀에서 정리해야 하는 data가 표 형식일 때, 원하는 값을 추출하는데 유용한 함수 중 하나이다.

하지만 입력할 수 있는 조건에 해당하는 변수가 1개이기 때문에 다중 조건을 만족하는 값을 추출하기에 어려움이 있다.

따라서 이번에는 vlookup과 hlookup에 필수로 입력해야 하는 3가지 변수 중 하나를 값이 아닌 함수로 입력하여 다중 조건을 만족하는 값 추출 방법을 소개한다.

 

---

  

vlookup함수에서 다중 조건 값 추출하기

 

먼저 vlookup함수에 필수로 입력해야 하는 변수는 아래 3가지이다.

여기서 변수 3을 함수로 입력하면 2가지 조건에 해당하는 값 추출이 가능하다.

 

 vlookup(변수 1, 변수 2, 변수 3, 변수 4)

• 변수 1: 특정값
• 변수 2: 변수 1과 찾고자 하는 값을 포함한 전체 범위
• 변수 3: 해당 범위에서 오른쪽 몇 번째 열을 반환할지 숫자 표기
• 변수 4: false입력 시 변수 1과 일치하는 경우에만 결괏값을 반환함
• 참고: 변수 4를 미입력시 변수 1과 가장 비슷한 값을 기준으로 결괏값을 반환함

 

예를 들어 아래와 같은 표가 있다고 생각해보자.

vlookup 다중 조건 값 추출

 

여기서 "과목"과 "이름"이라는 두 가지 조건에 해당하는 성적을 vlookup함수로 구해보자.

 

"과목"과 "이름" 2개 변수 중 변수 1에 입력할 변수는 "이름"이다.

그 이유는 "vlookup, hlookup이 안될 때 해결 방법" 게시물에서 소개했듯이 vlookup 사용 시 변수 1이 변수 2의 첫 번째 열에 위치해야 하기 때문이다.

 

다음으로 "과목"에 해당하는 값들이 변수 2(범위 조건)에서 몇 번째 열에 위치하는지 표를 만들어준다.

국어, 영어, 수학은 각각 해당 범위에서 2, 3, 4번째 열에 위치한다.

vlookup 다중 조건 값 추출

 

이제 이 두 가지 표와 vlookup을 사용하여 다중 조건에 해당하는 값 추출이 가능하다.

• 변수 1: 입력한 이름에 해당하는 C13
• 변수 2: 철수, 민수, 영수의 국어, 영어, 수학 점수를 모두 포함하는 범위인 B3:E6
• 변수 3: 입력한 과목이  변수 2에서 몇 번째 열에 위치하는지에 해당하는 VLOOKUP(C12, B8:C10,2) 

엑셀 vlookup 다중 조건 값 추출

 

---

 

hlookup함수 사용 시에도 변수 3에 함수를 입력하는 방법으로 다중 조건 값 추출이 가능하다.

이때 vlookup과 마찬가지로 hlookup사용 시 변수 1에 해당하는 값이 변수 2(범위 조건)의 첫 번째 행에 위치해야 한다는 점을 주의해야 한다. 

 

예를 들어 위 예시에서 hlookup을 사용한다면 국어, 영어, 수학에 해당하는 표를 아래와 같이 만들어야 한다.

hlookup 다중 조건 값 추출

 

그리고 변수 3은 VLOOKUP(C13, B3:E6, HLOOKUP(C12, E8:G9,2))이 된다. 

 

참고로 hlookup도 vlookup과 마찬가지로 필수로 입력해야 하는 변수는 3가지이다.

• 변수 1: 특정값
• 변수 2: 변수 1과 찾고자 하는 값을 포함한 전체 범위
• 변수 3: 해당 범위에서 아래쪽 몇 번째 행을 반환할지 숫자 표기
• 변수 4: false입력 시 변수 1과 일치하는 경우에만 결괏값을 반환함
• 참고: 변수 4를 미입력시 변수 1과 가장 비슷한 값을 기준으로 결괏값을 반환함