저번 포스팅에서 VLOOKUP 함수에 대해서 알아보았는데요.
엑셀 EXCEL - VLOOKUP 함수 쉬운 설명 그리고 단점
직장인이라면 엑셀은 땔래야 땔 수 없는 숙명과도 같은, 그리고 퇴근 시간을 앞당겨 줄 수 있는 강력한 툴로써 직장 생활을 한다고 한다면 은퇴할 때 까지 평생 같이가야 동반자와 같습니다. 그
oncelifehappy.tistory.com
VLOOKUP 함수의 단점에 대해서 얘기를 드렸습니다. 만약, 찾고자 하는 값이 범위(테이블)의 첫번째 열이 아니고 중간이나 마지막 열에 있을 경우 VLOOKUP 함수는 사용할 수 없다고 말이죠.
예를 들어 아래 표와 같이 '사과'라는 상품의 가격과 배송처에 대해서 알고 싶은데 과연, VLOOKUP 함수를 사용할 수 있을까요?
정답은 불가능하다. 입니다.
왜냐하면 VLOOKUP 함수는 첫번째 열을 기준값으로 하여 두번째, 세번째, 네번째 등등, 그 뒷열의 값을 추출하는 함수이기 때문이죠. 그래서 VLOOKUP 함수에는 한계가 존재합니다.
그렇다면 VLOOKUP 함수의 한계를 넘어서는 함수는 없을까요?
그것이 바로 INDEX/MATCH 함수의 조합이라고 할 수 있습니다.
- MATCH 함수
- INDEX 함수
- INDEX/MATCH 함수 응용
1. MATCH 함수
MATCH 함수란 찾고자 하는 값이 찾는 범위 내에 몇 번째 열에 있는지, 몇 번째 행에 있는지를 찾을 수 있는 함수입니다.
단, 행과 열의 갯수는 여러 개가 될 수 없고 오직 한행, 한열에 대해서만 수식이 가능합니다.
예를 들어 보겠습니다.
앞의 표에서 경기도가 몇 번째에 있을까요?
배송처를 기준으로 세 번째에 있습니다. 이것을 MATCH 함수의 수식으로 나타내면 아래와 같습니다.
=MATCH(찾고자 하는 값, 찾고자 하는 값이 포함 된 열 혹은 행, 0(완벽히 일치하는 값))
위의 사진은 열에 대한 기준을 알아 보았지만 행 역시 마찬가지로 할 수 있습니다. 만약, 열과 행이 바뀐 데이터라도 똑같이 적용할 수 있습니다.
2. INDEX 함수
INDEX 함수란 정해진 범위 안에서 몇 번째에 있는 값을 추출할 수 있는 함수 입니다. INDEX 함수에는 배열형과 참조형의 두 가지 형식이 있고 단독으로 쓰이기 보다는 다른 함수와 결합이 되어 쓰이는 것이 대부분 입니다. 대표적으로 MATCH 함수가 있죠.
배열형 = INDEX(범위 혹은 배열, 행 번호, 열 번호(생략가능))
참조형 = INDEX(값을 찾을 범위 영역(여러개 가능), 값이 위치한 행 번호, 값이 위치한 열번호(생략가능), 값이 위치한 범위의 번호(생략 가능하나 생략시 1이 기본값)
사실 INDEX 함수는 보통 배열형을 사용하고 참조형은 잘 사용하지 않습니다. 참조형의 경우에는 여러 범위를 설정하고 범위 자체가 동일해야 하며 그리고 그 범위가 한 Sheet 에 내에 존재 할 때 사용하는 경우가 많습니다.
엑셀이라고 하면 여러 파일 혹은 여러 Sheet 의 data 들을 취합하기 위해 많이 사용하므로 참조형은 잘 사용하지 않습니다. 그리고 본 포스팅의 주제인 INDEX/MATCH 함수에는 배열형만을 사용하기 때문에 이번 포스팅에서는 배열형에 대해서만 간략히 설명하겠습니다.
예를 들어 보겠습니다.
아래 5행X5열의 표에서 3번째 행, 2번 째 열에 있는 숫자를 구한다고 하겠습니다.
=INDEX(범위 혹은 배열, 행 번호, 열 번호)
=INDEX(지정 범위, 3, 2) 를 하게 된다면 "12" 라는 3행과 2열에 있는 값을 도출 할 수 있습니다.
3.INDEX/MATCH 함수 응용
앞서서 MATCH 함수와 INDEX 함수에 대해서 알아보았습니다. 이쯤 오셨으면 아마 어느 정도 눈치를 채셨을 것입니다. INDEX 함수와 MATCH 함수가 어떻게 응용되는지요.
MATCH 함수는 원하는 값의 행, 열 번호를 파악하기 위함이고 INDEX 함수는 행, 열 번호를 통해 다른 값을 찾는 함수이므로 이 둘을 조합한다면 원하는 값을 기준으로 다른 값을 찾는 조합 함수라고 볼 수 있습니다. VLOOKUP 과 상당히 유사하죠.
그렇기 때문에 VLOOKUP 함수가 가지고 있는 한계를 뛰어 넘을 수 있습니다.
다시 서론으로 돌아가보겠습니다.
우리의 최초 고민 거리였던 아래 표에서 사과 값에 대한 가격과 배송처를 이제는 구할 수 있지 않을까요? INDEX/MATCH 함수를 통해서 말이죠.
그렇다면 사과의 가격과 배송처를 알아본다는 기준으로 예를 들어 보겠습니다.
1. 먼저 MATCH 함수를 사용해서 사과의 열번호 혹은 행번호를 알아야합니다.
= MATCH("사과",C1:C5,0)
이렇게 한다면 사과의 열 번호 '1'을 추출할 수 있습니다. 즉 사과의 앞 배열에 있는 가격과 배송처의 값을 INDEX 함수를 통해 불러오면 우리의 고민거리는 해결입니다.
2. INDEX 함수를 통해서 사과의 가격과 배송처를 추출해 보겠습니다.
앞서서 MATCH 함수로 사과의 열 번호(1,첫번째)를 알았기 때문에 가격이 있는 범위, 배송처가 있는 범위에서의 첫 번째 열에 있는 가격을 추출하면 그것이 사과의 가격, 사과의 배송처가 됩니다.
=INDEX(B2:B5,MATCH("사과",C2:C5,0))
=INDEX(A2:A5,MATCH("사과",C2:C5,0))
VLOOKUP 으로 할 수 없었던 것을 INDEX/MATCH 함수로 가능합니다. 그리고 여기서 중요한 포인트는 INDEX 함수와 MATCH 함수의 범위 시작점을 동일하게 설정해야 합니다. 행 혹은 열의 위치에서 추출하는 것이기 때문에 범위 시작점이 달라진다면 값도 달라지기 때문입니다. 예를 들어 MATCH 함수의 범위를 A1 부터 시작한다면 INDEX 함수도 B1, C1, D1 등으로 시작해야 합니다. 만약 B2,C2,D2 등으로 시작한다면 다른 값이 추출되기 때문이죠.
4. 줄이자면..
INDEX/MATCH 함수는 다시 언급하자면 VLOOKUP 함수의 상위 버전이라고 말할 수 있습니다. VLOOKUP 함수의 단점과 한계를 극복할 수 있는 함수죠. 그렇기 때문에 VLOOKUP 함수가 하지 못했던 다중 조건이라던지, 다중 data 추출이 가능합니다. 초보자 수준에서 고급 수준으로 넘어갈 수 있는 단계죠. 그리고 응용을 잘하게 된다면 웬만한 data 추출은 INDEX/MATCH 로 마무리를 지을 수 있습니다. 특히 직장인 수준에서는 말이죠. 그렇기 때문에 INDEX/MATCH 함수는 친해 지면 언젠가는 보답하는 친구 입니다.
개념이 조금은 어려울 수도 있지만 단 한가지, 행과 열 번호를 통해서 데이터를 가져온다 라는 기본 개념만 이해하셔도 INDEX/MATCH 함수는 충분히 활용할 수 있을 것입니다.
'엑셀잡담' 카테고리의 다른 글
| 엑셀 EXCEL - 행정병 출신의 유용한 실무 단축키 모음 (3) | 2023.03.21 |
|---|---|
| 엑셀 EXCEL - IFERROR 함수, VLOOKUP 함수 시 #N/A 에러가 뜬다면? (0) | 2023.03.21 |
| 엑셀 EXCEL - 조건을 만족할 때 행 혹은 열 전체 색 변경하기 (0) | 2023.03.20 |
| 엑셀 EXCEL - 행, 열 번갈아서 배경색 채우기 그리고 한 가지 문제점 (0) | 2023.03.17 |
| 엑셀 EXCEL - VLOOKUP 함수 쉬운 설명 그리고 단점 (0) | 2023.03.15 |