포스트 목차
본 포스트에서는 Vlookup 함수를 활용하여, 테이블 형태로 나열되어 있는 데이터 중에서 데이터를 탐색하여 원하는 데이터를 가져오는 방법을 소개한다.
01. 질문
| 엑셀 질문입니다. 등기번호가 적혀있는 엑셀이 있고 스마트스토어엑셀로 등기번호를 보내려고 하는데 엑셀에서 등기번호가 적힌 엑셀에는 사람이름과 등기번호가 하나씩만 적혀있고 eg. ) 등기번호 | 홍길동 등기번호 | 아무개 스마트스토어 엑셀에는 사람이름 등기번호 칸이 여러개라 eg.) 빈칸 | 홍길동 빈칸 | 홍길동 빈칸 | 아무개 빈칸 | 아무개 등기번호를 옮길 때 한번에 같은 사람 이름 칸들에 등기번호가 들어가게 할 수 있나요? |
질문을 해석하면 다음과 같다.
- 두 개의 엑셀 파일이 있다. (등기번호가 적혀 있는 엑셀파일, 스마트스토어 엑셀 파일)
- 스마트스토어 엑셀파일에는 사람의 이름이 기록되어 있다.
- 스마트스토어 엑셀파일에는 동일한 이름이 여러 개 존재한다.
- 등기번호가 적혀 있는 엑셀 파일에는 등기번호와 사람의 이름이 적혀있으며, 사람이름마다 하나의 등기번호가 적혀있다.
- 스마트스토어 엑셀에 있는 사람 이름을 참고하여 옆칸에 등기번호를 들어가게 하고 싶다.
- 이 문제에서는 동명이인은 없다고 생각하는 것으로 사료 된다.
02. 문제 해결 방법
위 문제를 해결하기 위해서는 크게 두 가지 방법을 숙지하고 있어야 한다.
(1) 서로 다른 엑셀 파일에 있는 값을 참조하는 방법
다음과 같은 수식으로 다른 엑셀 파일에 있는 값을 참조할 수 있다.
='[파일명]시트명’!셀주소
두 개의 엑셀 파일이 있는 상황을 가정해보자.
참조대상.xlsx의 A1셀에는 aa라는 데이터가 입력되어 있다. 끌어오기 .xlsx의 A1셀에 위의 aa라는 값을 가져와 보자.
끌어오기 A1셀에서 =을 입력한 후, 참조대상.xlsx 파일의 A1셀을 선택해보자.
다음과 같은 수식이 채워지면서 참조한 값인 aa가 끌어오기.xlsx 파일의 A1셀에 표기되는 것을 확인할 수 있다.
| =[참조대상.xlsx]Sheet1!$A$1 |
이 상태에서 참조대상.xlsx 파일을 닫게 되면, 끌어오기.xlsx의 수식은 다음과 같이 참조대상.xlsx 파일이 있는 실제 경로로 변경된다.
해당 경로에 참조할 파일이 있다면 참조는 정상적으로 동작한다.
(2) 식별 가능한 기준 값을 이용하여 다른 값을 가져오는 방법
lookup 계열 함수 이용하기
본 포스트에서는 vlookup 함수를 이용하는 방법을 소개한다.
VLOOKUP 함수의 인수는 다음과 같다.
- Lookup_value: 찾을 대상이 되는 값
- Table_array: 찾을 내용이 저장되어 있는 영역
- Col_index_num: 찾을 대상이 있는 행에서 가져올 데이터가 있는 열의 번호(Table_array에서 지정한 영역 내에서의 열의 순서를 의미한다.)
- Range_lookup: 찾을 대상이 완벽하게 일치하기를 원할 경우 False, 유사한 대상을 찾을 경우 True
위와 같은 데이터가 있을 때 F2셀에 송장번호를 가져오는 작업을 진행해보자.
찾을 대상이 되는 값(Loolup_value)은 이름이며, 이 찾을 대상이 Table_array의 첫 번째 열에 위치해야 한다. Table_array는 A2:B4까지의 영역이다.
Col_index_num는 이름 다음에 위치하는 열이기 때문에 2를 넣어주면 된다. Range_lookup은 False로 지정하여 이름이 완벽하게 일치하는 경우에만 값을 가져오게 하자. False로 지정할 경우 일치하는 이름이 없다면 에러가 발생한다.
03. 예제
이제 다른 파일에 있는 데이터를 이용하여 질문자가 요청한 문제를 해결 해보자.
(1) 1번 파일 – 참조대상.xlsx
(2) 2번파일 – 끌어오기.xlsx
(3) 끌어오기.xlsx 에 수식 적용
=VLOOKUP(B2,[참조대상.xlsx]Sheet1!$A$2:$B$4,2,false)
위의 수식을 A열에 복사하자
원하는 결과를 얻을 수 있다.