다음과 같은 표에서 상품코드 'A005'에 해당하는 상품 '마우스패드'를 찾아야 한다면 어떻게 해야 할까요?
찾는 키값이 첫 번째 열이 아니라 다른 열에 있기 때문에 값을 찾을 때 자주 쓰는 VLOOKUP 함수로는 해결이 안됩니다. 대신 엑셀 2021 버전부터 사용할 수 있는 XLOOUP 함수를 사용하거나 INDEX, MATCH 함수를 조합해서 해결해야 합니다.
※ 예제 이해에 필요한 내용은 다음 링크를 참고하세요.
[ INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 ]
[ MATCH 함수 - 범위에서 값의 위치 찾기 ]
1. 원리 이해하기
1) INDEX 함수로 값 찾기
INDEX, MATCH 함수를 조합해서 값을 찾기 전에 원리 이해를 위해 INDEX 함수만 사용해서 상품명 '마우스패드'를 찾아 보겠습니다.
INDEX 함수는 값이 있는 행과 열의 위치를 지정하면 값을 찾아줍니다.
=INDEX(B5:B11,5,1)
다음과 같이 입력해도 결과는 동일합니다. 첫번째 인수가 단일 열이므로 세번째 인수 column_num은 생략가능합니다.
=INDEX(B5:B11,5)
다음과 같이 5행 1열에 위치한 '마우스패드'를 가져옵니다.
값을 찾긴 했는데 INDEX 함수의 두 번째 인수에 행의 위치 5를 직접 입력해야 하는 문제가 있습니다.
2) MATCH 함수로 값의 위치 찾기
MATCH 함수로 'A005'의 행의 위치 5를 찾아보겠습니다.
MATCH 함수는 범위와 찾고자 하는 값을 지정해주면 값 위치를 찾아줍니다.
=MATCH("A005",D5:D11,0)
다음과 같이 상품코드가 입력된 범위 [D5:D11]에서 'A005'의 행의 위치 5를 찾아줍니다.
3) INDEX, MATCH 함수 조합해서 값 찾기
이제 위에서 살펴본 INDEX, MATCH 함수의 기능을 조합하면 상품명을 찾을 수 있습니다.
=INDEX(B5:B11,MATCH(B25,D5:D11,0))
다음과 같이 상품코드 'A005'에 해당하는 상품명 '마우스패드'를 가져옵니다.
수식을 풀어 보면 다음과 같이 MATCH 함수가 먼저 실행되어 'A005'에 해당하는 위치를 5를 찾아서 INDEX 함수로 넘겨주면 INDEX 함수는 상품명 범위의 5번째 행에서 '마우스패드'를 찾아줍니다.
이번에는 동일한 방법으로 단가를 찾아보겠습니다.
=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] 셀에 입력된 수식을 복사하여 아래의 나머지 범위에 붙여 넣으면 판매 실적 자료가 완성됩니다.
관련 글
XLOOKUP 함수 - 표에서 값찾기(VLOOKUP 단점 해결) - XLOOKUP 함수는 기존 VLOOKUP, HLOOKUP 함수의 몇 가지 단점(찾는 키값이 항상 1열에 있어야 하고, 가로로 된 표는 VLOOKUP 대신 HLOOKUP… …
HLOOKUP 함수 - 표를 수평으로 따라가면서 값찾기 - 엑셀의 HLOOKUP 함수는 셀 범위에서 원하는 값을 찾을 때 사용하는 함수입니다. HLOOKUP이라는 함수 이름은 Horizontal Lookup을 줄여서 쓴 것으로 다음과… …
MATCH 함수 - 범위에서 값의 위치 찾기 - 엑셀의 MATCH 함수는 지정된 범위에서 찾고자 하는 값의 위치를 반환합니다. 단순히 위치만 찾아 주므로 위치만 가지고는 쓸 일이 많이 없습니다.… …
INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 - 엑셀의 INDEX 함수는 테이블이나 범위에서 행과 열에 해당하는 값을 찾아줍니다. INDEX 함수에는 배열형과 참조형이라는 두 가지 형식이 있습니다. INDEX 함수는… …
INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 다음 그림과 같이 상품정보에서 단가를 연도별로 관리한다고 할때 단가를 찾으려면 상품코드에 연도 조건을 추가해서 찾아야 합니다. 이 경우에 VLOOKUP 함수나… …
정말로 정말로 감사드립니다.....
참고로 찾고자하는 셀이 숫자형식으로 안되어있는경우(ex. 한글문서에서 붙여넣기한경우) 오류가 나니, 숫자를 찾고자하는 경우 셀의 서식이 숫자형식인지 반드시 확인해야하는 듯합니다.
감사합니다!!! 덕분에 많은 도움되었습니다!
엑셀 2016에서는 안되는거 같네요....저도 이직 전에는 365 썼을 때 자주 활용 했던 기능인데 지금 하려니깐 도저히 안되고 MATCH의 Lookup Value를 1로 놓고 해야되네요
2016에도 되는 기능입니다. 수식을 어떻게 입력하셨는지 알려주시면 답변드리겠습니다. 파일을 admin@xlworks.net으로 보내주시면 더 좋습니다.
설명이 아주 자세하고 이해가 잘 됩니다. 덕분에 공부가 많이 되었습니다. 감사합니다.
친절한 설명 감사합니다
indes/match로 값을 불러오는데....혹시 그 값이 문자라 했을때 그 문자의 글꼴 서체 색상도 같이 가져 올 수 있는 방법이 있나요? 부탁드립니다^^
index/match는 값만 가져올 수 있습니다.