엑셀의 INDIRECT 함수는 문자열로 만들어진 참조를 유효한 셀 참조로 바꿔줍니다. 수식은 바꾸지 않고 문자열로 만들어진 참조만 변경해서 결과값을 가져와야 할 때 사용할 수 있습니다.
예를 들면 VLOOKUP 함수를 사용할 때 값을 찾는 시트의 이름이 그때 그때 바뀌어야 한다면 INDIRECT 함수를 이용하면 간단하게 해결할 수 있습니다.
구문(Syntax)
INDIRECT(ref_text, [a1])
문자열로 만들어진 참조를 유효한 셀 참조로 바꿔준다.
인수
- ref_text : 참조할 문자열
- a1 : 생략가능(생략시 기본값은 TRUE), TRUE- A1 스타일 참조 사용, FALSE – R1C1 스타일 참조 사용
사용 예
1) 문자열로 만들어진 참조를 반환하기
INDIRECT 함수를 응용하기 전에 간단한 예를 먼저 살펴보겠습니다.
위 시트의
[C4], [C5] 셀에는 각각 '안녕하세요', '처음 뵙겠습니다'가 입력되어 있습니다.
두 개의 수식 모두 '안녕하세요'를 가져옵니다.
어떻게 해서 두 개의 수식이 같은 결과를 보여주는지 살펴보겠습니다.
[C7] 셀에 숫자 4가 입력되어 있으므로=INDIRECT("C"&C7)는 = INDIRECT("C4") 수식과 같습니다.
INDIRECT 함수는 문자열로 만들어진 참조를 유효한 셀 참조로 바꿔주므로
= INDIRECT("C4")는 =C4 수식으로 바뀌고
[C4] 셀의 '안녕하세요'를 가져옵니다.
이번에는 [C7]에 입력된 4를 5로 바꿔보겠습니다.
결과는 '안녕하세요'에서 '처음 뵙겠습니다'로 바뀌었습니다. [C7] 셀에 5가 입력되어 있으므로
=INDIRECT("C"&C7)는 = INDIRECT("C5") 수식과 같고
= INDIRECT("C5")는 =C5 수식으로 바뀌므로
[C5] 셀의 '처음 뵙겠습니다'를 가져옵니다.수식은 바꾸지 않고 [C7] 셀에 입력된 값만 4에서 5로 바꾸면 참조하는 셀이 [C4]에서 [C5]로 바뀐 것입니다.
INDIRECT 함수의 이러한 기능을 다른 함수와 조합해서 응용하면 수식은 바꾸지 않고 참조하는 값을 바꿔가면서 결과를 바꾸고자 할 때 유용하게 사용할 수 있습니다.
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(C18,INDIRECT("'"&C17&"'" & "!A1:C11"),3,FALSE)
[C17] 셀에 시트명을 "1월-실적"으로 입력했므로 INDIRECT("'"&C17&"'" & "!A1:C11") 수식이 "1월-실적" 시트를 참조하고 최종적으로 VLOOKUP함수가 1월 판매실적을 찾아줍니다.
관련 글
데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기 - 이번 글에는 엑셀의 '데이터 유효성 검사' 기능을 이용하여 상위목록에 종속되는 하위목록을 만드는 방법을 알려드리겠습니다. 데이터 유효성 검사에 대한 전반적인 내용은… …
ADDRESS 함수 - 행,열 번호로 셀주소 표시하기 - 엑셀의 ADDRESS 함수는 행과 열의 번호를 이용하여 셀 주소를 표시해 줍니다. 셀주소 자체만으로는 쓸 일이 별로 없기 때문에 ADDRESS 함수는… …
도움이 많이 되었습니다. 감사합니다.
그런데 시트이름에 dash (-) 가 포함되어 있으면 작동되지 않던데, 원래 그런가요?
안녕하세요. dash가 포함되어 있으면 시트명 앞에 홑따옴표를 붙여야 합니다. 수식을 아래와 같이 바꾸면 됩니다.
=VLOOKUP(C14,INDIRECT("'"&C13&"'" & "!A1:C11"),3,FALSE)
수식을 고쳐서 다시 올려 두었습니다. 혹시 위의 수식을 붙여넣어서 안되면 다시 실습파일을 다운로드해서 사용하세요.
안녕하세요. 시트명 뿐만 아니라 파일명도 Indirect로 가능한가요?
[A.xls]sheet1!C12 이와 같이 만들려고
C1 = "[A.xls]sheet1!"
VLOOKUP(C14,INDIRECT("'"&C1&"'" & "!A1:C11"),3,FALSE) 이와 같이하면 되나요?
시트명만 바꾸는것은 되는데 파일명까지 하는것은 에러나네요
다음과 같이 하시면 됩니다.
=VLOOKUP(C14,INDIRECT("'[A.xls]" & C1 & "'" & "!A1:C11"),3,FALSE)
C14 : lookup_value, C1 : 시트명
안녕하세요 잘 보았는데요, 시트명에 공백이 있으면 작동하지 않던데,
홑따옴표를 붙여도요. 어떻게 해결할 수 없을까요??
안녕하세요. 다음과 같이 하면 시트명에 공백이 있어도 작동합니다.
=VLOOKUP(C17,INDIRECT("'"&C16&"'" & "!A1:C11"),3,FALSE)
홑따옴표 앞뒤로 공백이 있거나 하면 오류가 납니다.
계속해서 오류가 나면 파일을 admin@xlworks.net으로 보내주시면 확인하고 답변드리겠습니다.
시트명이 10월26일로 되어있고
위 엑셀에서 c17에 해당하는셀은 =TODAY()-1 즉 셀에표시되는건 10월26일 로 되어있습니다.
보기에는 같은데 엑셀에선 이상없는데 스프레드시트에 적용하려하면 44860 어쩌구 유효한 셀범위참조가 아니라고 뜨면서 안됩니다.
어떻게 방법이없을까요? ㅠ
[C17] 셀에 날짜를 입력해도 실제로 내부적으로는 숫자 44860으로 저장됩니다. 따라서 시트의 이름이 44860이어야 찾을 값을 수 있습니다.
다른 방법은 다음과 같이 날짜 앞에 아포스트로피를 붙여서 텍스트로 인식하게 하면 됩니다.
'2022-10-26
대신 TODAY()-1과 같은 수식은 사용할 수 없겠지요.
날짜가 숫자로 저장되는 이유는 다음 글을 참고하세요.
https://xlworks.net/excel-function-date-getting-started/