VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기

다음과 같은 표에서 상품코드 'A005'에 해당하는 상품 '마우스패드'를 찾아야 한다면 어떻게 해야 할까요?

찾는 키값이 첫 번째 열이 아니라 다른 열에 있기 때문에 값을 찾을 때 자주 쓰는 VLOOKUP 함수로는 해결이 안됩니다. 대신 엑셀 2021 버전부터 사용할 수 있는 XLOOUP 함수를 사용하거나 INDEX, MATCH 함수를 조합해서 해결해야 합니다.

 

엑셀-VLOOKUP으로안될때-INDEX-MATCH함수사용 (2).xlsx

0.01MB

 

1. 원리 이해하기

1) INDEX 함수로 값 찾기

INDEX, MATCH 함수를 조합해서 값을 찾기 전에 원리 이해를 위해 INDEX 함수만 사용해서 상품명 '마우스패드'를 찾아 보겠습니다.

INDEX 함수는 값이 있는 행과 열의 위치를 지정하면 값을 찾아줍니다.

[B16] 셀에 다음 수식을 입력합니다.
=INDEX(B5:B11,5,1)

다음과 같이 입력해도 결과는 동일합니다. 첫번째 인수가 단일 열이므로 세번째 인수 column_num은 생략가능합니다.
=INDEX(B5:B11,5)

 

다음과 같이 5행 1열에 위치한 '마우스패드'를 가져옵니다.

값을 찾긴 했는데 INDEX 함수의 두 번째 인수에 행의 위치 5를 직접 입력해야 하는 문제가 있습니다.

 

 

2) MATCH 함수로 값의 위치 찾기

MATCH 함수로 'A005'의 행의 위치 5를 찾아보겠습니다.

MATCH 함수는 범위와 찾고자 하는 값을 지정해주면 값 위치를 찾아줍니다.

[B21] 셀에 다음 수식을 입력합니다.

=MATCH("A005",D5:D11,0)

 

다음과 같이 상품코드가 입력된 범위 [D5:D11]에서 'A005'의 행의 위치 5를 찾아줍니다.

 

 

3) INDEX, MATCH 함수 조합해서 값 찾기

이제 위에서 살펴본 INDEX, MATCH 함수의 기능을 조합하면 상품명을 찾을 수 있습니다.

[C25] 셀에 다음 수식을 입력합니다.

=INDEX(B5:B11,MATCH(B25,D5:D11,0))

 

다음과 같이 상품코드 'A005'에 해당하는 상품명 '마우스패드'를 가져옵니다.

수식을 풀어 보면 다음과 같이 MATCH 함수가 먼저 실행되어 'A005'에 해당하는 위치를 5를 찾아서 INDEX 함수로 넘겨주면 INDEX 함수는 상품명 범위의 5번째 행에서 '마우스패드'를 찾아줍니다.

 

이번에는 동일한 방법으로 단가를 찾아보겠습니다.

[C29] 셀에 다음 수식을 입력합니다.

=INDEX(C5:C11,MATCH(B29,D5:D11,0))
상품명을 찾을 때와 수식이 거의 비슷합니다. INDEX 함수의 첫번째 인수인 찾는 범위가 B열에서 단가가 있는 C열로 바뀐 것만 다릅니다.

 

다음과 같이 상품코드 'A005'에 해당하는 단가 6,500원을 가져옵니다.

 

 

2. 실무 예제

위에서는 원리 이해를 위한 간단한 예를 살펴 보았는데 이번에는 실무 예제를 다루어 보겠습니다.

다음과 같이 판매 실적 자료를 만들어야 하는데 '상품코드'는 이미 입력되어 있고 상품정보에서 '상품명'과 '단가'를 가져오는 경우입니다.

 

1) 상품명 가져오기

상품명을 가져오기 위해 [D35] 셀에 다음 수식을 입력합니다.

=INDEX($B$5:$B$11,MATCH(C35,$D$5:$D$11,0))

앞에서 살펴본 수식과 거의 비슷합니다. 상품코드를 직접 입력하는 대신 셀에 입력된 상품코드를 참조하고 값을 찾을 범위가 절대참조(다른 곳에 수식을 복사해서 붙여 넣을 때 참조 위치가 밀리지 않고 그대로 유지되도록 하기 위해)로 바뀐것만 다릅니다.

 

2) 단가 가져오기

단가를 가져오기 위해 [E35] 셀에 다음 수식을 입력합니다.

=INDEX($C$5:$C$11,MATCH(C35,$D$5:$D$11,0))

 

마지막으로 [D35], [E35] 셀에 입력된 수식을 복사하여 아래의 나머지 범위에 붙여 넣으면 판매 실적 자료가 완성됩니다.