Excel : index & match (특정 셀의 값 얻어오기)

 

 

 

index(), match() function은 거의 동시에 쓰이기 때문에 같이 다뤄보겠습니다.

 

 

먼저 index() 함수입니다.

 

Syntax

index(range, row_no, column_no)

 

index 함수는 총 3개의 인자를 받습니다.

 

- range

셀의 범위입니다.

 

- row_no

행(row) 번호입니다. 여기서 말하는 행 번호는 엑셀 시트의 행 번호가 아니라 지정된 range 내에서 '몇 번째 행'인지를 나타내는 번호입니다.

 

- column_no

열(column) 번호입니다. 여기서 말하는 열 번호는 엑셀 시트의 열 번호가 아니라 지정된 range 내에서 '몇 번째 열'인지를 나타내는 번호입니다.

 

index 함수는 이렇게 3개의 인자를 받아

지정된 range 내에서 (row_no, column_no) 위치에 있는 셀의 값을 return합니다.

 

 

 

 

위 이미지를 봅시다.

B2:D9 범위에 데이터가 입력되어있고 이 범위에 대해 index 함수를 적용할겁니다.

 

B12 셀에는 index함수가 입력되어있고 id라는 값이 표시되고 있습니다.

대체 왜 id라는 값이 표시되는지 해석해봅시다.

 

 

=INDEX(B2:D9, 1, 1)

 

B12 셀에 적힌 수식은 위와 같습니다.

 

첫 번째 인자는 범위를 적으면 된다고 했습니다.

 

B2:D9가 적혀있습니다.

 

그리고 1, 1이 적혀있는데 각각 row_no, column_no를 의미합니다.

 

즉 이 index 함수는 B2:D9 범위에서 (1, 1) 위치에 있는 값을 출력하라는 의미입니다.

 

row_no = 1 -> 이것의 의미는 첫 번째 행을 의미합니다.

column_no = 1 -> 이것의 의미는 첫 번째 컬럼을 의미합니다.

 

B2:D9 범위 안에서 첫 번째 행, 첫 번째 컬럼 위치는 바로 B2 셀이 되므로 B2 셀에 있는 값인 id라는 값을 return하는 것이죠.

주의할 점은 엑셀 전체에 대해서 첫 번째 행, 첫 번째 컬럼이 아니라 지정된 범위 내에서 첫 번째 행, 첫 번째 컬럼을 의미합니다.

 

 

 

 

 

 

 

 

다른 예시입니다.

row_no, column_no를 바꿔보았습니다.

 

 

 

=INDEX(B2:D9, 5, 2)

 

수식을 해석해보면

B2:D9 범위 내에서 (5, 2) 위치에 있는 값을 return하라는 의미입니다.

 

B2:D9 범위 내에서 (5, 2)의 위치 즉, 5번째 행, 2번째 컬럼의 위치는 위와 같습니다.

즉, grape라는 값이 return되는 것을 볼 수 있습니다.

 

 

 

 

두 번째는 match() 함수입니다.

match() 함수는 어떤 값을, 어떤 범위에서 찾아 몇번째에 있는지를 return해주는 함수입니다.

 

 

Syntax

match(lookup_value, range, match_type)

 

- lookup_value

찾을 값 입니다.

 

- range

값을 어떤 셀 범위에서 찾을지에 대한 셀 범위를 명시해주는 부분입니다.

 

- match_type

값을 찾을 때 어떤 기준으로 찾을지를 의미합니다. -1, 0, 1 총 3개의 값을 입력할 수 있습니다.

 

아래는 각 match_type에 대한 의미입니다. (주로 0을 사용합니다.)

 

-1 -> range에서 lookup_value보다 작거나 같은 값 중 최대값을 찾습니다.

0 -> range에서 lookup_value와 정확히 같은 값을 찾습니다. 정확히 같은 값이 여러 셀에 적혀있을 경우 가장 먼저 찾아지는 셀의 순서를 return합니다.

1 -> range에서 lookup_value보다 크거나 같은 값 중 최소값을 찾습니다.

 

 

 

 

 

 

위 예시는 match() 함수를 사용한 예시입니다.

 

=MATCH("grape", C2:C9, 0)

 

위 함수를 해석해보면 다음과 같습니다.

"grape"라는 값을

C2:C9 범위에서 찾아서 몇번째에 있는지 return하는데,

값을 찾을 때에는 정확히 일치하는 값(0)을 return한다.

라는 의미입니다.

 

 

C2:C9 범위 내에서 grape랑 정확히 일치하는 값을 찾으면 5번째에 있으므로 5가 return됩니다.

 

 

 

 

 

 

 

 

=MATCH("shinemuscat", B7:D7, 0)

 

이번엔 수식을 이렇게 바꿔보겠습니다.

 

B7:D7 범위 내에서 shinemuscat을 찾으라는 의미이죠.

 

