엑셀에서 일치하는 문자열 찾아 이동하는 하이퍼링크 생성 방법

오늘 포스팅은 오랜만에 엑셀 함수에 대해서 포스팅을 해보려고 합니다. 올해에는 엑셀 함수에 관한 팁도 많이 올려 보려고 생각 중이거든요. 이번 포스팅에서는 일치하는 문자열을 찾아서 해당 문자열이 있는 위치로 이동할수 있도록 하이퍼 링크를 자동으로 생성해주는 함수에 대해서 알아 보려고 합니다. 

물론 하나의 함수가 아니라 몇개의 함수를 섞어서 해당 기능을 사용할수 있도록 하는 방법입니다. 이번 포스팅에서 사용하는 함수는 HYPERLINK, CELL, INDEX, MATCH 입니다. 

일단 아래 이미지에서 보시는것처럼 전체 함수를 한번 보도록 하겠습니다. 

=HYPERLINK("#"&CELL("address",INDEX($E$2:$E$8,MATCH(B3,$E$2:$E$8,0))),B3)

기능은 간단합니다. 지역 옆에 바로가기에 해당 지역 단어가 처음에 나오는 위치로 이동할수 있도록 하이퍼링크를 만들어 주는건데요. 아래 예에서는 수원을 누르면 옆에 수원이 있는 셀로 바로 이동을 하게 됩니다. 

엑셀 함수 예제

그럼 전체 함수를 분해 해서 알아 보도록 하겠습니다. 조금더 이해를 하기 쉽도록 각각 함수를 분해 해서 실행했을때의 결과도 아래 추가해 보았습니다. 직접 해보시면 더 이해가 쉽겠지만 바로 해보실수 없다면 아래 이미지를 참고해서 보시면 좋을거 같네요.

엑셀 함수 예제

함수는 가장 안쪽에 있는 함수부터 알아 보도록 하겠습니다. 

=HYPERLINK("#"&CELL("address",INDEX($E$2:$E$8,MATCH(B3,$E$2:$E$8,0))),B3)

MATCH 함수는 MATCH(찾을값, 범위, 옵션) 으로 사용을 하는데요. 범위내에서 찾을값과 일치하는 위치를 반환을 해주게 됩니다. 그럼 풀어서 넣으면 아래처럼 될거 같네요.

MATCH( 찾을값 B3 = "수원"  , 범위 = E2~E8 , 옶션 = 0 정확히 일치하는 값으로 찾기 ) 

그래서 결과는 수원이 첫번째에 들어 있기 때문에 1을 반환을 하게 됩니다. 

=HYPERLINK("#"&CELL("address",INDEX($E$2:$E$8,MATCH(B3,$E$2:$E$8,0))),B3)

INDEX 함수는 INDEX(범위, 행번호, [열번호], [배열번호]) 로 사용이 되는데요. 범위에서 행번호에 해당하는 값을 반환해주는 함수 입니다. 이때 배열형으로 사용할 경우에는 값을, 참조형인 경우에는 셀의 위치를 반환해 준다고 합니다. 

INDEX( 범위 = E2 ~ E8, 행번호 = MATCH 에서 찾은 1 ) 

그냥 index 함수를 사용하면 값이 표시가 되지만 여기에 CELL 함수를 사용해서 위치를 반환 하도록 만든 함수 입니다. 

=HYPERLINK("#"&CELL("address",INDEX($E$2:$E$8,MATCH(B3,$E$2:$E$8,0))),B3)

CELL 함수는 CELL (정보유형, 셀위치) 의 형태로 사용을 하는데요. 셀에 대한 각종 정보를 받아 올수가 있습니다. 여기에서 사용한 address 는 셀의 위치라고 보면 될거 같습니다. 

마지막으로 HYPERLINK 의 경우에는 HYPERLINK(링크주소, 표시이름) 으로 링크를 걸어 주는 함수 입니다. 여기에서는 최종적으로 #을 붙여서 셀위치로 이동을 할수 있도록 하이퍼링크를 걸어 주는 용도로 사용이 되었습니다. 표시이름은 B3 로 찾는 텍스트 이름과 똑같이 넣어서 하이퍼링크를 만들게 됩니다. 

실제로 위 함수의 실행 결과는 아래 움직이는 이미지를 참고 하시면 될거 같습니다. 

엑셀 함수 결과

이상으로 오랜만에 포스팅 해본 엑셀 함수 포스팅이였습니다. 엑셀에서 일치하는 문자열 찾아 이동하는 하이퍼링크 생성 방법이였는데 도움이 되실지 모르겠네요 ^^ 앞으로 종종 엑셀 함수 팁을 열심히 포스팅 해보도록 하겠습니다. 위 함수는 인터넷에서 Excel formula 에서 참고 했습니다. 

그럼 도움이 되셨길 바라면서 이만 포스팅 마치도록 하겠습니다. 하단에 구독 & 공감 & 댓글 많이 부탁 드립니다. 여기까지 보아 주셔서 감사드려요 ^^