엑셀 함수 > 찾기 및 참조영역 함수 > INDIRECT 함수 – 문자열을 참조로 바꾸기

INDIRECT 함수 – 문자열을 참조로 바꾸기

엑셀의 INDIRECT 함수는 문자열로 만들어진 참조를 유효한 셀 참조로 바꿔줍니다. 수식은 바꾸지 않고 문자열로 만들어진 참조만 변경해서 결과값을 가져와야 할 때 사용할 수 있습니다.

예를 들면 VLOOKUP 함수를 사용할 때 값을 찾는 시트의 이름이 그때 그때 바뀌어야 한다면 INDIRECT 함수를 이용하면 간단하게 해결할 수 있습니다.

 

구문(Syntax)

INDIRECT

INDIRECT(ref_text, [a1])

문자열로 만들어진 참조를 유효한 셀 참조로 바꿔준다.

인수

  • ref_text : 참조할 문자열
  • a1 : 생략가능(생략시 기본값은 TRUE), TRUE- A1 스타일 참조 사용, FALSE – R1C1 스타일 참조 사용
실습용 엑셀파일 다운로드 : INDIRECT-함수사용법.xlsx

 

사용 예

1) 문자열로 만들어진 참조를 반환하기

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

INDIRECT 함수로 문자열로 만들어진 참조를 반환하기

위 시트의
[C4], [C5] 셀에는 각각 '안녕하세요', '처음 뵙겠습니다'가 입력되어 있습니다.

[C9] 셀에 수식 =C4를 입력하고

[C10] 셀에는 수식 =INDIRECT("C"&C7)를  입력하면

두 개의 수식 모두 '안녕하세요'를 가져옵니다.

 

어떻게 해서 두 개의 수식이 같은 결과를 보여주는지 살펴보겠습니다.

[C7] 셀에 숫자 4가 입력되어 있으므로

=INDIRECT("C"&C7)= INDIRECT("C4") 수식과 같습니다.

INDIRECT 함수는 문자열로 만들어진 참조를 유효한 셀 참조로 바꿔주므로

= INDIRECT("C4")=C4 수식으로 바뀌고

[C4] 셀의 '안녕하세요'를 가져옵니다.

 

이번에는 [C7]에 입력된 4를 5로 바꿔보겠습니다.

INDIRECT 함수로 문자열로 만들어진 참조를 반환하기

[C10] 셀의 수식은 바뀌지 않았는데
결과는 '안녕하세요'에서 '처음 뵙겠습니다'로 바뀌었습니다.

[C7] 셀에 5가 입력되어 있으므로

=INDIRECT("C"&C7)= INDIRECT("C5") 수식과 같고

= INDIRECT("C5")=C5 수식으로 바뀌므로

[C5] 셀의 '처음 뵙겠습니다'를 가져옵니다.

수식은 바꾸지 않고 [C7] 셀에 입력된 값만 4에서 5로 바꾸면 참조하는 셀이 [C4]에서 [C5]로 바뀐 것입니다.

INDIRECT 함수의 이러한 기능을 다른 함수와 조합해서 응용하면 수식은 바꾸지 않고 참조하는 값을 바꿔가면서 결과를 바꾸고자 할 때 유용하게 사용할 수 있습니다.

 

2) 시트명을 바꿔가면서 값 찾기

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

INDIRECT 함수로 시트명을 바꿔가면서 값 찾기

 

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

사원번호가 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함수를 이용하면 하나의 수식으로 해결이 가능합니다.

INDIRECT 함수로 시트명을 바꿔가면서 값 찾기

=VLOOKUP(C18,INDIRECT("'"&C17&"'" & "!A1:C11"),3,FALSE)

[C17] 셀에 시트명을 "1월-실적"으로 입력했므로 INDIRECT("'"&C17&"'" & "!A1:C11") 수식이 "1월-실적" 시트를 참조하고 최종적으로 VLOOKUP함수가 1월 판매실적을 찾아줍니다.

 

INDIRECT 함수로 시트명을 바꿔가면서 값 찾기

[C17] 셀에 시트명을 "2월-실적"으로 입력하면 INDIRECT("'"&C17&"'" & "!A1:C11") 수식이 "2월-실적" 시트를 참조하고 최종적으로 VLOOKUP함수가 2월 판매실적을 찾아줍니다.

 

 


관련 글

데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기 - 이번 글에는 엑셀의 '데이터 유효성 검사' 기능을 이용하여 상위목록에 종속되는 하위목록을 만드는 방법을 알려드리겠습니다. 데이터 유효성 검사에 대한 전반적인 내용은…

