INDEX 함수 구성
참조범위가 하나일 경우
INDEX함수는 특정 참조 범위에 대해서 행 값과 열 값을 숫자로 입력하여 해당 셀 주소의 값을 불러오는 함수로 아래 그림 1과 같이 구성되어 있다.
위 그림 1에서 처럼 INDEX 함수의 참조범위를 A3:C9로 잡고, 행 번호=2, 열번호=2 를 입력하면 A3:C9 범위에서 2행 2열에 있는 값인 "AK홀딩스"라는 값을 얻을 수 있다.
참조범위가 둘 이상일 경우
INDEX 함수는 참조 범위를 여러 개로 할 수도 있다. INDEX 함수의 참조범위를 입력하는 부분에 "()"괄호를 넣고 ", "콤마로 구분하여 여러 개의 참조범위를 넣을 수 있는 것이다. 그중 어떤 참조범위를 사용할지는 맨 마지막에 네 번째 입력변수를 넣어서 정한다. 기본 형태는 아래와 같다.
위 그림 2에서는 참조범위1을 A3:C5로, 참조범위 2를 A8:C11로 잡았다. 참조범위에 대해서 행 번호=3, 열 번호=2 인 값을 찾는데 맨 마지막에 참조범위 2 선택옵션을 추가적으로 부여해 주면서 참조범위 2를 선택했다. 그 결과 참조범위 2인 A8:C11의 3행 2열 값인 "BGF에코머티리얼즈" 값을 얻을 수 있다.
MATCH 함수 구성
MATCH 함수는 행 번호를 반환하는 함수이다. 그래서 항상 함수의 결과값이 숫자가 된다. 기본적인 형태는 찾는 값을 먼저 주고, 범위를 설정한 뒤에 찾기 옵션을 선택하는 모양으로 아래 그림 3과 같다.
찾기 옵션은 0: 완전히 일치, 1: 보다 작음, -1: 보다 큼 옵션이 있는데 숫자를 찾을 때는 1, -1 옵션이 의미가 있지만 보통의 문자열을 찾는 경우 0을 세팅하면 된다. 위 그림 3에서 "BGF"라는 값은 범위로 지정한 B4:B11에서 다섯 번째 행에 있으므로 BGF의 절대적인 위치인 "B8"과는 상관없이 5라는 값이 나온다.
INDEX(MATCH) 함수 조합
VLOOKUP 함수의 한계
INDEX(MATCH) 함수 조합은 VLOOKUP 함수 사용이 불가능할 때 주로 사용한다. VLOOKUP 함수는 아래 그림 4와 같이 찾는 값이 찾는 범위의 첫 번째 열과 일치한다면, 찾는 범위의 첫 번째 열이든 두 번째 열이든 값을 가져오는 함수이다.( 엑셀 VLOOKUP 함수 사용법: 지수별 종가 찾기 참조)
따라서 위 그림 4에서 처럼 종목코드로 종목명을 불러올 수는 있지만 VLOOKUP 함수를 사용해서 종목명으로 종목코드를 역으로 불러오는 것은 불가능하다. 데이터 구조가 종목코드가 왼쪽에 있고, 엑셀의 모든 범위는 왼쪽에서 시작해서 오른쪽으로 끝나기 때문이다.
INDEX(MATCH) 함수 구성
INDEX MATCH 함수를 조합해서 쓰면 위와 같은 VLOOKUP 함수의 한계를 보완할 수 있다. 사용방법은 단순히 INDEX 함수의 행 번호를 입력하는 자리에 MATCH함수를 써서 내가 원하는 값이 있는 행 번호를 찾아주는 것으로 아래 그림 5와 같다.
이렇게 함으로써 참조범위 상에서 내가 찾는 값의 행 값이 나오고 그 행의 앞쪽 열을 선택하면 VLOOKUP 함수가 할 수 없는 종목명으로 통한 종목코드 찾기가 가능해 진다. 여기에서 주의할 점은 참조범위가 3행~ 14행이라면, MATCH함수의 찾는 범위도 3행 ~ 14행이 되어야 한다는 점이다.
INDEX MATCH함수 사용예제: 종목코드 찾기
종목코드 찾기
KRX 정보데이터시스템(http://data.krx.co.kr)에서 주식-> 종목시세 -> 전종목 시세를 검색할 수 있다. 아래 그림 6처럼 데이터를 엑셀로 다운로드 받아서 "3S"라는 종목의 코드를 INDEX MATCH 함수를 사용해서 찾아보자.
"3S"라는 종목의 코드를 찾고 싶으면, 먼저 INDEX 함수를 열어서 데이터의 전체 범위인 A2: C2787 까지 참조범위를 잡아준다. 이 참조범위에서 먼저 3S가 위치한 행의 값을 찾기 위해 MATCH 함수를 추가적으로 열어준다. MATCH 함수에서 찾을 값은 "3S"라는 값이 있는 E2 셀이 된다. 찾는 범위는 종목명이 위치한 B2:B2787 까지가 되고, 완전히 일치하는 대상을 찾기 위해 0 옵션을 선택하고 MATCH 함수를 닫는다. 다시 INDEX 함수로 돌아가서 초기에 설정한 참조범위의(A2: C2787 ) 열 값을 설정해 준다. 우리는 참조범위의 1열에 있는 종목코드를 가져와야 하기 때문에 1을 선택해 주고 INDEX 함수까지 닫는다.
목록을 구성해서 종목코드 찾기
종목명을 입력하는 칸을 아래 그림 8과 같이 데이터 -> 데이터 유효성 검사 -> 목록/원본 범위 선택 을 통해서 목록에서 선택하는 방식으로 바꿀 수도 있다. 이때 원본 범위는 종목명의 범위 즉, MATCH 함수에서 찾는 범위와 같다.(보기 편하게 종목명 입력란을 E2에서 F2로 옮겼다.)
최종적으로 아래와 같이 드롭다운 목록에서 종목명을 선택하면 자동으로 종목코드가 세팅되는 것을 확인할 수 있다.
'엑셀함수기초' 카테고리의 다른 글
| 엑셀 DATEVALUE 함수 & DATE 함수로 텍스트를 날짜로 변환 (0) | 2023.12.23 |
|---|---|
| 엑셀 텍스트함수 기초: LEFT RIGHT MID SUBSTITUTE (0) | 2023.12.23 |
| 엑셀 IF 함수 사용법(이중IF함수&다중IF함수): 부동산 데이터 찾기 (0) | 2023.12.12 |
| 엑셀 VLOOKUP 함수 사용법: 지수별 종가 찾기 (0) | 2023.12.10 |
