엑셀 조회 함수 INDEX와 MATCH로 필요한 데이터 찾기(2가지 이상 조건 검색)

정리해둔 데이터에서 필요한 값을 찾는 VLOOKUP과 HLOOKUP 함수가 있습니다.

 - https://toast-story.tistory.com/9

엑셀 조회함수 Vlookup과 Hlookup으로 필요한 DATA를 찾자

 

VLOOKUP과 HLOOKUP으로 충분한 경우가 많지만 실제로는 좀 더 복잡한 상황이 많습니다.

아직까지 엑셀에서는 조회함수로 사용가능한 함수 중 MATCH와 INDEX를 조합이 가장 유용합니다.

 

 

1. 기본적인 함수의 설명에 대해 알아보겠습니다.

 

MATCH(lookup_value, lookup_array, [match_type]) : 범위(lookup_array)에서 lookup_value의 위치를 반환합니다.

 

• lookup_value : lookup_array에서 찾으려는 값입니다.
• lookup_array : 필수 요소입니다. 검색할 셀 범위입니다
• [match_type] : 선택 사항입니다. 숫자 -1, 0 또는 1입니다.
                         -1 : 작거나 같은 값 중에서 최대값을 찾습니다.
                         0 : 정확한 값을 찾습니다.
                         +1 : 크거나 같은 값 중에서 최소값을 찾습니다.

INDEX(array, row_num, [column_num]) : 범위(array)내에서 원하는 위치에 있는 값을 반환합니다.

 

• array : 배열 상수나 셀 범위입니다.
• row_num : 값을 반환할 배열의 행을 선택합니다.
• [column_num] : 선택 요소입니다. 값을 반환할 배열의 열을 선택합니다.

두 함수 다 단독으로도 많이 사용됩니다.

index는 배열이나 행렬을 반환하는 모든 상황에서 사용하는 유용한 함수입니다.

match는 어째꺼나 목록에서 값을 찾는데 유용합니다.

 

2. Match와 index의 조합

 

기본적인 사용법은 아래와 같습니다.

index의 범위 array에는 출력할 값을 match의 범위는 찾는 값을 입력합니다.

주의할 점을 각 범위가 같은 행이나 같은 열에서 시작해야 합니다.

match는 B행에서 "서울특별시"의 위치가 몇번째인지 찾고 index는 "2015.01"의 범위내에서 값을 출력합니다.

 

 

여기에서 가로 줄도 조건에 넣을 수 있습니다.

index에서 행과 열을 모두 찾는 기능을 사용하면 큰 범위에서도 사용합니다.

match를 두개 넣어서 복잡한 함수가 됩니다.

 

 

320x100

 

 

3. 두 가지 이상 조건 입력하기

 

조건 하나만으로 충분히 강력한 함수지만 두가지 이상의 검색식을 가지는 배열함수라는 사용법이 있습니다.

정확하게는 match함수를 배열함수로 변경하는 것입니다. 사용법은 아래와 같습니다.

 

 

최근 버전의 엑셀에서는 배열함수를 자동으로 인식해서 평범하게 사용할 수 있습니다.

하지만 예전의 엑셀버전에서는 함수를 입력할때는 ctrl + shift + enter를 입력해야 합니다.

 

 

index의 검색와 match의 기능을 둘다 활용하면 복잡한 상황에서도 응용이 여러가지로 가능함니다.

하지만 다중함수를 너무 남용하면 오류가 나기 쉽습니다.

 

1. 검색할 값이 리스트에 없는 경우, 오타이거나 "."이나 의도하지 않는 공백문자가 들어가있을 수 있습니다.
2. 범위가 맞지 않는 경우, index와 match의 범위가 어긋나기 쉽습니다.
3. 배열함수가 정확하게 입력되지 않았을 때  ctrl + shift + enter로 입력해야 합니다.

 

오류가 발생하면 위의 사항을 확인해 보기바랍니다.

혹시 장기적으로 써야하는 문서에서 작성했다면 시물레이션으로 몇번 확인을 해보고 사용하는게 좋습니다.