VLOOKUP, HLOOKUP, INDEX, MATCH 매크로 차이점 이해

엑셀은 스프레드시트 프로그램으로 도표 형태의 데이터를 관리하기 위해 주로 사용합니다. 이때 가장 표 데이터를 찾고 참조하려고 할때 가장 많이 상용하는 매크로가 VLOOKUP, HLOOKUP 입니다. 여기서 INDEX, MATCH 매크로까지 함께 결합하여 사용하면 표에 위치한 어떤 데이터 든 찾을 수 있는 강력한 기능을 제공하는 매크로 입니다.

먼저 VLOOKUP, HLOOKUP 매크로부터 살벼 보겠습니다.

 

(그림1) VLOOKUP 매크로 사용법

• Vertical Lookup 단어 그대로 "찾고자 하는 범위"의 첫번째 열에서 "조회하려는 제품명"이 있는 행을 먼저 찾습니다. 그리고 "조회하려는 제품명"이 있는 행에서 "월요일의 열 번호(순번)"에 해당하는 값을 반환 합니다. "검색 방법"은 일반적으로 FALSE로 지정하여 일치값을 찾도록 합니다.
• (그림1)은 제품명으로 월요일에 판매량을 찾는 VLOOKUP 매크로 예제 입니다.

(그림2) HLOOKUP 매크로 사용법

• Horizontal Lookup 또한 "찾고자 하는 범위"의 첫번째 행에서 "조회하려는 요일"이 있는 열을 먼저 찾습니다. 그리고 "조회하려는 요일"이 있는 열에서 "삼성-노트북의 행 번호(순번:3)"에 해당하는 값을 반환 합니다. "검색 방법"은 일반적으로 FALSE로 지정하여 일치값을 찾도록 합니다.
• (그림2)는 특정 요일의 "삼성-노트북" 판매량을 찾는 HLOOKUP 매크로 예제 입니다.

(그림3) VLOOKUP, HLOOKUP 매크로 제약 사항

• VLOOKUP과 HLOOKUP을 잘 사용하면 표에서 대부분의 데이터를 쉽게 찾을 수 있으나 두 매크로로 해결할 수 없는  제약 사항도 있습니다. 위 (그림3)에서 보는 바와 같이 찾기 범위 내 영역의 데이터를 가져오는 것은 문제가 없으나 VLOOKUP의 좌측과 HLOOKUP의 위쪽에 있는 영역의 데이터는 가져올 수 없는 제약 조건도 있습니다.
• 예를 들면 (그림3) 찾기 범위 VLOOKUP으로는 합계 판매량 365개인 제품의 요일별 판매량은 가져 올수 있으나 제품명은 가져올 수 없습니다. HLOOKUP의 경우도 "애플-노트북" 판매량이 59개 였던 요일을 찾아 올 수 없습니다.
• 이러한 제약 사항 문제는 INDEX와 MATCH 매크로를 조합하여 사용하면 모두 해결 할 수 있으며, VLOOKUP과 HLOOKUP 매크로의 기능도 대체할 수 있습니다.

(그림4) INDEX 매크로 사용법

• (그림4)에서 INDEX 매크로 사용 예를 보면 "판매량을 찾는 셀의 범위"에서 수요일 열 번호(순번:4), LG-노트북 행 번호(순번:3)을 가지고 LG-노트북의 수요일 판매량을 찾아 올수 있습니다.

(그림5) MATCH 매크로 사용법

• (그림5)는 MATCH 매크로로 "찾는 요일"이 "요일 검색 범위" 내에서 몇번째 순번에 위치하는지 찾는 예 입니다. "검색 방법"은 일치값을 찾는 것으로 합니다. 위 (그림5)에서 찾는 요일이 목요일 이면 매크로는 순번 4를 반환 합니다. 

(그림6) INDEX, MATCH 매크로 조합

• (그림6)은 INDEX와 MATCH 매크로 조합을 사용하여 판매량 합계 값에 일치하는 제품명을 찾는 예시 입니다. VLOOKUP 매크로를 사용해서는 판매량 합계로 제품명을 찾아 올 수 없는 문제를 INDEX와 MATCH 매크로를 사용하여 간단하게 해결하였습니다. HLOOKUP도 동일한 방식으로 사용하면 제약 사항을 해결 할 수 있습니다.

(퀴즈) VLOOKUP, HLOOKUP, INDEX, MATCH 매크로 사용하여 아래 문재를 해결해 보세요.

(그림7) 제품별&판매량 표 퀴즈 데이터

 

퀴즈에 대한 정답은 첨부된 엑셀파일에서 확인할 수 있습니다. 퀴즈를 보면서 해결 방법이 머리 속에 떠오르신다면 이미  매크로 전문가가 되신 겁니다.

VLOOKUP,HLOOKUP으로 해결하지 못하는 문제를 해결하는 매크로.xlsx

0.01MB