직장인 엑셀 INDEX MATCH 함수 사용법

오늘은 VLOOKUP함수 보다는 조금 까다로운 INDEX, MATCH 함수 사용 방법에 대해 알아보겠습니다. VLOOKUP함수는 방대한 데이터 자료에서 필요값을 뽑아내는 함수라서 실무에서 많이 쓰입니다. 다만 VLOOKUP은 찾을값이 데이터 상 맨 왼쪽에 잊지 않을 경우, 찾을값이 1개 이상일 경우 단독으로 사용할 수 없는 한계점이 있습니다. 그래서 이럴 때 대체해서 쓰는 함수가 바로 INDEX와 MATCHG함수인데요! 직장인 엑셀 중 하나인만큼 오늘 포인트만 딱딱 집어 설명드리겠습니다.

 

① INDEX, MATCH 함수 소개

	A	B	C
1	코드	성별	나이
2	DFS	남	30
3	ERW	여	28

 

INDEX함수는 =INDEX(지정범위,행번호,열번호)로 지정범위 내에서 행과 열번호를 입력하면 그 위치값을 불러옵니다.

위 표를 예시로 =INDEX(A1:C3,3,1) 이라고 적으면 A1:C3 범위 기준 3행 1열에 있는 값을 불러오라는 거죠!

3행 1열에 있는 값은 ERW이므로 ERW가 출력될 겁니다.

→ INDEX함수는 지정한 위치에 있는 값을 불러오는 함수

 

 

MATCH함수는 =MATCH(찾을값, 지정범위, 0 or 1 or -1)로 찾을값이 몇 행 또는 몇 열에 위치해 있는지 알려줍니다.

위 표를 예시로 =MATCH("ERW",A1:A3,0)이라고 적으면

찾을값 ERW는(ERW 좌우에 따옴표를 붙인 이유는 엑셀에서 문자를 쓸 때 양쪽에 따옴표를 적어줘야 인식하기 때문)

A1:A3범위에서 몇 행 또는 몇 열에 위치해 있는지 알려달라는 말입니다.

여기서 지정범위 A1:A3은 세로 방향입니다. 이렇게 세로 방향 즉 열방향으로 범위를 잡으면 출력값은 행위치가 나옵니다.

ERW는 3행에 있으므로 값은 3이 출력됩니다.

 

만약 =MATCH("ERW",A3:C3,0)이라고 적으면 A3:C3은 가로 방향 즉 행방향이므로 출력값은 열 위치가 나옵니다.

ERW는 1열에 있으므로 값은 1이 출력됩니다.

 

→ MATCH함수는 행번호 또는 열번호를 출력하는 함수

→ 범위를 열방향으로 잡으면 출력값은 행위치가 나옴

→ 범위를 행방향으로 잡으면 출력값은 열 위치가 나옴

→ MATCH함수 공식 마지막 0 or 1 or -1의 뜻은 0은(0은 생략가능) 정확히 일치하는 값을, 1과 -1은 유사값을 말함

보통 실무에서는 정확히 일치하는 값을 찾기 때문에 MATCH함수 마지막에는 0을 쓴다 또는 안 쓴다로 외워두면 됨

 

 

② 찾을값이 데이터 상 맨 왼쪽에 위치하지 않을 때 사용해 보자!

	A	B	C
1	코드	성별	나이
2	DFS	남	30
3	ERW	여	28

VLOOKUP은 앞서 말했듯이 방대한 자료에서 필요값을 뽑아내는 함수라고 했습니다.

VLOOKUP공식은 =VLOOKUP(찾을값,데이터 포함 전체범위,열번호,0 or1) 인데요.

여기서 찾을값은 지정범위 기준 항상 왼쪽에 위치해 있어야 합니다.

만약 내가 지정범위를 A1:C3이라고 잡으면 찾을값은 맨 왼쪽 A열만 가능합니다.

만약 내가 지정범위를 B1:C3이라고 잡으면 찾을값은 맨 왼쪽 B열만 가능하지요.

 

