엑셀 수식을 사용하다보면 수식에 있는 참조가 동적으로 바뀌어야 하는 경우가 있다. 예를 들면 VLOOKUP함수를 사용할 때 값을 찾는 시트의 이름이 그 때 그 때 바뀌어야 한다면 다음과 같이 INDIRECT함수를 이용하면 해결할 수 있다.
=VLOOKUP(C14,INDIRECT(C13 & "!A1:C11"),2,FALSE)
이 수식을 이해하기 위해 INDIRECT함수를 먼저 살펴보자.
INDIRECT(ref_text, [a1])
문자열로 만들어진 참조를 반환한다.
인수 :
– ref_text : 참조할 문자열
– a1 : 생략가능(생략시 TRUE), TRUE이면 A1스타일의 참조를 사용, FALSE이면 R1C1 스타일의 참조를 사용
사용예 :
1) 문자열로 만들어진 참조를 반환하기
INDIRECT함수를 응용하기 전에 먼저 간단한 예를 살펴보자.
C5셀에 "안녕하세요!" 라는 내용이 입력되어 있을 때 일반적으로 이 값을 참조하려면
=C5
를 입력하면 된다.
그런데
=INDIRECT("C5")
를 입력해도 동일한 결과를 얻는다.
위 결과를 보면 INDIRECT함수는 다른 셀을 참조할 수 있도록 "문자열로 만들어진 값"을 참조형태로 바꾸어 주는데 이렇게만 보면 그다지 쓸모가 있어 보이지는 않는다.
INDIRECT함수는 단독으로는 잘 사용되지 않고 아래의 예와 같이 VLOOKUP함수 등 다른 함수에 포함되어서 참조를 동적으로 바꿔야 할 때 많이 사용된다.
2) 시트명을 동적으로 참조하여 값 찾기
다음 그림과 같이 판매실적 시트가 월별로 있을 때
시트별(월별)로 판매실적을 찾을 경우 시트명이 다르므로 보통 아래와 같이 시트명을 바꿔가면서 각각 수식을 입력해야 할 것이다.
사원번호가 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월 판매실적을 찾아준다.
[ 엑셀 찾기 및 참조영역 함수 ]
- 엑셀함수 SORT - 범위 또는 배열의 내용을 정렬하기
- 엑셀함수 SORTBY - 범위의 값을 기준으로 범위의 내용을 정렬하기
- 엑셀함수 FILTER - 원하는 조건으로 데이터 조회(필터링)하기
- VLOOKUP의 단점을 해결한 XLOOKUP함수로 표에서 값찾기
- 엑셀함수 HLOOKUP - 표를 수평(가로)으로 따라가면서 값찾기
- 엑셀함수 HYPERLINK - 하이퍼링크 만들기
- 엑셀함수 ROWS - 범위나 배열의 행의 개수 구하기
- 엑셀함수 COLUMNS - 범위나 배열의 열의 개수 구하기
- 엑셀함수 AREAS - 참조영역내의 영역의 개수 구하기
- 엑셀함수 ADDRESS - 행,열 번호로 셀주소 표시하기
- 엑셀함수 TRANSPOSE - 행과 열을 바꾸기
- 엑셀함수 INDIRECT – 참조를 동적으로 바꾸기
- 엑셀함수 LOOKUP - 행 또는 열의 같은 위치에 있는 값 찾기
- 엑셀함수 OFFSET - 행열이동 후 참조구하기
- 엑셀함수 COLUMN - 열번호를 자동으로 매기기
- 엑셀함수 ROW - 행번호를 자동으로 매기기
- 엑셀함수 CHOOSE - 값목록에서 원하는 값 선택하기
- 엑셀함수 MATCH - 특정 범위에서 값의 위치 찾기
- 엑셀함수 INDEX - 특정 범위에서 행과 열을 이용하여 값 찾기
- 엑셀 VLOOKUP 함수로 표에서 값 찾기(간단 버전)
- 엑셀에서 값을 찾을 때 VLOOKUP, HLOOKUP 함수 사용하기
- VLOOKUP으로 안될때 INDEX,MATCH 사용하기
- INDEX, MATCH를 이용한 다중조건으로 값찾기
핑백: 엑셀함수 ADDRESS - 행,열 번호로 셀주소 표시하기 - XLWorks 엑셀웍스
도움이 많이 되었습니다. 감사합니다.
그런데 시트이름에 dash (-) 가 포함되어 있으면 작동되지 않던데, 원래 그런가요?
안녕하세요. dash가 포함되어 있으면 시트명 앞에 홑따옴표를 붙여야 합니다. 수식을 아래와 같이 바꾸면 됩니다.
=VLOOKUP(C14,INDIRECT("'"&C13&"'" & "!A1:C11"),3,FALSE)
수식을 고쳐서 다시 올려 두었습니다. 혹시 위의 수식을 붙여넣어서 안되면 다시 실습파일을 다운로드해서 사용하세요.