엑셀함수 INDIRECT – 참조를 동적으로 바꾸기

엑셀 수식을 사용하다보면 수식에 있는 참조가 동적으로 바뀌어야 하는 경우가 있다. 예를 들면 VLOOKUP함수를 사용할 때 값을 찾는 시트의 이름이 그 때 그 때 바뀌어야 한다면 다음과 같이 INDIRECT함수를 이용하면 해결할 수 있다.

=VLOOKUP(C14,INDIRECT(C13 & “!A1:C11”),2,FALSE)

이 수식을 이해하기 위해 INDIRECT함수를 먼저 살펴보자.

구문(Syntax)

문자열로 만들어진 참조를 반환한다.

인수 :

– ref_text : 참조할 문자열

– a1 : 생략가능(생략시 TRUE), TRUE이면 A1스타일의 참조를 사용, FALSE이면 R1C1 스타일의 참조를 사용

INDIRECT함수를 응용하기 전에 먼저 간단한 예를 살펴보자.

C5셀에 “안녕하세요!” 라는 내용이 입력되어 있을 때 일반적으로 이 값을 참조하려면
=C5
를 입력하면 된다.

그런데
=INDIRECT(“C5”)
를 입력해도 동일한 결과를 얻는다.

위 결과를 보면 INDIRECT함수는 다른 셀을 참조할 수 있도록 “문자열로 만들어진 값”을 참조형태로 바꾸어 주는데 이렇게만 보면 그다지 쓸모가 있어 보이지는 않는다.

INDIRECT함수는 단독으로는 잘 사용되지 않고 아래의 예와 같이 VLOOKUP함수 등 다른 함수에 포함되어서 참조를 동적으로 바꿔야 할 때 많이 사용된다.

다음 그림과 같이 판매실적 시트가 월별로 있을 때

시트별(월별)로 판매실적을 찾을 경우 시트명이 다르므로 보통 아래와 같이 시트명을 바꿔가면서 각각 수식을 입력해야 할 것이다.

사원번호가 102인 직원의 1월 판매실적 가져올 때 :
VLOOKUP(102,’1월실적’!A1:C11,3,FALSE) => 2,000,000

사원번호가 102인 직원의 2월 판매실적 가져올 때 :
VLOOKUP(102,’2월실적’!A1:C11,3,FALSE) => 2,600,000

시트의 갯수만큼 수식을 바꿔가면서 작성해야 하는 번거로움이 있는데 다음과 같이 INDIRECT함수를 이용하면 하나의 수식으로 해결이 가능하다.

=VLOOKUP(C14,INDIRECT(C13 & “!A1:C11”),3,FALSE)

C13셀에 시트명을 “1월실적”으로 입력하면 INDIRECT(C13 & “!A1:C11”) 수식이 1월실적 시트를 참조하고 최종적으로 VLOOKUP함수가 1월 판매실적을 찾아준다.

C13셀에 시트명을 “2월실적”으로 입력하면 INDIRECT(C13 & “!A1:C11”) 수식이 2월실적 시트를 참조하고 최종적으로 VLOOKUP함수가 2월 판매실적을 찾아준다.

[ 엑셀 찾기 및 참조영역 함수 ]