엑셀 실무] VLOOKUP, INDEX, MATCH 함수를 사용하여 다른 시트에서 이미지 가져오기

VLOOKUP 함수를 사용하여 다른 시트에 있는 이미지를 가져오는 방법을 알고 싶다는 질문을 종종 받습니다.

그래서 준비해 봤습니다.

 

실습 파일 다운로드

제품설명서.xlsx

1.38MB

 

완성 파일 다운로드

제품설명서_완성.xlsx

1.43MB

먼저 [제품 목록] 시트에 품목별 이미지 정보와 단가를 정리해 두었습니다.

그런 다음 [제품 설명서] 시트에서 제품명을 목록에서 선택하면 단가와 제품 이미지를 제품 목록에서 찾아오도록 수식을 작성하는 방법을 소개하겠습니다.

 

[제품 목록] 시트

[제품 설명서] 시트

이름 정의

수식에서 다른 시트에 있는 셀 또는 범위를 참조하면 불편하기 때문에 수식에 사용할 범위를 이름으로 정의해서 사용하면 편리합니다.

먼저 이름을 정의하겠습니다.

 

1. 품명 범위 [B3:B10]를 선택하고 이름 상자에 품명이라고 입력하고 <Enter>를 눌러 이름을 정의합니다.

2. 제품 목록 전체 범위 [B3:D10]를 선택하고 제품목록이라고 입력하고 <Enter>를 눌러 이름을 정의합니다.

 

제품명을 선택하는 목록 만들기

 

1. [C4] 셀을 선택하고 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 실행하고 [제한 대상] → [목록]을 선택합니다.

 

2. 원본 입력창에 =품명이라고 입력하고 [확인] 버튼을 클릭합니다.

 

제품 설명서에서 제품명 목록 버튼을 눌러보세요.

 

 

 

VLOOKUP 함수를 사용하여 단가가 자동으로 입력되도록 설정하기

1. 단가를 구할 [C5] 셀을 선택하고 =VLOOKUP()이라고 입력한 다음 함수 삽입 대화 상자를 실행합니다.

2. Lookup_value 인수는 [C4] 셀을 참조하고 Table_array 인수 입력창에 정의해 놓은 이름 제품목록을 입력합니다 .

3. Col_index_num 인수는 3, Range_lookup 인수 입력창에 0을 입력하고 [확인] 버튼을 클릭합니다.

 

 

 

---------------------------------------------------------------------------------------

 

여기서 한가지 더!

VLOOKUP 함수가 아닌 INDEX + MATCH 함수를 사용하여 단가를 찾아오는 방법을 소개하겠습니다.

 

INDEX + MATCH 함수를 사용하여 단가가 자동으로 입력되도록 설정하기

 

1. 단가를 구할 [C5] 셀을 선택하고 =INDEX()라고 입력한 다음 함수 삽입 대화 상자를 실행합니다.

2. Array 인수 입력창에 커서를 두고 ‘제품목록’이라고 입력하고 Row_num 인수 입력창에 MATCH() 함수를 중첩합니다.

그리고 수식 입력줄에서 MATCH() 함수를 선택합니다.

 

3. MATCH 함수 삽입 대화 상자로 바뀌면 Lookup_value 인수 입력창에 [C4] 셀을 지정합니다.

 

4. Lookup_array 인수는 제품목록 시트에 품명 범위를 이름으로 정의해둔 품명을 입력하고 Match_type 인수 입력창에 0을 입력한 다음 수식 입력줄에서 INDEX 함수를 선택합니다.

 

 

5. Column_num 인수 입력창에 3을 입력합니다. 단가는 Array 인수로 지정한 제품목록 범위의 세 번째 열에 있습니다.

 

 

6. 정확하게 제품명에 대한 단가를 구했습니다.

 

 

 

제품 이미지를 찾아올 수식을 이름으로 정의하기

 

단가를 제품목록에서 찾아오듯이 이미지는 가져올 수 없습니다.

INDEX와 MATCH 함수를 사용한 수식을 이름으로 정의해서 이미지 개체에 대입해야지 가져올 수 있습니다.

이름 상자에서는 함수 삽입 대화상자를 사용할 수 없어 직접 수식을 작성해야 합니다.

 

