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 함수를 사용하여 단가가 자동으로 입력되도록 설정하기

 

ㅊㅊㅊㅊㅊ

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 함수는 단독으로 사용하기보다는 두 함수를 중첩해서 사용하면 활용도가 높습니다. Index 함수는 표 또는 범위에서 행과 열이 일치하는 값을 찾는 함수입니다. 인수로는 범위, 행 번호, 열 번..

hantip.net

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

 

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

index와 match 함수의 기본 사용법을 익혔다면 이번 강좌에서는 예제를 사용하여 index, match 함수를 잘 사용할 수 있도록 익히는 시간을 가져보겠습니다. 실습 데이터 다운로드 ↓ 하프 마라톤 기록에서 1위~3..

hantip.net

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

 

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

실습 데이터 다운로드 ↓ 다른 시트에 있는 그림을 불러오는 간단한 예제를 한 번 만들어봤습니다. 목록에서 이름을 선택하면 해당하는 그림이 표시되도록 하는 예제입니다. 그림 목록 시트에 각 이름별 그림을..

hantip.net

필요한 모든 분들께 이 강좌가 도움이 되기를 바라며. . .

 

+ Recent posts