■ 목차
1. 함수 설명
2. Match 함수의 구문 / 사용방법
3. Match 함수 사용해 보기
4. Index 함수의 구문 / 사용방법
5. Index 함수 사용해 보기
6. Index, Match 함수로 원하는 값 찾기
7. Index, Match 함수로 여러 개의 값 가져오기 - 배열 수식
8. Index, Match 함수로 다중 조건으로 찾기 - 배열 수식
** 핵심 요약
1. 함수 설명
- 오늘은 Index 함수와 Match 함수를 이용한 Vlookup 함수와 동일한 기능인 원하는 값 찾기를 해보도록 하겠습니다.
- 함수 자체는 간단하게 설명을 드리고 조건에 맞는 값을 찾는 원리와 사용방법에 대해서 좀 더 자세히 설명을 할 수 있도록 하겠습니다.
- 조건에 맞는 값을 찾아오는 함수로 Vlookup 함수를 많이 사용을 하고 있지만 표의 왼쪽에 있는 값을 찾아오지 못하는 한계 때문에 많은 분들이 Index, Match 함수를 사용하고 있습니다.
- 최신 버전의 엑셀을 사용하신다면 Xlookup 함수가 더욱 사용하기 편리함으로 Xlookup 함수 사용을 추천드립니다.
- 작동 원리로 보면 수식의 적용 순서는 먼저 ① "Match 함수"가 일치하는 데이터의 위치를 찾아 오고
- 다음으로 ② "Index 함수"가 위치를 참조하여 해당 값을 찾아오는 방식으로 작동을 하고 있습니다.
- Match 함수는 매칭되는 "위치"를 찾아 주는 함수이고 Index 함수는 지정한 위치의 "값(데이터)"을 찾아 주는 함수라고 이해하시면 되겠습니다.
2. Match 함수의 구문 / 사용방법
- MATCH(lookup_value, lookup_array, [match_type])
- lookup_value 필수 요소입니다. lookup_array에서 찾으려는 값입니다.
- lookup_array 필수 요소입니다. 검색할 셀 범위입니다.
- match_type 선택 사항입니다. 숫자 -1, 0 또는 1입니다.
- Match 함수는 지정된 텍스트의 위치를 찾아주는 함수입니다. 지정한 범위에서 몇 번째에 있다고 해당 위치의 숫자를 정수로 반환합니다.
- 첫 번째 인수 lookup_value는 위치를 찾고자(lookup) 하는 "값(value)"이라고 보시면 됩니다. 텍스트, 숫자 모두 가능합니다.
- 두 번째 인수 lookup_array는 찾고자(lookup)하는 "범위(array)"입니다. Match 함수는 맨 위에서부터 lookup_array 범위에서 아래로 찾기를 해줍니다.
- 세 번째 인수 match_type으로 매칭타입 즉 "찾고자 하는 방식"인데 정확하게 일치하는 값을 찾고자 한다면 숫자 0을 입력합니다.
- 우리는 거의 대부분의 경우에 정확하게 일치하는 값을 찾기 때문에 거의 숫자 0을 입력하고 사용하신다고 보시면 됩니다.
- 세 번째 인수로 1 혹은 -1을 지정할 수 있지만 정렬과 같이 사용을 해야 하고 우리가 잘 사용하는 조건은 아닙니다.
- 숫자 1 혹은 생략은 보다 작거나 같은 값 중에서 최댓값을 찾아 준다(?)는데 말도 어렵고 기본적으로 lookup_array가 정렬이 된 상태에서 정확하게 사용이 가능하기 때문에 잘 사용을 하지 않습니다.
- 숫자 -1도 마찬가지로 어렵습니다. ^^
- 일부 억지로 사용을 하기도 하지만 명확하게 이해가 되지 않고 다른 함수로 대체가 가능하기 때문에 공부를 위한 목적이 아니면 그렇게 관심을 안 가지셔도 됩니다. ^^
- 이때 만약에 찾고자 하는 값이 1개 이상인 경우에는 첫 번째 값을 찾아 첫 번째 위치만을 반환하게 되니 사용상 주의 바랍니다.
3. Match 함수 사용해 보기
- 상기 예제의 사원현황에서 "강수빈" 사원을 찾아서 위치를 확인해 보도록 하겠습니다.
- 이렇게 위치를 찾는 이유는 "강수빈" 사원의 위치를 알면 동일 위치로 "강수빈" 사원의 "소재지"를 찾아올 수 있기 때문입니다.
- 첫 번째 인수는 찾고자 하는 "값"으로 "강수빈"이 입력되어 있는 B9 셀을 입력합니다.
- 두 번째 인수는 찾고자 하는 "범위"로 "성명" 리스트가 있는 "B3:B7"을 입력하고
- 마지막으로 정확하게 일치하는 매칭타입으로 숫자 0을 이렇게 3개의 인수를 차례대로 입력합니다.
- 그러면 결과로 지정된 범위(lookup_array)에서 "강수빈(lookup_value)"은 3번째에 위치한다는 것을 알 수 있습니다.
4. Index 함수의 구문 / 사용방법
- INDEX(array, row_num, [column_num])
- array 필수 요소입니다. 배열 상수나 셀 범위입니다.
- row_num column_num이 없는 한 필수입니다. 값을 반환할 배열의 행을 선택합니다.
- column_num 선택 요소입니다. 값을 반환할 배열의 열을 선택합니다.
- 엑셀 도움말인데 말이 역시 어렵네요. 배열 형식으로도 사용이 가능하기 때문인 것 같습니다. ㅠㅠ
- 도움말의 인수 설명은 꼭 한 번씩 읽어 보시기를 추천드립니다. 이해가 되지 않아도 영문 인수에 익숙해지시는 게 좋습니다.
- 함수 사용 시에 풍선도움말이라고 영문 인수가 보이는데 대충 눈치로 무슨 말인지 알 수 있는 쉬운 영어이기 때문에 익숙해지면 함수 작성에 도움이 됩니다.
- Index 함수는 Match 함수와 다르게 선택된 범위에서 지정한 위치의 "값(데이터)"을 가져오는 함수입니다.
- "이 범위(array)에서 3번째(row_num) 위치에 해당하는 값(데이터)을 자져와라"라는 느낌으로 사용을 하시면 됩니다.
- 첫 번째 인수 array는 찾고자 하는 셀의 "범위"를 의미합니다.
- 두 번째 인수 row_num는 몇 번째 값을 가져올 것인지 위치를 지정하는 "숫자"입니다.
- 세 번째 인수 column_num는 array의 셀 범위가 2차원인 경우에 사용하는 값으로 1차원의 (열 한 줄의 범위) 경우 입력하지 않고 생략합니다.
- 우리는 거의 1차원으로 사용하기 때문에 생략하고 다만 여러 개의 값(배열)을 가져오는 경우에는 사용할 수도 있습니다.
5. Index 함수 사용해 보기
- Match 함수에서 "강수빈" 사원이 3번째 위치한다는 것을 알았습니다.
- 이 위치 정보를 활용하여 "강수빈" 사원의 "소재지"를 찾아보도록 하겠습니다.
- 첫 번째 인수는 찾고자 하는 "범위"입니다. "소재지" 정보가 입력되어 있는 열의 셀 범위 "D3:D7"을 지정해 줍니다.
- 이때 정확한 위치를 찾기 위해서는 Match 함수에서 사용한 범위와 동일한 위치에 있는 동일한 개수를 사용해 주시기 바랍니다.
- 두 번째 인수는 찾고자 하는 "위치"로 Match 함수로 찾은 3이 입력된 C9 셀을 지정합니다.
- 그러면 결과로 "소재지" 범위에서 "강수빈"사원에 해당하는 3번째 위치의 "대전"이란 값을 찾을 수 있습니다.
6. Index, Match 함수로 원하는 값 찾기
- 지금까지 설명드린 방법이 Index/Match 함수를 사용하여 원하는 값을 찾아오는 방법이었습니다.
- Match 함수로 일치하는 값의 위치를 찾고 Index 함수로 해당하는 값을 찾아오는 방법입니다.
- Vlookup 함수도 Index/Match 함수와 동일한 원리로 원하는 값을 찾아오는 방식의 함수입니다.
- 이번에는 위치를 살짝 바꾸어 표의 왼쪽 편에 있는 데이터 값을, Vlookup 함수로는 작업이 불가능한 값을 찾아보도록 하겠습니다.
- Vlookup 함수는 먼저 찾고자 하는 열을 포함한 전체 범위를 지정하고 가장 왼편의 열에서 일치하는 값을 먼저 찾기 때문에 표의 왼편에 있는 값들은 찾을 수가 없지만
- 반면에 Index / Match 함수의 경우 Index 함수의 셀 범위와 Match 함수의 셀 범위를 각각 지정을 해주기 때문에
- 셀 범위의 크기와 위치만 같다면 표의 왼편, 오른편 구분 없이 어디에 있는 범위든지 적용이 가능하고 값을 찾아올 수 있는 장점이 있습니다.
- Vlookup 함수가 대단히 좋은 함수이지만 이러한 제한 때문에 어떤 분들은 처음부터 Index / Match 함수만을 사용하기도 합니다.
- 우리는 그때그때 상황에 맞게 우리가 쉽게 사용할 수 있는 함수를 사용하면 될 듯합니다.
- Indext, Match 함수는 2가지 식을 합쳐둔 형태이고 처음에는 전체 수식이 조금 어려울 수 있습니다.
- 전체 수식이 조금 어렵다고 느껴지신다면 Match 함수, Index 함수 각각의 수식으로 원하는 값을 나누어서 찾은 후에
- Match 함수의 수식을 복사해서 Index 함수의 해당 위치에 붙여 넣기를 하시면 됩니다.
- 실제로 복잡한 수식은 나누어서 작성하고 마지막에 합치는 방법을 많이들 사용하고 있습니다.
7. Index, Match 함수로 여러 개의 값 가져오기 - 배열 수식
- Index, Match 함수로 조건을 만족하는 한 개의 값을 찾아오는 방법을 배웠습니다.
- 보통은 Index 함수에서 한 개의 열만 지정해서 하나의 데이터만을 가져오지만
- Index 함수는 여러 열을 지정해서 한 번에 여러 개의 데이터를 가져오는 것도 가능합니다.
- 이 때는 Index 함수에서 가져오는 ① 셀의 범위는 여러 개의 열로 지정해주고 ② 배열 수식으로 입력을 해주어야 합니다.
- 그리고 ③ row_num, column_num 인수 모두를 입력해주셔야 하는데
- column_num 인수의 경우 그냥 콤마( , )만 찍어 주고 인수값을 입력하지 않거나 0을 입력하게 되면 지정된 범위 전체 열의 데이터를 한 번에 가져올 수 있습니다.
- 다만 배열 수식을 사용하기 위해서 수식 입력의 마무리는 "Enter" 대신에 "Ctrl + Shift + Enter" 입력으로 마무리해줘야 하고
- 이렇게 입력을 하게 되면 입력된 수식에 중괄호 ( " { } " ) 표시가 되게 되며 우리는 중괄호 표시를 보고 배열 수식임을 알 수 있습니다.
- ① Match 함수는 기존과 동일하게 "강수빈(B10)"을 "성명 범위(B3:B7)"에서 해당 위치를 찾아 줍니다.
- ② Index 함수를 배열로 적용하고 2개의 열(근속년수, 소재지)에서 데이터를 가져오기 위해서
- 수식 입력범위는 "근속년수/소재지"가 모두 입력될 수 있도록 2개의 셀("C13:D13")을 선택을 합니다.
- ③ 이렇게 입력될 2개의 셀을 모두 선택한 상태에서 Index 함수를 입력하는데
- 이 경우 "수식 입력줄"에서 입력을 해주시면 2개의 셀이 모두 선택된 상태에서 입력하기가 용이합니다.
- ④ 수식 "= index(C3:D7, C10, )으로 첫 번째 인수로 "근속년수, 소재지"에 해당하는 2개 열의 범위를 모두 선택하고, Match 함수에서 찾은 위치를 두 번째 인수로 입력합니다.
- ⑤ 그리고 세 번째 인수는 입력을 하지는 않지만 반드시 인수구분자 콤마( , )를 찍어 주셔야 합니다.
- 그래야 세 번째 인수가 있고 세 번째 인수는 생략이 되었다고는 것을 엑셀이 알 수 있습니다.
- ⑥ 마지막으로 "Ctrl + Shift + Enter"로 배열 수식으로 입력을 마무리합니다.
- 이렇게 입력하는 방법이 "배열의 다중 셀 입력" 방식입니다. 배열을 여러 셀에 보이도록 입력한다는 의미입니다.
- 엑셀 배열수식에 대해 경험이 없으시다면 그냥 이런 게 있다 정도로 이해를 하시고 넘어가셔도 됩니다. ^^
8. Index, Match 함수로 다중 조건으로 찾기 - 배열 수식
- 일단 다중 조건이라는 말이 들어가면 A도 만족하고 B도 만족하는 And 조건을 말하며 이를 구현하기 위해서는 배열 수식이 필요합니다.
- 배열 수식에 대한 지식이나 경험이 없으신 분은 그냥 참조를 하시고 배열 수식에 대해서 조금 공부해 보실 것을 추천드립니다.
- 여러 조건에 해당하는 값을 찾는 데 있어서 "핵심은 배열수식으로 입력하는 Match 함수"입니다.
- 일단 ① Match 함수를 배열 수식으로 사용해서 다중 조건을 만족하는 행의 위치를 찾은 다음에
- ② Index 함수의 일반적인 기능을 사용해서 해당 위치의 데이터를 가져오는 방식입니다.
- Match 함수에서 배열 수식은 ("소속" = "2팀") And ("성명" = "강수빈")으로 배열 곱하기로 수식을 적용합니다.
- (소속 = "2팀")*(성명 = "강수빈") = {False ; False ; True ; Fasle ; False} * {True ; False ; True ; False ; False }가 되고
- 최종적으로 { 0 ; 0 ; 1 ; 0 ; 0 }의 배열 값을 가지게 됩니다.
- 이는 엑셀에서는 True = 숫자 1, False = 숫자 0으로 취급이 되고 배열 곱하기에서 0 * 0 = 0 * 1 = 0 (False, 거짓)이 되고
- 1 * 1 = 1 (True, 참)이 되는 방식으로 계산이 되기 때문입니다. ㅠㅠ
- 그리고 우리는 최종 배열 값에서 숫자 1의 위치를 Match 함수로 찾아오면 되는 방식입니다.
- 조금 복잡하고 어려울 수 있는데 가볍게 읽어 보시기 바랍니다.
- 이렇게 해당 위치를 Match 함수로 찾았다면 Index 함수를 사용해서 위치에 해당하는 원하는 값을 가져올 수 있습니다.
- 이런 방식이면 2가지 조건뿐만 아니라 3가지, 4가지 원하는 조건을 찾는 것이 모두 가능합니다.
- Match 함수에서 배열 수식을 (범위1 = 조건1)*(범위2 = 조건2)*(범위3 = 조건3)*, …. 이렇게 이론상 얼마든지 가능하기 때문입니다. ^^
- 다시 얘기를 드리지만 배열 수식은 아직은 초보분들에게는 다소 어려운 개념입니다.
- 배열수식을 알아야 하지만 일반 함수에 대한 지식도 있어야 잘 사용할 수 있는 영역이라서 시간이 조금 필요합니다.
- 오늘은 Index , Match 함수를 이용해서 원하는 조건에 해당하는 데이터를 찾는 방법을 알아보았습니다.
- 위치를 찾아서 (Match 함수), 그 위치에 해당하는 데이터를 가져온다 (Index 함수)라는 원리이기 때문에
- 일단 이런 개념을 익히시면 다양하게 응용이 가능한 방식이고 최신 엑셀을 사용하시는 분이라면 반드시 Xlookup 함수를 사용하시는 것을 추천드립니다.
- 수고 많으셨습니다.
** 핵심 요약 : Index, Match 함수 사용법 - 원하는 값 찾기, 다중조건, 배열함수
1. 함수 설명
- ① Match 함수가 일치하는 데이터의 위치를 찾아오고
- ② Index 함수가 위치를 기반으로 원하는 값(데이터)을 찾아오는 방식으로 작동한다.
2. Match 함수의 구문 / 사용방법
- MATCH(lookup_value, lookup_array, [match_type])
- lookup_value 필수 요소입니다. lookup_array에서 찾으려는 "값"입니다.
- lookup_array 필수 요소입니다. 검색할 셀 "범위"입니다.
- match_type 선택 사항입니다. 숫자 -1, 0 또는 1입니다.
- 세 번째 인수는 match_type으로 매칭타입 즉 "찾고자 하는 방식"인데 정확하게 일치하는 값을 찾고자 한다면 숫자 0을 입력한다.
3. Match 함수 사용해 보기
- 사원현황에서 "강수빈" 사원을 찾아서 위치를 확인해 보자
- 첫 번째 인수는 찾고자 하는 "값"으로 "강수빈" 혹은 "강수빈"이 입력된 셀 주소를 입력한다.
- 두 번째 인수는 찾고자 하는 "범위"로 "성명" 정보가 입력된 열의 셀 범위를 지정해 준다.
- 마지막으로 정확하게 일치하는 매칭타입으로 숫자 0을 입력하면 결과로 3번째 위치한다는 것을 알 수 있다.
4. Index 함수의 구문 / 사용방법
- INDEX(array, row_num, [column_num])
- array 필수 요소입니다. 배열 상수나 셀 범위입니다.
- row_num column_num이 없는 한 필수입니다. 값을 반환할 배열의 행을 선택합니다.
- column_num 선택 요소입니다. 값을 반환할 배열의 열을 선택합니다.
- "여기 이 범위(array)에서 몇 번째(row_num) 위치한 값을 가져온다"는 느낌으로 사용하면 된다.
5. Index 함수 사용해 보기
- Match 함수에서 찾은 "강수빈" 사원이 3번째 위치한다는 정보를 활용하여 강수빈 사원의 "소재지"를 찾아보자.
- 첫 번째 인수는 찾고자 하는 범위로, "소재지" 정보가 입력되어 있은 열의 셀 범위를 지정해 준다.
- 두 번째 인수는 찾고자 하는 위치로 Match 함수로 찾은 위치정보 3을 입력한다.
- 그러면 결과로 "소재지" 범위에서 "강수빈"사원에 해당하는 3번째 위치인 "대전"이란 값을 찾을 수 있다.
6. Index, Match 함수로 원하는 값 찾기
- Match 함수로 일치하는 값의 위치를 찾고 Index 함수로 해당하는 값을 찾아오는 방법이다.
- Index / Match 함수의 경우 Index 함수의 셀 범위와 Match 함수의 셀 범위를 각각 지정을 해주기 때문에
- 셀 범위의 크기와 위치만 같다면 표의 왼편, 오른편 구분 없이 어디에 있는 범위든지 적용이 가능하고 값을 찾아올 수 있는 장점이 있다.
7. Index, Match 함수로 여러 개의 값 가져오기 - 배열 수식
- Index 함수는 여러 열을 지정해서 한 번에 여러 개의 데이터를 가져오는 것도 가능하다.
- 가져오는 ① 셀의 범위를 여러 개의 열로 지정해주고 ② 배열 수식으로 입력을 해주어야 한다.
- 이 때는 ③ row_num, column_num 인수 모두를 입력해주셔야 하는데
- column_num 인수의 경우 그냥 콤마( , )만 찍어 주고 인수값을 입력하지 않거나 0을 입력하게 되면 지정된 범위 전체 열의 데이터를 한 번에 가져올 수 있다.
8. Index, Match 함수로 다중 조건으로 찾기 - 배열 수식
- 일단 Match 함수를 배열 수식으로 사용해서 다중 조건을 만족하는 행의 위치를 찾은 다음에
- Index 함수의 일반적인 기능을 사용해서 해당 위치의 데이터를 가져오는 방식이다.
- Match 함수에서 배열 수식은 ("소속" = "2팀") And ("성명" = "강수빈")으로 배열 곱하기로 수식을 적용한다.
- 그리고 우리는 최종 배열 값에서 숫자 1의 위치를 Match 함수로 찾아오면 되는 방식이다.
- Index, Match 함수는 위치를 찾아서 (Match 함수), 그 위치에 해당하는 데이터를 가져온다 (Index 함수)라는 원리이다.
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글