만약 여자의 코드번호를 알고 싶다면 어떻게 해야 할까요?

=VLOOKUP("여자",B1:C3,?,0)

찾을값은 여자이고 범위는 B1:C3으로 잡았습니다.

B1:C3 범위에서 찾을값 여자는 맨 왼쪽에 위치해 있으니 OK.

그러나 코드번호는 B1:C3범위에 포함되어 있지 않음!

이럴 경우에는 VLOOKUP함수를 사용 못합니다.

 

이런 VLOOKUP 한계를 INDEX와 MATCH함수가 커버해 주죠!

앞서 MATCH함수는 값이 몇 행 또는 몇 열에 있는지 알려주는 함수이고

INDEX는 행과 열번호를 입력하면 그 위치에 있는 값을 출력하는 함수라고 했습니다.

그래서 INDEX안에 MATCH함수를 넣어서 적는답니다. 

 

다시 돌아가서 INDEX와 MATCH함수를 이용해 여자의 코드번호를 알아봅시다.

순서는 MATCH함수 → INDEX함수 순서로 하며

일단 MATCH함수를 이용해 여자가 몇 행 또는 몇 열에 있는지 알아보면

=MATCH(찾을값, 지정범위, 0 or 1 or -1)

=MATCH("여자",B1:B3,0)

=3

 

지정범위 B1:B3은 세로방향 즉 열방향이므로 출력값은 행이 나옵니다.

여자는 B1:B3 범위에서 3행에 있으므로 값은 3이 나옵니다.

 

여자의 위치를 알아냈으니 INDEX를 이용해서 최종값 코드 번호를 출력해 봅시다.

=INDEX(지정범위,행번호,열번호)

 

지정범위의 경우 우리는 코드 번호가 필요한 것이니 코드 번호가 있는 영역인 A1:A3을 지정하면 됩니다.

행번호는 우리가 MATCH함수를 통해 이미 출력해 놨죠? 이 행번호에 MATCH함수가 들어가면 되겠네요.

열번호는 A1:A3의 열은 1열만 있기 때문에 1을 적으면 됩니다. 1일 경우에는 생략을 해도 상관은 없습니다.

 

=INDEX(A1:A3,MATCH("여자",B1:B3,0),1)

=ERW

 

 

③ 찾을값이 여러 개일 때 사용해 보자!

VLOOKUP식 =VLOOKUP(찾을값,데이터 포함 전체범위,열번호,0 or1) 을 보면 찾을값은 여러개가 들어갈 수 없습니다. 찾을값은 단 1개만 들어갈 수 있는데요. 만약 찾을값이 여러 개일 경우, 즉 A조건과 B조건을 모두 충족하는 값을 뽑아내고 싶을 땐 VLOOKUP대신 INDEX & MATCH 함수를 이용해서 값은 뽑아낼 수 있습니다.

 

	A	B	C
1	부서	이름	급여
2	경영지원부	김시연	100
3	기획부	박하늘	150
4	생산부	박하늘	200

위 표를 예시로 생산부의 박하늘 급여를 알고 싶다면,

부서에서 생산부를 찾아야 하고(찾을값1) 이름에서 박하늘(찾을값2)을 찾아야 합니다.

즉 찾을값이 이렇게 2개일 경우에는 VLOOKUP을 사용하지 못하니 INDEX & MATCH 함수를 사용하면 됩니다.

바로 사용하기 전에 일단 TRUE와 FALSE 작업부터 시작해야 하는데요!

 

	A		B	C	D
1	TRUE인가 FALSE인가		부서	이름	급여
2	="생산부"=B2:B4 =FALSE =0	="박하늘"=C2:C4 =FALSE =0	경영지원부	김시연	100
3	="생산부"=B2:B4 =FALSE =0	="박하늘"=C2:C4 =TRUE =1	기획부	박하늘	150
4	="생산부"=B2:B4 =TRUE =1	="박하늘"=C2:C4 =TRUE =1	생산부	박하늘	200

엑셀에서 TRUE는 숫자 1로 FALSE는 숫자 0으로 인식합니다.

