안녕하세요. 행아아빠입니다.
이번 엑셀 내용에서 확인해볼 내용은 index함수와 match함수입니다.
만약 여러분들이 Vlookup함수는 활용을 잘 하는데, Vlookup함수로 원하는 결과값을 계산할 수 없거나, 데이터의 가로, 세로를 참조하여 서로 만나는 지점의 결과값을 가져오고자 하는경우, 또는 날짜, 시간, 숫자등의 일정한 구간에 해당하는 정해져있는 결과값을 가져오고자 하는경우와 같이 다양한 형태로 변형해서 사용할 수 있는 함수와 수식이 될 수 있을것 같습니다.
index함수와 match함수를 활용해서 작성할 수 있는 수식들을 몇가지 예제들을 통해서 확인해 보도록 하겠습니다.
■ 행아아빠의 Youtube 채널.
● 주요 컨텐츠 주제 - 내일은로또왕 / 내일은엑셀왕
● Youtube 채널 주소 - https://www.youtube.com/@economic-freedom
■ 행아아빠 후원 안내.
● Youtube 채널 맴버십 후원 - https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join
● Youtube 댓글 후원 - Youtube 영상 댓글에서 "Super Thanks" 구매 후 댓글 작성.
● 내일은엑셀왕 대표 Youtube 영상 - https://youtu.be/oYVZHgv_sKM (2023년 급여 자동 계산 서식)
1. index함수와 match함수 수식 구문.
1-1. index함수 수식 구문.
=index(array, row_num, [column_num])
● array : 결과값 데이터가 포함되어 있는 범위.
● row_num : 행 번호. (결과값 데이터가 포함되어 있는 범위에서 시작 되는 행이 1행.)
● [column_num] : 열 번호. (결과값 데이터가 포함되어 있는 범위에서 시작 되는 첫열이 1열.)
1-2. match함수 수식 구문.
=match(lookup_value, lookup_array, [match_type])
● lookup_value : 찾을값 또는 찾을 조건.
● lookup_array : 조건을 찾을 범위.
● [match_type] :
① 1 또는 생략 : 찾을 조건과 유사한 값. (조건의 값보다 작거나 일치하는 데이터값 중에서 큰값을 찾아서 반환.)
② 0 : 찾을 조건과 정확히 일치 하는 데이터값.
③ -1 : 찾을 조건과 유사한 값. (조건의 값보다 크거나 일치하는 데이터값 중에서 작은값을 찾아서 반환.)
2. index함수와 match함수의 예제와 수식 작성 방법.
index함수와 match함수의 수식 활용은 굉장히 다양한 형태의 데이터에 활용되기 때문에 이번 내용에서 모두 소개할 수 있을지는 모르겠습니다만, 여러가지 형태의 데이터의 예제에서 어떻게 활용해서 수식을 작성하는지 확인해 보도록 하겠습니다.
2-1. index함수 이해.
예제 이미지에서 보는것과 1행에는 번호, A열에는 이름을 구분해서 1반 A이름에 해당 하는 데이터는 1행 1열이라고 했을때 "1행 1열"이라는 데이터는 전체 데이터에서 2행 2열에 입력된 데이터입니다.
여기서 번호와 A열, 다시 말해서 행과 열을 참조해서 만나는 지점의 데이터들을 결과값으로 가져오고자 하는것일때 index함수로 결과값을 가져오기 위해서는 =index($B$2:$E$6,행번호,열번호)로 작성할 수 있습니다.
결과값을 가져오기 위한 범위 B2셀부터 E6셀에서 1행 1열의 데이터를 결과값으로 가져오기 위해서는 =index($B$2:$E$6,1,1)와 같이 작성하면 "1행 1열"의 데이터값을 가져올수가 있습니다.
전체 데이터에서 "1행 1열"이라는 데이터값은 2행 2열에 입력된 데이터값이지만 index함수에서 결과값을 가져오기 위한 데이터 범위를 B2셀부터 E6셀까지로 지정하고 있기 때문에 2행2열의 B2셀이 1행 1열이 되는것입니다.
그럼 E6셀의 "5행 4열"의 데이터를 가져오려면 index함수의 수식을 어떻게 작성해야 할까요.
"5행 4열"의 데이터는 전체 데이터 범위 A1셀부터 E6셀에서는 6행 5열에 입력된 데이터이지만, index함수에서 결과값을 가져오고자 하는 범위를 B2셀부터 E6셀까지로 지정했기 때문에 B2셀을 1행 1열의 기준점으로부터 E6셀은 5행 4열의 지점만큼 떨어져 있기 때문에 =index($B$2:$E$6,5,4)와 같이 수식을 작성 할 수 있습니다.
index함수에서 결과값을 가져오기 위한 데이터의 범위의 시작을 1행 1열의 기준점으로 한다는것을 기억 하시면 좋을것 같습니다.
2-2. match함수 이해.
match함수는 참조 범위 안에서 조건을 만족하는 데이터가 몇번째에 위치해 있는지를 계산할 수 있습니다.
A1셀부터 E1셀의 범위안에서 1번 번호가 입력되어 있는 위치는 2번째 위치이고, A1셀부터 A6셀의 범위에서 D가 입력된 위치는 5번째 위치에 D가 입력되어 있는것을 확인할 수 있습니다.
match함수 역시 index함수와 마찬가지로 설정한 범위에서 시작되는 첫번째 셀을 첫번째 위치로 반환하고 있습니다.
따라서 B2셀에 입력된 "1행 1열"이라는 데이터값을 첫번째 자리에 위치한 결과값으로 반환한다고 하면 =match("1행 1열",$B$2:$E$2,0)와 같은 수식으로 "1행 1열" 찾을 조건을 B2셀부터 E2셀 범위안에서 정확히 일치 하는 결과값을 찾으라는 수식을 작성해야 1의 결과값을 얻을수가 있습니다.
만약 해당 수식을 =match("1행 1열",$A$2:$E$2,0)와 같이 참조 하는 범위를 A2셀부터 E2셀로 변경하고 "1행 1열"의 데이터와 정확히 일치 하는 결과값을 확인한다면 2라는 결과값을 얻을 수 있습니다.
참조 하는 범위가 행이 아닌 열을 참조하더라도 참조되는 열범위에서 처음 시작되는 참조셀이 1이 되는것입니다.
그러나 참조 범위를 1행, 1열의 범위가 아닌 그 이상의 범위 B2셀부터 E3셀까지의 범위 B2:E3으로 설정하게 되면 오류값을 반환하게 되는데, match함수의 참조 범위는 1개의 행 범위 또는 1개의 열 범위로 설정해 줘야 한다는것을 기억해 주시길 바랍니다.
3. index함수와 match함수를 활용한 수식과 Vlookup함수, Lookup함수 비교 작성 예제.
index함수와 match함수의 기본적인 수식 구분을 작성해 봤는데, index함수와 match함수를 단독 함수로 사용하는것 보다는 기타 함수와 결합되었을때 그 활용도가 굉장히 다양한 형태의 데이터에 적용할 수 있을 뿐만 아니라 index함수와 match함수가 서로 결합된 형태의 수식만 작성할줄 알더라도 다양한 형태의 데이터에서 원하는 결과값을 얻고자 하는 수식으로 작성할 수 있습니다.
index함수와 match함수가 서로 결합된 형태의 예제들을 살펴 보면서 여러분들이 index함수와 match함수를 이해하는데 도움이 될 수 있기를 바라겠습니다.
3-1. 예제1. Vlookup함수, Lookup함수, Index,match함수 수식 결과값 비교.
index함수와 match함수로 수식을 작성할 수 있는 간단한 형태의 데이터로 결과값을 계산 하는 수식을 작성해 보고자합니다.
예제 이미지를 보면 참조할 데이터 범위는 F2셀부터 G5셀까지 데이터를 참조해서 A열에 번호를 입력하면 A열에 입력한 번호와 F열의 번호가 서로 정확히 일치 하는 G열의 데이터값을 결과값으로 표기하고자 하는것입니다.
B열에는 Vlookup함수, C열에는 Lookup함수, D열에는 index, match함수로 수식을 각각 작성해서 결과값을 확인해 보도록 합니다.
| 1. B열 - Vlookup함수 수식. =VLOOKUP($A2,$F$2:$G$5,2,FALSE) 2. C열 - Lookup함수 수식. =LOOKUP(1,1/($F$2:$F$5=$A2),$G$2:$G$5) 3. D열 - index, match함수 수식. =INDEX($G$2:$G$5,MATCH($A2,$F$2:$F$5,0)) |
예제1 데이터는 일반적으로 결과값을 가져오기 위해 사용할 함수로 Vlookup함수로 사용할 가능성이 많습니다.
하지만 Lookup함수, Index, match함수로도 그 결과값을 가져오는데 충분히 활용할 수 있는 수식이고, 비슷한 형태의 데이터에서 오히려 Vlookup함수로는 결과값을 계산할 수 없는 수식을 Lookup함수 또는 index, match함수로 결과값을 계산하는 수식을 작성 할 수 있는 경우가 많기 때문에 Vlookup함수보다도 Lookup함수와 index, match함수의 활용도의 범위가 많다고 할 수 있습니다.
3-2. 비교할 조건이 결과값을 가져올 범위보다 뒷열에 데이터가 입력된 경우 수식 작성.
참조할 데이터를 F열과 G열을 서로 바꿨을때 A열에 입력한 번호와 G열의 번호를 비교해서 정확히 일치하는 F열의 데이터값을 결과값으로 가져오고자 하는 경우에는 Vlookup함수로는 그 결과값을 가져올수가 없지만, Lookup함수와 index, match함수로는 결과값을 충분히 가져올 수 있는것을 볼 수 있습니다.
그 차이점으로 Vlookup함수는 결과값을 참조 범위가 시작되는 첫번째 열을 조건과 비교할 범위로 인식하기 때문에 조건을 비교할 범위가 데이터를 가져올 범위보다 뒤에 있다면 그 결과값을 가져올 수 없지만, lookup함수와 index, match함수는 결과값을 가져올 범위, 조건을 비교할 범위를 각각 지정해주기 때문에 비교할 조건이 데이터를 가져올 범위보다 뒷열에 있더라도 결과값을 계산해서 표기할 수 있습니다.
3-3. 비교할 조건을 행과 열에서 각각 일치 하는 값을 찾아서 행과 열이 서로 만나는 지점의 데이터를 결과값으로 가져오는 경우 수식 작성.
예제1의 데이터에서 비교할 조건을 행 방향으로 입력한 번호뿐만 아니라 2행에 입력한 A, B, C의 조건을 열방향으로 조건을 비교해서 행과 열이 만나는 지점의 데이터값을 결과값으로 가져오고자 한다면 어떤 함수로 어떤 수식을 작성 할 수 있는지 확인해 보겠습니다.
참조할 범위는 F2셀부터 I6셀까지 범위 안에 입력된 데이터 범위안에서 행방향의 번호와 열방향의 A, B, C의 데이터값이 일치하는 데이터를 G3셀부터 I6셀 범위에서 결과값을 가져오고자 하는 경우, 행과 열의 조건을 모두 비교해야 하는데 Vlookup함수와 Lookup함수는 행방향의 조건은 비교할수 있지만 열방향으로 입력된 조건을 비교할 수 없고, 결과값을 가져와야 하는 범위가 1개의 열이 아닌 다중범위 안에서 행과 열이 만나는 지점의 데이터를 가져와야 하는것이기 때문에 그 결과값을 가져올 수 없습니다.
따라서 행번호와 열번호를 인수로 수식을 작성하는 index함수와 참조범위 안에서 조건과 일치하는 데이터가 N번째 위치하는지 알 수 있는 match함수를 활용해야만 그 결과값을 가져올수 있습니다.
D3셀에 index, match함수의 수식을 작성 하면 다음과 같이 수식을 작성 할 수 있습니다.
| D3셀 - A3셀 숫자1이 F열에서 일치하고, D2셀 C가 G2셀부터 i2셀까지 중에서 일치하는 열을 찾아서 서로 만나는 지점의 데이터값을 결과값으로 반환하는 수식. =INDEX($G$3:$I$6,MATCH($A3,$F$3:$F$6,0),MATCH($D$2,$G$2:$I$2,0)) |
결과값을 가져올 범위는 G3셀부터 I6셀까지이기 때문에 결과값을 가져올 범위 index(G3:G6)을 범위로 지정하고, index함수의 수식 구문에서 비교할 조건의 순서가 행번호, 열번호 순서로 조건을 만족하는 결과값을 가져오기 때문에 행방향의 조건과 일치 하는 N번째 행번호를 찾기 위해서 MATCH($A3,$F$3:$F$6,0)를 인수로 작성하고, C의 데이터가 N번째 열에 입력되어 있는지 열번호를 찾기 위해서 MATCH($D$2,$G$2:$I$2,0)를 열번호 인수로 작성하면 1번과 C의 데이터가 만나는 지점에 입력된 I3셀의 데이터값 9를 결과값으로 가져올 수 있는것 입니다.
4. index, match함수 구간 사이에 해당하는 결정값을 결과값으로 가져오는 예제와 수식 작성.
index, match함수는 vlookup함수와 lookup함수와 비슷한 형태로 활용할수 있다는것을 확인해 봤고, Vlookup함수와 Lookup함수로는 결과값을 계산할 수 없는 형태의 데이터를 참조 하는 방법도 함께 확인해 봤습니다.
4-1. 데이터값과 데이터값 사이의 구간에서 결정되는 데이터값을 결과값으로 가져오는 수식 작성.
이번 예제에서는 index, match함수로 데이터값과 데이터값 사이에 해당할 경우 결정된 데이터값이 있을 경우 데이터값과 데이터값 사이의 구간에 해당할때 결정된 데이터값을 가져오는 예제로 행과 열의 조건을 모두 참조해야 하는 수식을 작성해 보도록 하겠습니다.
예제와 같이 비교할 조건이 1~1650 사이의 값일 경우에는 1650을 결과값으로, 1651~1750 사이의 값일 경우에는 1750으로 결과값을 가져오고자 하는것과 같이 데이터값과 데이터값 사이의 구간에 해당한다면 해당 구간에서 결정된 결정값을 결과값으로 가져 오기 위해서 다음과 같이 수식을 작성할 수 있습니다.
| F2셀 - A2셀부터 C6셀의 범위 안에서 E2셀 조건 1790이 해당하는 구간의 결정값을 F2셀에 결과값으로 가져오기. =INDEX($C$2:$C$6,MATCH($E$2,$A$2:$A$6,1)) |
E2셀의 조건 1790의 수치는 참조범위 A2셀부터 C6셀의 범위 안에서 4행의 데이터값 구간 1751~1850 사이에 해당하는 수치로 해당 수치안에 포함될경우 C열의 1850을 결과값으로 가져오는것입니다.
따라서 결과값을 가져오고자 하는 범위는 C열로 C2셀부터 C6셀까지이고, 열번호를 구하기 위한 참조 범위는 A열 또는 B열이 되겠는데 1790의 조건은 정확히 일치 하는 조건을 찾는것이 아니고 어느 구간에 해당하는지 찾기 위한것으로 유사한값을 결과값으로 반환하는 수식으로 작성해야 합니다.
따라서 index함수에서 행번호를 구하기 위한 인수를 match함수로 작성하는데, match함수에서 유사값, 정확히 일치하는값의 Type인수를 0이 아닌 1또는 -1로 작성해 주어야 합니다.
그런데 Type인수를 1로 작성해 준것은 A열의 데이터가 1, 1651, 1751...와 같이 오름차순으로 정렬되어 있기 때문에 오름차순으로 정렬된 데이터중에서 작거나 같은값중 큰값을 결과값으로 반환 하기 위해서 Type인수를 1로 작성할 수 있는것입니다.
4-2. 데이터값과 데이터값 사이의 구간에서 결정되는 데이터값을 행과열을 참조해서 결과값으로 가져오는 수식 작성.
예제 이미지에서 보는것과 같이 열방향으로 C2셀부터 G2셀까지 1650, 1750, 1850, 1950, 2050의 폭 데이터와 행 방향으로 B3셀부터 B5셀까지 900, 950, 1000이라는 데이터 길이를 각각 오름차순으로 정렬되어 있을때 각각의 행과 열에 해당하는 데이터를 C3셀부터 G5셀안에 입력된 데이터하고 했을때 폭은 1850이고, 길이가 950의 결과값은 행과 열이 서로 만나는 E4셀의 데이터 90,000을 결과값으로 가져올 수 있습니다.
폭과 길이의 조건보다 큰값을 결과값하는 행과 열의 결과값을 반환하는 수식을 작성한다고 했을때 폭은 1790, 길이는 940이라고 해보겠습니다.
1790폭보다 큰폭 1850폭이 선택되어야하고, 길이 940보다 큰 950을 선택해서 폭과 길이가 서로 만나는 지점의 데이터는 E4셀의 데이터값 90,000을 결과값으로 가져와야합니다.
따라서 조건을 비교해서 찾고자 하는 결과값이 정확히 일치하는값이 아니고, 유사값을 결과값으로 반환하고, 데이터값과 데이터값 사이의 구간에 해당하는 결정된 데이터값을 결과값으로 반환해서 행과 열의 결과값이 서로 만나는 지점을 최종 결과값으로 가져오고자 하는 문제입니다.
단순히 원본데이터 그대로 수식을 작성하면 다음과 같이 작성할 수 있습니다.
| =INDEX($C$3:$G$5,MATCH($C$8,$B$3:$B$5,1),MATCH($B$8,$C$2:$G$2,1)) |
하지만 해당 수식으로 원하는 결과값 90,000이 아닌, 30,000을 결과값으로 반환하게 되는데 match함수의 type인수 1은 비교 데이터가 오름차순으로 정렬되어 있을때 조건과 비교하여 작거나 같은값중에서 큰값을 결과값으로 반환 하기 때문에 1790은 1650, 1750, 1850, 1950, 2050의 데이터와 비교했을때 작거나 같은값은 1750이기 때문에 폭은 1750으로 결정되고 길이 940은 900, 950, 1000과 비교했을때 900의 결과값의 반환하기 때문에 폭 1750, 길이 900에 해당하는 데이터값 30,000을 반환하게 되는것입니다.
따라서 이러한 경우에는 폭과 길이를 결정하는 구간값에서 결정되는 결정값을 먼저 계산하고 폭과 길이에서 정해진 결과값을 행번호, 열번호의 인수로 작성해서 다시 한번 행과 열을 참조하는 index, match함수로 수식을 작성해야 합니다.
조건의 폭과 길이의 구간에 해당하는 결과값을 계산하는 수식과 폭과 길이의 결과값으로 C3셀부터 G5셀의 범위에 해당하는 데이터값을 가져오는 수식을 작성해 보면 다음과 같이 작성할 수 있습니다.
| 1. 길이 조건 결과값을 계산 하는 수식. =INDEX($B$3:$B$5,MATCH($C$8,$A$3:$A$5,1)) 2. 폭의 조건 결과값을 계산 하는 수식. =INDEX($C$2:$G$2,MATCH($B$8,$C$1:$G$1,1)) 3. 1번과 2번에서 결정된 길이와 폭으로 행과 열을 참조하는 결과값 계산 하는 수식. =INDEX($C$3:$G$5, MATCH(INDEX($B$3:$B$5,MATCH($C$8,$A$3:$A$5,1)),$B$3:$B$5,0), MATCH(INDEX($C$2:$G$2,MATCH($B$8,$C$1:$G$1,1)),$C$2:$G$2,0)) |
1번의 수식은 길이의 조건이 어느 구간에 해당하는지 확인하고 해당하는 구간에서 결정되는 길이값을 B열의 데이터값으로 결과값을 가져오고, 2번의 수식은 폭의 조건이 어느 구간에 해당하는지 확인하고 해당하는 구간에서 결정되는 폭 2행의 데이터값으로 결과값을 가져오기 위한 수식을 먼저 작성해 본것입니다.
따라서 폭 1790은 2번의 수식으로 1850이라는 결과값으로 계산하고, 길이 940은 1번의 수식으로 950이라는 결과값으로 계산됩니다.
다시 한번 index, match함수로 수식을 작성하게 되면 1번 수식은 행번호를 반환하는 인수가 되고, 2번 수식은 열번호를 반환하는 인수로 작성해서 1850폭과 길이 950에 해당하는 데이터값 90,000을 결과값으로 가져올 수 있습니다.
5. index, match함수의 match인수를 참조하는 비교 조건이 여러개일때 다중조건 비교조건으로 수식 작성 하는 방법.
예제에서 보는것과 같이 공정, 규격, 수량, 단위에 따라 단가가 입력된 A열부터 E열의 원본 데이터에서 G열부터 J열에 공정, 규격, 수량, 단위를 입력했을때 입력된 조건 공정, 규격, 수량, 단위와 정확히 일치 하는 데이터의 단가를 K열에 결과값으로 계산하여 가져오고자 하는경우 index, match함수를 활용해서 어떻게 수식을 작성하는지 확인해 보도록 하겠습니다.
사실 해당 수식은 Lookup함수로 그 결과값을 가져오는것이 효율적인 방법일수 있지만, index, match함수의 수식을 다양한 방법으로 활용하고자 하는 의미에서 Lookup함수와 함께 수식을 작성해서 비교해 보도록 하겠습니다.
| 1. K열 - Lookup함수로 작성한 수식. =LOOKUP(1,1/(($A$2:$A$7=$G2)*($B$2:$B$7=$H2)*($C$2:$C$7=$I2)*($D$2:$D$7=$J2)),$E$2:$E$7) 2. K열 - index, match함수로 작성한 수식. {=INDEX($E$2:$E$7,MATCH(1,($A$2:$A$7=$G2)*($B$2:$B$7=$H2)*($C$2:$C$7=$I2)*($D$2:$D$7=$J2),0))} |
작성된 수식을 확인해 보면 Lookup함수와 index, match함수의 수식 구문에 의한 인수 작성의 차이를 제외한, 공정, 규격, 수량, 단위의 조건과 일치 하는 데이터를 찾기 위해 작성한 인수 작성 형태가 비슷한것을 알 수 있고, 가장 큰 이차이점으로는 Lookup함수는 일반수식으로, index, match함수는 배열수식으로 작성되었다는것입니다.
배열수식은 작성된 수식의 종료를 Ctrl+Shift+Enter키를 누르면 수식의 가장 앞부분과 뒷부분에 "{}" 중괄호 기호가 자동으로 생성되는것을 확인할 수 있습니다.
만약 2번의 수식을 배열수식으로 작성하지 않고 일반수식 =INDEX($E$2:$E$7,MATCH(1,($A$2:$A$7=$G2)*($B$2:$B$7=$H2)*($C$2:$C$7=$I2)*($D$2:$D$7=$J2),0))으로 작성한다면 그 결과값은 오류를 반환하거나 엉뚱한 결과값을 반환할 수 있습니다.
여기까지 Vlookup함수, Lookup함수, index, match함수로 그 결과값이 어떻게 나오는지 그리고 어떠한 형태의 데이터 서식에서 어떤 함수를 적용할 수 있는지 확인해 보았습니다.
가장 큰 차이점은 Vlookup함수는 단일조건과 행번호를 참조하고 찾고자 하는 조건이 찾고자 하는 참조범위에서 가장 첫번째열에 작성이 되어 있어야 한다는것이고 Lookup함수는 Vlookup함수에서 찾고자 하는 조건이 여러개일때 대안으로 활용할 수 있는 함수이면서 찾고자 하는 조건의 참조 범위 위치에 구속받지 않는다는것으로 볼 수 있고, 마지막으로 index, match함수는 vlookup함수와 lookup함수로는 찾을 수 없는 행과 열을 모두 참조해서 조건과 비교하고 그 결과값을 가져온다는데서 차이점이 있다고 할 수 있습니다.
실제로 여러분들의 업무에서 다양한 형태로 함수를 적용해 보시길 바라면서 오늘 내용은 여기서 마치도록 하겠습니다. 감사합니다.
Blog : https://hang-a-fafa.tistory.com
Youtube : https://www.youtube.com/@economic-freedom
Naver 까페 : https://cafe.naver.com/hatime
'엑셀' 카테고리의 다른 글
| 엑셀 날짜와 시간대별 예약 관리 현황 서식과 수식 작성 방법. (1) | 2023.03.02 |
|---|---|
| 엑셀 급여 계산 사용자 조건에 맞는 급여 계산 서식 작성 방법. (0) | 2023.02.27 |
| 엑셀 자주 사용하는 함수 - sumif함수, sumifs함수. (0) | 2023.02.22 |
| 엑셀 자주 사용하는 함수 - Countif함수, Countifs함수. (0) | 2023.02.20 |
| 엑셀 자주 사용 하는 함수 - Vlookup함수, Lookup함수 (0) | 2023.02.19 |
댓글