데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기 더 보기 »

ADDRESS 함수 - 행,열 번호로 셀주소 표시하기 - 엑셀의 ADDRESS 함수는 행과 열의 번호를 이용하여 셀 주소를 표시해 줍니다. 셀주소 자체만으로는 쓸 일이 별로 없기 때문에 ADDRESS 함수는…

ADDRESS 함수 - 행,열 번호로 셀주소 표시하기 더 보기 »

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

엑셀웍스 책 출간안내  자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

“INDIRECT 함수 – 문자열을 참조로 바꾸기”의 10개의 댓글

  1. 도움이 많이 되었습니다. 감사합니다.
    그런데 시트이름에 dash (-) 가 포함되어 있으면 작동되지 않던데, 원래 그런가요?

    1. 안녕하세요. dash가 포함되어 있으면 시트명 앞에 홑따옴표를 붙여야 합니다. 수식을 아래와 같이 바꾸면 됩니다.
      =VLOOKUP(C14,INDIRECT("'"&C13&"'" & "!A1:C11"),3,FALSE)
      수식을 고쳐서 다시 올려 두었습니다. 혹시 위의 수식을 붙여넣어서 안되면 다시 실습파일을 다운로드해서 사용하세요.

  2. 안녕하세요. 시트명 뿐만 아니라 파일명도 Indirect로 가능한가요?
    [A.xls]sheet1!C12 이와 같이 만들려고

    C1 = "[A.xls]sheet1!"
    VLOOKUP(C14,INDIRECT("'"&C1&"'" & "!A1:C11"),3,FALSE) 이와 같이하면 되나요?
    시트명만 바꾸는것은 되는데 파일명까지 하는것은 에러나네요

    1. 다음과 같이 하시면 됩니다.
      =VLOOKUP(C14,INDIRECT("'[A.xls]" & C1 & "'" & "!A1:C11"),3,FALSE)
      C14 : lookup_value, C1 : 시트명

  3. 달봉맘

    안녕하세요 잘 보았는데요, 시트명에 공백이 있으면 작동하지 않던데,
    홑따옴표를 붙여도요. 어떻게 해결할 수 없을까요??

    1. 안녕하세요. 다음과 같이 하면 시트명에 공백이 있어도 작동합니다.
      =VLOOKUP(C17,INDIRECT("'"&C16&"'" & "!A1:C11"),3,FALSE)
      홑따옴표 앞뒤로 공백이 있거나 하면 오류가 납니다.
      계속해서 오류가 나면 파일을 admin@xlworks.net으로 보내주시면 확인하고 답변드리겠습니다.

  4. 시트명이 10월26일로 되어있고
    위 엑셀에서 c17에 해당하는셀은 =TODAY()-1 즉 셀에표시되는건 10월26일 로 되어있습니다.
    보기에는 같은데 엑셀에선 이상없는데 스프레드시트에 적용하려하면 44860 어쩌구 유효한 셀범위참조가 아니라고 뜨면서 안됩니다.
    어떻게 방법이없을까요? ㅠ

    1. [C17] 셀에 날짜를 입력해도 실제로 내부적으로는 숫자 44860으로 저장됩니다. 따라서 시트의 이름이 44860이어야 찾을 값을 수 있습니다.
      다른 방법은 다음과 같이 날짜 앞에 아포스트로피를 붙여서 텍스트로 인식하게 하면 됩니다.
      '2022-10-26
      대신 TODAY()-1과 같은 수식은 사용할 수 없겠지요.

      날짜가 숫자로 저장되는 이유는 다음 글을 참고하세요.
      https://xlworks.net/excel-function-date-getting-started/

  5. firstfairylele

    안녕하세요! 혹시 'indirect' 함수를 이용하여 다른 탭에 있는 값 (ex. A1 : F20) 을 원하는 위치에 옮길수도 있나요?

    1. 값을 옮긴다는 것이 어떤 의미인지 모르겠습니다. 말 그대로 해석하면 원래의 값을 복사해서 원하는 위치에 붙여넣고 원래의 값을 지운다는 것인데요. 이런 경우 VBA로 해야 합니다.
      옮기는 것이 아니라 참조해서 원하는 위치에 표시한다면 다음 수식으로 가능합니다.
      =TAKE(INDIRECT("'" & C17 & "'" & "!A1:C11"),3)

      TAKE함수 사용법:https://xlworks.net/excel-function-take/

댓글 남기기

Scroll to Top