엑셀｜[ index & match ]｜ vlookup보다 유용한 index 와 match 조합 !!

 

 

엑셀을 다루는 직장인들이 이용하는 여러 수식들 중에

많은 데이터 속에서 원하는 특정 정보의 값을 추출할 수 있는 함수를 소개 하려고 한다

 

 

---

요약                       

 

1) vlookup

 

 

 

2) index&match

 

 

* vlookup은 많이 쓰이고 있으나 데이터 범위 지정에 제한이 있음

 

* index&match는 x,y그래프 ( x , y )처럼

  범위 내의 몇행 몇열의 데이터를 추출

 

* 몇행 몇열의 값을

  행조건이나 열조건이 바뀔 때마다 자동으로 추출되게끔 match함수를 사용

 

 

---

 

 

 

1) vlookup 함수                        

 

1-1) vlookup 구조       

 

먼저 유용하고 널리 쓰이고 있는 vlookup (hlookup)의 구조를 먼저 알아보면

이고 결과는 그 셀의 값을 반환한다

예시)

이렇게 왼쪽에 모든 데이터정보가 있고 오른쪽에 구하고자 하는 3명의 '키'와 '지역'이 주어진 후

홍길동의 키(cm) (=L2)를 구해보면

이렇게 L2에 178 이라는 값을 구해낼 수 있다

 

 

1-2) vlookup 단점       

 

하지만 vlookup 함수는 몇가지 단점이 있는데

 

하나는 데이터 범위를 지정할때, 구하려는 값이 데이터 범위내에서

꼭 첫번째 열에 찾고자 하는 데이터가 포함되어야 한다

즉 홍길동, 최주한, 김지성이라는 '이름' 데이터가 B열에 있기때문에 데이터범위는 B? : ?? 으로 시작 되어야 하며 

이렇게 넘버링 데이터가 있는 A열을 범위의 첫번째열로 지정하면

구하고자 하는 홍길동 등의 '이름'데이터가 A열에 없으므로 오류가 난다

 

그리고 바로 이 첫번째열 지정 때문에 파생되는 핵심적인 어려움이 있다

 

 

 

이번엔 오른쪽 아래 각 ID 3개의 해당 '생년월일'과 '성별'을 구하려고 하는데

'ID'는 G열에 있기에 데이터 범위를 G열을 첫번째 열로 놓고 보니 오른쪽에 데이터가 H열 '지역' 정보밖에 없다

그래서 범위를 구하고자 하는 '생년월일'과 '성별'이 포함되도록 설정 해도

 

위와 같이 오류가 난다

 

해결하기 위해선 '성별'의 C열과 '생년월일'의 D열을

구하는 값인 'ID'의 G열보다 오른쪽으로 열 전체를 옮기거나 'ID'의 G열을 맨앞으로 옮기거나

하는 등의 수정작업 후 가능은 하겠지만

다루는 데이터의 양이 상당하거나 원본 데이터 수정이 힘들다면 따로 복사하여 작업하는 등의 수고로움이 따른다

 

(+ hlookup 함수는 행,열이 반대로 되어 몇번째 행의 값을 찾는 함수이며 똑같은 어려움를 갖고있는 함수다)

 

 

 

 

여기서 

 

이러한 단점을 보완할 수 있는 수식이 바로 'index 와 match' 조합이다

 

 

---

 

 

2) index & match 함수　

 

 

함수가 두가지 이상이 조합이 된다면 복잡하고 어려워 보일 수 있는데

핵심만 잘 기억하면 이 수식들은 의외로 간단하다

 

 

2-1) index 함수       

 

 

먼저 Index 함수는 2가지 형태 (배열형, 조형)가 있는데

여기서는 match 함수와 조합을 이루는 배열형(첫번째)만 설명한다

두번째 형태인 조형은 여러개의 조(범위 -> reference)를 지정하고 그에 맞는 행,열 위치(row/column num)와 몇번째조인지(area num)을 설정하면 각각 다른 조의 행,열 위치에 맞는 값을 구할 수 있는건데

다음에 기회가 될 때 소개 하도록 하겠다

 

 

 

 

 

그럼 다시 우리가 이용할 Index 배열형의 구조는

이고 결과는 그 셀의 값을 반환한다

마치 x,y 좌표를 넣고 그 위치로 찾아가는것처럼

범위 내에서 몇행, 몇열의 데이터 값을 반환한다

예시)

여기서 우리는 1과 2를 직접 써넣는 단 한번의 작업을 위하여 수식을 이용하는 것이 아니기 때문에, 

구하고자 하는 행과 열의 값이 매번 바뀔 것이기 때문에

행번호, 열번호가 자동으로 바뀌도록 그자리에 다른 함수를 조합을 하는것인데

여기서 쓰일것이 match 함수이다

 

 

2-2) match 함수       

 

 

match 함수의 구조는

이고 결과는 숫자값을 반환하는데

범위안에 그 값이 몇번째에 위치해있는지 숫자로 반환해준다

예시)

A4에 있는 포도의 위치를 찾고자 B4에 수식을 적용한 모습이다

포도는 A1:D1 범위 내에서 두번째 칸에 위치하기때문에 2라는 결과를 반환한다

이렇게 match함수는 결과값이 한개의 숫자를 나타내기 때문에

데이터의 범위가 한개의 행이거나 한개의 열 이여야 한다

ex)               <한개의 행>                                                      <한개의 열>

 

 

이렇게 2개 이상의 행과 열을 데이터범위로 지정하게 되면

2행 1열에 있는 딸기를 match함수의 결과값으로 2,1 이런식으로 나타내는 것이 아니라

오류가 난다

 

 

 

그렇다면 우리가 적용할 index와 match의 구조는

이렇게 될 것이다

 

 

 

2-3) index & match 적용       

 

 

 

그럼 처음으로 돌아와 vlookup으로 구하지 못했던 값들을 다시 보면

 

 

rlftns 이란 'ID'를 가진 사람의 '생년월일'을 구하고 싶다면

 

 

이렇게 구할 수 있는데

rlftns 뿐만 아니라 다른 'ID'조건이 나와도 (행번호), '생년월일' 뿐만 아니라

'성별' 등의 다른 조건이 나와도 (열번호)

자동으로 바뀔 수 있게 match 함수를 적용하면

 

index의 행번호(match) = 2

index의 열번호(match) = 3

즉, index(범위,행번호,열번호)에서

행번호 : 'rlfehd'은 행번호 1, 'alswl'는 행번호 2가 자동으로 나오도록,

열번호 : '생년월일'은 열번호 3, '성별'은 열번호 2가 자동으로 추출 되도록 하기위해 match 함수를 사용한 것이다

 

 

그리하여 이렇게 찾을 수 있다 (드디어 찾았다)

 

그리고 중간중간 F4키로 고정시켜 놓은 행과 열들 덕분에

L7의 셀을 주변에 구해야 할 셀들에 그대로 복사하거나 셀의 오른쪽아래 ㅁ를 끌어당기면

 

원데이터 양이나 찾아야할 데이터가 많은 양이더라도

수월하게 일을 처리할 수 있다

 

 

 

 

끝

 

 

 

 

---