1. [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 선택합니다.

2. [새 이름] 대화상자가 실행되면 [이름] 입력창에 불러오기라고 입력합니다.

3. 참조 대상 입력창에 좁아 수식을 작성하기 불편하기 때문에 새 이름 대화상자 크기를 늘려줍니다.

 

4. [참조 대상] 입력창에 =INDEX(제품목록,MATCH('제품 설명서'!$C$4,품명,0),2)라고 수식을 작성하고 [확인] 버튼을 클릭합니다 .

 

5. [이름 관리자] 대화상자에 ‘불러오기’ 이름이 추가된 것을 확인할 수 있습니다.

 

----------------------------------------------------------------------------------------

 

이름으로 정의한 INDEX + MATCH 함수가 어렵다면 이 글을 참고하세요.

 

이름으로 정의된 수식은 다음과 같습니다.

함수 삽입 대화상자에서 수식을 작성하다가 입력된 수식을 보니 어렵게 느껴지죠? 한 단계씩 수식을 풀어서 설명해 보겠습니다.

=INDEX(제품목록,MATCH('제품 설명서'!$C$4,품명,0),2)

먼저 INDEX 함수의 첫 번째 Array 인수는 미리 이름으로 정의해둔 제품목록입니다.

제품목록에서 2번열에 있는 이미지 정보를 가지고 오려고 하는 거죠.

하지만 [제품 설명서]에서 선택된 제품명에 맞는 이미지 정보를 찾아와야 하기 때문에 제품명을 선택할 때마다 찾을 품명은 달라집니다.

그래서 MATCH 함수를 INDEX 함수에 중첩하여 [제품 설명서]에서 선택된 제품명에 맞는 행 번호를 알아야 합니다.

 

 

MATCH 함수는 [제품 설명서]에서 선택한 제품명을 이름으로 정의해둔 품명 범위에서 찾아 맨 먼저 일치하는 품명의 행 번호를 구해줍니다.

‘모니터’를 선택하면 MATCH('제품 설명서'!$C$4,품명,0) 수식에 의해 3을 구합니다.

 

다시 전체 수식을 보면서 설명하겠습니다.

=INDEX(제품목록,MATCH('제품 설명서'!$C$4,품명,0),2)

INDEX 함수는 제품목록에서 MATCH 함수가 구한 3행과 INDEX 함수의 3번째 인수(열 번호) 2열에 있는 노트북 이미지를 가져오게 되는 겁니다.

 

 

-----------------------------------------------------------------------------------------

제품 이미지 불러오기

 

1. [제품 목록] 시트에서 이미지 하나를 복사한 다음 [제품 설명서] 시트 [C6] 셀에 붙여 넣기 합니다.

2. 이미지가 선택된 상태에서 수식 입력줄에 커서를 두고 =불러오기라고 입력하고 <Enter> 누릅니다 .

선택한 제품명에 맞는 이미지로 변경됩니다 .

 

 

3. 제품명을 선택하면 단가와 제품 이미지가 자동으로 표시됩니다.

 

----------------------------------------------------------------------------------

 

2007 버전에서는 조금 다릅니다.

 

엑셀 2007 버전에서만 이미지를 선택한 상태에서 수식 입력줄에 커서가 생기지 않습니다.

2007 버전에서 이미지에 이름을 대입하는 방법을 소개하겠습니다.

 

1. [C6] 셀을 복사한 후 [홈] 탭 → [클립보드] 그룹 → [붙여넣기 ▼] → [그림 형식 ▼] → [연결하여 그림 붙여넣기]를 실행합니다.

 

2. 붙여넣기 한 그림을 선택한 다음 수식 입력줄에 커서를 두고 =불러오기 라고 입력하고 <Enter> 누릅니다.

 

 

 

---------------------------------------------------------------------------------------------------------------

 

이전에 소개한 INDEX + MATCH 사용법을 아래에 링크 걸어 두겠습니다.

참고하세요.

 

INDEX + MATCH 함수의 기본 사용법 https://hantip.net/48?category=676450

엑셀 45강] index, Match 함수의 기본 사용법

예제를 통한 INDEX와 MATCH 함수 익히기 https://hantip.net/49?category=676450

엑셀 46강] 예제를 통한 index와 Match함수 익히기

INDEX, MATCH 함수를 활용한 다른 시트의 그림 불러오기 https://hantip.net/51?category=676450

엑셀 48강] index, Match함수를 활용한 다른 시트의 그림 불러오기

필요한 모든 분들께 이 강좌가 도움이 되기를 바랍니다.