"생산부"=B2:B4 라고 작성할 경우 생산부와 일치하는 값은 TRUE가, 일치하지 않을 경우엔 FALSE가 나옴.

"박하늘"=C2:C4 라고 작성할 경우 박하늘과 일치하는 값은 TRUE가, 일치하지 않을 경우엔 FALSE가 나옴.

 

FALSE와 FASE를 곱하면 0X0=0 

FALSE와 TRUE를 곱하면 0X1=0

TRUE와 TRUE를 곱하면 1X1 =1

생산부와 박하늘이라는 조건을 둘 다 만족해야만 곱하기 값이 1이 나오겠구나!

 

이 부분을 이해했으면 이제 MATCH함수로 넘어가서 수식을 쓰겠습니다.

 

		A		B	C	D
1		TRUE인가 FALSE인가		부서	이름	급여
2	FALSE X FALSE = 0	="생산부"=B2:B4 =FALSE =0	="박하늘"=C2:C4 =FALSE =0	경영지원부	김시연	100
3	FALSE X TRUE = 0	="생산부"=B2:B4 =FALSE =0	="박하늘"=C2:C4 =TRUE =1	기획부	박하늘	150
4	TRUE X TRUE = 1	="생산부"=B2:B4 =TRUE =1	="박하늘"=C2:C4 =TRUE =1	생산부	박하늘	200

=MATCH(찾을값, 지정범위, 0 or 1 or -1)

=MATCH(1,--("생산부"=B2:B4)*("박하늘"=C2:C4),0)

 

찾을값은 TRUE와 TRUE를 곱했을 때의 값 1을 말함

 

지정범위는 1이 나오는 범위를 지정하는 거니까

"생산부"=B2:B4 조건1과 "박하늘"=C2:C4 조건2를 곱한 것이 지정범위가 됨

이때 곱하기 앞쪽에 있는 -- 기호는 오류 방지를 위해 쓰는 것!

이렇게 TRUE, FALSE 곱하기 할 때는 -- 를 쓴다고 외우기!

 

마지막 0은 일치하는 값을 찾는 것이니 0을 쓴 것! 이것도 그냥 마지막은 0을 쓴다고 외우기 (0은 생략 가능함)

 

범위 ("생산부"=B2:B4)*("박하늘"=C2:C4) 가 세로방향 즉 열방향이니 값은 행번호가 나오겠죠?

1은 3행에 있으니 값은 3이 나옵니다.

 

이제 INDEX를 사용해 생산부 박하늘의 급여를 압아봅시다!

=INDEX(지정범위,행번호,열번호)

=INDEX(D2:D4,행번호,열번호)

 

급여가 최종 출력값이니 지정범위는 D2:D4

행번호는 우리가 MATCH함수를 통해 이미 출력했죠? 이 행번호에 MATCH함수가 들어가면 되겠네요.

열번호는 D2:D4의 열은 1열만 있기 때문에 1을 적으면 됩니다. 1일 경우에는 생략을 해도 상관은 없답니다.

 

=INDEX(D2:D4,MATCH(1,--("생산부"=B2:B4)*("박하늘"=C2:C4),0),1)

=200

 

---

이것으로 엑셀 실무에서 잘 쓰는 VLOOKUP의 짝꿍 격인 INDEX & MATCH 함수에 대해 알아봤습니다. 다소 헷갈리시는 분들을 위해 VLOOKUP, INDEX, MATCH 함수에 관한 다른 포스팅들을 링크로 달아두겠습니다. 그럼 다음 포스팅도 유익한 정보로 찾아뵙겠습니다. 끝까지 읽어주셔서 감사드립니다.

 

 

[ 참고하면 좋은 글 ]

엑셀실무 VLOOKUP INDEX MATCH 사용법

 

 

 

VLOOKUP함수 다중조건 찾을값 여러 개일 때 응용법

 

2023.02.13 - [엑셀 정보] - VLOOKUP함수 중복값 찾기 COUNTIF 기억하자!

VLOOKUP함수 중복값 찾기 COUNTIF 기억하자!