그러면 shinemuscat이라는 값은 두 번째 셀에 있으므로 2가 return됩니다.

 

 

 

 

 

 

 

=MATCH("shinemuscat", C2:C9, 0)

 

위 예시를 봅시다.

name 컬럼에 shinemuscat이란 값을 3개 넣었습니다.

C4, C7, C8 셀에 shinemuscat이라는 값이 있죠.

 

수식을 보면 C2:C9 범위 내에서 shinemuscat 값을 찾으라는 의미입니다.

 

C2:C9 범위에서 shinemuscat을 찾으면 총 3개가 찾아지지만 가장 먼저 찾아지는 값인 C4 셀의 shinemuscat을 찾습니다.

C4 셀의 shinemuscat이라는 값은 C2:C9 범위에서 3번째에 있는 값이므로 3이 return됩니다.

 

중복값이 있을 경우 가장 먼저 찾아지는 값을 return한다는게 이런 의미입니다.

 

엑셀에서 가장 먼저란 더 왼쪽에 있을 수록, 더 위쪽에 있을 수록 더 먼저가 됩니다.

 

 

 

 

 

 

위 예시를 봅시다.

 

=MATCH("abcde", C2:C9, 0)

 

abcde라는 값을 C2:C9 범위에서 찾아야하는데 abcde라는 값은 C2:C9 범위에 존재하지 않습니다.

따라서 #N/A 에러가 발생합니다.

 

 

 

 

 

자 여기까지 해서 index(), match() 함수를 알아보았습니다.

 

처음에 이 2개의 함수는 주로 동시에 쓴다고 했죠.

 

예시를 봅시다.

 

 

위 예시를 보면 C13셀에 수식이 걸려있습니다.

 

 

 

수식이 위처럼 적혀있는데

수식의 요지를 말하면 id = 3인 상품의 price를 출력하는 것입니다.

 

id = 3인 상품의 price는 19000이죠.

그래서 19000이 return되는 것을 알 수 있습니다.

 

수식을 한번 해석해봅시다.

 

=INDEX(B2:D9, MATCH(B13, B2:B9, 0), MATCH(C12, B2:D2, 0))

 

일단 가장 먼저 index 함수가 보입니다.

 

index부터 그냥 대충 이해하기 쉽게 나타내보자면 다음과 같습니다.

 

=INDEX(B2:D9, row_no, column_no)

 

B2:D9 범위에서 (row_no, column_no) 위치에 있는 값을 return하라는 의미가 됩니다.

 

여기서 row_no, column_no를 구하기 위해 match() 함수가 들어가있구요.

 

그러면 각각의 match() 함수가 row_no, column_no를 return한다고 예상할 수 있겠네요.

 

 

 

 

 

=INDEX(B2:D9, MATCH(B13, B2:B9, 0), MATCH(C12, B2:D2, 0))

다시 원래 수식으로 돌아와서 row_no 자리에 있는 match() 함수를 봅시다.

 

 

MATCH(B13, B2:B9, 0)

B13 셀의 값을

B2:B9 범위에서

정확하게 일치하는 값을 찾아 그 값이 몇번째에 있는지 return하라는 의미입니다.

 

B13셀의 값은 3이죠.

 

따라서 B2:B9 범위에서 3을 찾으면 됩니다.

B2:B9 범위에서 3이라는 값을 가진 셀은 4번째에 있습니다.

 

 

MATCH(B13, B2:B9, 0)

따라서 위 수식은 4를 return합니다.

 

 

 

 

 

 

 

=INDEX(B2:D9, MATCH(B13, B2:B9, 0), MATCH(C12, B2:D2, 0))

이제 column_no 자리에 있는 match() 함수를 봅시다.

 

 

MATCH(C12, B2:D2, 0)

C12 셀의 값을

B2:D2 범위에서

정확하게 일치하는 값을 찾아 그 값이 몇번째에 있는지 return하라는 의미입니다.

 

C12셀의 값은 price이죠.

 

따라서 B2:D2 범위에서 price라는 글자를 가진 셀을 찾으면 됩니다.

B2:D2 범위에서 price라는 값을 가진 셀은 3번째에 있습니다.

 

 

 

MATCH(C12, B2:D2, 0)

따라서 위 수식은 4를 return합니다.

 

 

 

 

 

 

 

=INDEX(B2:D9, MATCH(B13, B2:B9, 0), MATCH(C12, B2:D2, 0))

그러면 위 수식에서 match() 함수 대신 match() 함수의 결과를 넣어보면 다음과 같습니다.

 

 

=INDEX(B2:D9, 4, 3)

B2:D9 범위에서 (4, 3) 위치에 있는 값을 return하라는 의미입니다.

굉장히 간단해지죠?

 

 

 

 

B2:D9 범위에서 (4, 3) 위치에 있는 값은 19000입니다.

바로 id = 3인 상품의 price이죠.

 

 

index(), match() 함수는 주로 이렇게 사용합니다.