엑셀 행과 열의 조건에 맞는값 찾기!! INDEX, MATCH 함수

엑셀 실무를 사용 하면서 업무 시간을 단축하기 위해

쓰이는 기본적이 자동화 엑셀의 기초라 할 수 있는

INDEX , MATCH 함수에 대해 알아 보겠습니다.

엑셀 실무에서 가장 많이 사용하고 응용할 수 있는

함수 중 하나라 하겠습니다.

한마디로 쉽게 이야기하자면,

가로 방향의 데이터를 찾는vlookup과

세로 방향의 데이터를 찾는 hlookup이

합쳐진 기능이라고 보시면 되겠습니다.

그럼 지금부터

비쥬얼 베이직과 메크로를 사용하지 않고

가로와 세로(행과 열) 조건에 맞는 값을 찾는

방법에 대해 쉽게 알아보도록 하겠습니다.

조건값찾기.xlsx

INDEX함수는 굉장히 사용하기 쉬운데요

C행의 3번째 열의 값을 찾을려면 아래와 같이 함수를 입력합니다.

=INDEX(A1:D4,C1,A3)

3가지의 조건만 입력하면 됩니다.

조건, 가로셀, 세로셀

INDEX는 INDEX자체로 쓰이는 경우가 실무에서는 거의 없기 때문에

이 정도의 개념만 알아 놓으시면 됩니다.

다음은 MATCH 함수입니다.

이 함수 또한 굉장히 쉽습니다.

아래의 예제를 볼게요.

99라는 값이 몇번째에 있는지 찾아 보았습니다.

위와 같이 3가지의 조건을 입력하면 4번째에 있다고 표시가 되네요.

=MATCH(99,A4:D4,0)

이렇게 표기가 되겠네요.

사실 MATCH 함수도 실무에서 단독으로 쓰이는 경우는 잘 없어요.

그냥 개념정도만 알아두시면 됩니다.

다음은 실무에서 아주 유용한

INDEX+MATCH를 같이 사용할 수 있는 방법과

어떤 경우에 실무에서 사용되는지 예제와 함께 보겠습니다.

전자제품의 기종과 출고가, 그리고 연도별 지원금 데이터가 있습니다.

이 데이터를 기반으로 아래의 그림과 같이

기종과 연도의 두가지 조건을 입력하면 

자동으로 지원금이 나오도록 해 보겠습니다.

먼저 함수를 사용하기 이전에 해야할 작업이 있습니다.

지정한 범위의 셀에 이름을 지정하는 작업입니다.

수식에서 이름관리자 -새로 만들기를 실행합니다.

가로행의 연도, 세로열의 기종에 이름을 부여 해야합니다.

이름에 기종이라고 쓴뒤 참조대상의 범위를 지정합니다.

범위는 카메라부터 냉장고까지 지정하시면 됩니다.

이름에 연도 참조대상에 2013년부터 2019년까지 지정을 합니다.

이렇게 하시면 "기종","연도"의 이름에 각각의 범위가 지정 되었습니다.

이렇게 지정한 뒤 INDEX와 MACTH함수를 사용만 하면 끝입니다.

=INDEX(지원금!D4:J8,MATCH(C2,기종,0),MATCH(C3,연도,0))

쉽게 풀이를 해보겠습니다.

=INDEX(지원금!D4:J8

내가 위와 같이 지정한 범위에서 교차되는 값을 찾을거야.

MATCH(C2,기종,0),MATCH(C3,연도,0))

C2에 입력한값(휴대폰)과 같은 값을 이름으로 지정한 "기종"이라는 범위내에서 찾고,

C3에 입력한값(2016)과 같은 값을 이름으로 지정한 "연도"라는 범위내에서 찾을꺼야.

이렇게 해서 결과 값이 320,000이 됩니다.

어려워 보여도 한번만 이해하면 굉장히 쉽습니다.

지원금이 완성되었으니 출고가와 판매가도 자동으로 입력되게 해 놓을게요.

출고가는 vlookup을 통해서 불러왔고

판매가는 출고가에서 지원금을 뺀 값을 적용시켰습니다.

기종이랑 연도에 입력하면 자동으로

출고가 / 지원금 / 판매가가 바뀝니다.

여기서 끝이 아닙니다.

조금 더 고급스러워 보이도록 기종과 연도를 직접 입력하지 않고

아래와 같이 목록형으로 선택 해 보도록 하겠습니다.

목록형으로 나타낼 셀 위치에서(C2)

데이터 - 데이터 유효성 검사를 실행한 뒤,

제한대상을 목록으로 지정합니다.

원본으로 되어 있는 부분에

목록으로 나타내고 싶은 항목들을 드래그로 지정하면 끝입니다.

연도도 마찬가지 방법으로 지정 해 주면 되겠습니다.

이상으로 엑셀 행과 열의 조건값 찾기에 대한 함수와

사용방법을 알아 보았습니다.

다들 업무시간을 줄여서 칼퇴합시다.^^