Warning: array_merge(): Argument #1 is not an array in /home/rhaqjffp/html/wp-includes/blocks/shortcode.php on line 31

Warning: array_merge(): Argument #1 is not an array in /home/rhaqjffp/html/wp-includes/blocks/social-link.php on line 45
엑셀 : VLOOKUP 의 단점을 보완하고, 대신 사용할 수 있는 INDEX, MATCH 함수 – 곰벌레닷컴

엑셀 : VLOOKUP 의 단점을 보완하고, 대신 사용할 수 있는 INDEX, MATCH 함수

VLOOKUP 함수는 엑셀에선 없어서는 안될 요긴한 함수지만 사용하다보면 아래와 같은 불편한 단점들을 느낄 때가 있다.

  • 검색기준열은 검색범위의 가장 왼쪽열로 항상 고정
  • 두가지 이상 검색조건으로 검색 어려움
  • 검색은 항상 세로열 방향으로만 가능하여 면으로 검색 (마치 VLOOKUP 과 HLOOKUP 을 혼합해놓은 검색)은 불가능
  • 넓은 검색범위로 인해 검색범위 안에 열삽입/삭제 하게되면 VLOOKUP 의 결과가 틀어질 가능성 높음
  • 영문 대/소문자 구별 검색 불가능

하지만 VLOOKUP 대신 INDEX, MATCH 함수를 사용하면 VLOOKUP 과 같은 결과를 보이게 할 수 있으면서도 위 단점들을 최소화하는 보다 유연한 검색이 가능하다.

기본 사용법

아래는 VLOOKUP 함수와 INDEX, MATCH 함수의 비교와 동일한 결과를 보이고 있는 예시이다.

= VLOOKUP( 검색값, 검색범위, 열번호, false )
= INDEX( 검색대상열, MATCH( 검색값, 검색기준열, 0 ))

“거래ID” 로 “단가” 를 검색

두 함수식의 차이점은, VLOOKUP 함수는 검색범위를 넓게 지정하면 가장 왼쪽열이 검색기준열이 되고, 열번호만큼 오른쪽에 위치한 열이 검색대상열이 되지만, INDEX, MATCH 함수는 검색기준열검색대상열을 독립적으로 각각 지정한다는 점에 있다.

이제 아래부터 VLOOKUP 으로는 어려운 검색을 INDEX, MATCH 함수로 구현해보자.

함수식 응용

1. 오른쪽을 기준으로 보다 왼쪽에 있는 대상을 검색

오른쪽에 있는 “단가” 로 보다 왼쪽에 있는 “거래ID” 검색

기본 함수식 대로 사용하면 된다. 검색기준열검색대상열을 독립적으로 각각 지정하기 때문에 이와 같은 검색이 가능하다. 또한 이 이유로 인해 검색범위 안에 열삽입/삭제를 했을 때 결과가 틀어지는 VLOOKUP 함수의 단점도 INDEX, MATCH 함수에서는 나오지 않는다.

2. 두가지 이상 검색기준으로 검색

= INDEX( 검색대상열, MATCH( 1, (검색기준열1=검색값1)*(검색기준열2=검색값2), 0 ))

“거래처” 와 “상품” 으로 “단가” 검색

위 수식을 넣고 CTRL+SHIFT+ENTER 키를 눌러 배열수식으로 입력해야 정상적으로 작동한다. 또한 검색기준열=검색값 형태를 * 기호로 계속 이어붙이면 세개 이상의 조건 검색도 가능하다.

검색기준열=검색값과 같은 비교문의 결과는 true 아니면 false 이다. 이 결과에 * 기호와 같은 사칙연산을 가한 것인데, 엑셀은 이렇게 true 혹은 false 에 사칙연산을 가하면 각각을 1 과 0 으로 치환하여 계산한다. * 기호로 연결되어 있으므로 모든 검색기준열=검색값이 true 가 되어야 1 이 될 것이다. 마침 MATCH 함수의 첫번째 인수는 1 이므로 모든 조건을 만족하는 (즉 모든 조건이 true 가 되는) 위치를 INDEX 함수에 알려주게 되는 구조이다.

3. 면으로 검색 (VLOOKUP 과 HLOOKUP 의 혼합)

= INDEX( 검색대상범위, MATCH( 검색값, 검색기준열, 0 ), MATCH( 검색값, 검색기준행, 0 ))

세로방향 “거래처” 와 가로방향 “상품” 으로 “단가” 검색

위 엑셀 예시를 보면 면으로 검색한다는 것이 어떤 의미인지 알 것이다. INDEX 함수 안에 MATCH 함수가 두번 나오는데, 첫번째 MATCH 함수는 열방향(세로방향)으로 검색을, 두번째는 행방향(가로방향)으로 검색을 한다고 이해하면 된다.

4. 대/소문자 구별 검색

= INDEX( 검색대상열, MATCH( true, EXACT( 검색값, 검색기준열 ), 0 ))

VLOOKUP 함수는 원하는 결과를 제대로 보이지 못하고 있다.

위 수식 입력 후 CTRL+SHIFT+ENTER 키를 눌러 배열수식으로 입력해야 제대로 작동한다. 예시를 보면 VLOOKUP 함수와 비교를 해놓았는데 VLOOKUP 함수는 대/소문자를 구별하고 있지 않기에 엉뚱한 결과를 가리키고 있다.

대/소문자 구별의 핵심은 EXACT 함수인데, 엑셀에서 이 함수를 입력할 때 나오는 도움말 팝업을 보면 “대/소문자를 구분합니다.” 라고 나온다. 본래 EXACT 함수는 두 셀을 비교하여 같은지 다른지를 검사하지만 위 수식처럼 한쪽에 범위를 주고 배열수식으로 입력하면, 검색값검색기준열을 일일이 하나하나 비교하여 대/소문자를 구별한 완전일치 여부를 다시 배열로 반환한다. 이를 INDEX, MATCH 함수가 받아서 처리하는 구조이다.

참고사항

본 포스팅 서두에 VLOOKUP 의 단점을 나열하면서 마치 VLOOKUP 함수로는 구현이 아주 불가능한 것처럼 표현하였지만 실은 그렇지가 않다. 일부는 VLOOKUP 함수 자체로도 극복이 가능한 단점들인데 아래 포스팅을 참고하자.

대화에 참여

댓글 2개

댓글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다