엑셀 공부 : 고급 VLOOKUP 기술

고급 VLOOKUP 기술

5. 고급 VLOOKUP 기술

VLOOKUP은 간단한 데이터 조회뿐 아니라 다른 시트나 외부 파일의 데이터를 참조하고, 데이터 변경에 따라 동적으로 범위를 설정할 수도 있습니다. 이 섹션에서는 이러한 고급 기술들을 하나씩 살펴보겠습니다.

---

1. 다른 시트 참조

VLOOKUP으로 같은 파일 내의 다른 시트를 참조하여 데이터를 검색할 수 있습니다.

예시

Sheet1 (데이터가 있는 시트):

	A	B
1	이름	점수
2	김철수	85
3	이영희	90

 

Sheet2 (검색할 시트):

"김철수"의 점수를 찾으려면 아래와 같이 입력합니다.

 

함수 입력:

=VLOOKUP("김철수", Sheet1!A1:B3, 2, FALSE)

설명:

• Sheet1!A1:B3는 Sheet1의 데이터 범위를 참조합니다.
• 결과: 85

=VLOOKUP 을 걸어 둔 상태에서 범위를 지정할 때 마우스를 이용해서 시트를 이동해서 지정해주면 된다.

키보드로 할때는 컨트롤 + 페이지 다운 , 페이지 업을 누르면 시트간 이동할 수 있다.

---

2. 외부 파일 데이터 조회

다른 엑셀 파일에서 데이터를 검색할 때, 파일 경로를 지정하여 참조할 수 있습니다.

예시

외부 파일: C:\data\students.xlsx
Sheet1에 아래 데이터가 있다고 가정합니다.

	A	B
1	이름	점수
2	김철수	85
3	이영희	90

 

함수 입력:

=VLOOKUP("김철수", '[students.xlsx]Sheet1'!A1:B3, 2, FALSE)

설명:

• '[students.xlsx]Sheet1'!A1:B3는 외부 파일 students.xlsx의 Sheet1 범위를 참조합니다.
• 외부 파일이 열려 있어야 정상 작동합니다.

이것도 =VLOOKUP 을 해서 작성을 하다가 범위에서 알트탭 혹은 다른 열어둔 파일을 가서 범위를 지정하면 된다.

대신  하나의 엑셀프로그램에서 열린 파일이여야 한다. 이 개념은 역시나 말로는 설명하기 어려운데.. 간혹 새로운 프로그램에서 엑셀이 열릴 경우가 있다. 혹은 그렇게 설정해둔 사용자들이 있다.. 혹은 어떻게 설정한지도 모르는...

프로그램이 다르면 마우스 클릭으로는 참조를 걸 수 가 없다.. 하나의 프로그램에서 파일이 열려야 가능하다는거.. 일단은 이렇게 알고 넘어가자.

---

3. 동적 범위 설정

데이터가 늘어나거나 줄어들 경우, 고정된 범위 대신 동적 범위를 사용하면 자동으로 범위를 확장하거나 축소할 수 있습니다.

방법 1: 표(Table) 기능 사용

1. 데이터를 선택하고 Ctrl+T를 눌러 표로 변환합니다.
2. 표의 이름을 지정합니다(예: StudentTable).
3. VLOOKUP에서 표 이름을 사용합니다.

예시:

=VLOOKUP("김철수", StudentTable, 2, FALSE)

 

자주 쓰는 목록의 경우 매번 범위를 지정하지 않고, 표 이름으로 범위를 만들 수 있다.

이 방법은 굉장히 유용한 방법이므로, 한번 설정할 때 귀찮을 수 있지만 쉽게 작성하기에는 편하다.

 

방법 2: OFFSET 함수로 동적 범위 설정

OFFSET 함수와 COUNTA 함수를 결합하여 데이터 범위를 자동으로 설정합니다.

 

예시:

 

	A	B
1	이름	점수
2	김철수	85
3	이영희	90

 

함수 입력:

=VLOOKUP("김철수", OFFSET(A1, 0, 0, COUNTA(A:A), 2), 2, FALSE)

설명:

• OFFSET(A1, 0, 0, COUNTA(A:A), 2)는 A열의 데이터 개수만큼 범위를 확장합니다.

---

 

복습해보자.. 어떤걸 ? VLOOKUP 사용법.

 

VLOOKUP 사용법은 계속 되뇌이고, 되뇌여야 외워진다.

 

VLOOKUP ( 찾을 값 , 찾을 표(범위) , 몇번째 있지? , 정확하게? (FALSE / TRUE) )

1번째 찾을 값은 문자면 쌍따옴표 안에 넣어준다. 숫자면 그냥 써도 된다. 다만 찾는 표(범위) 에서 숫자로 있는지 문자로 있는지도 알아야 한다.

2번째 찾을 표(범위) 는 항상 내가 찾고자 하는 값이 제일 왼쪽에 있어야 한다.

3번째 몇번째 있지? , 내가 찾을 값이 항상 표에서 1번째가 된다. 그곳부터 1 이다 1,2,3,4 이런식으로 속으로 새면 된다.

(필자도 그렇다 ㅎ)

4번째 FALSE / TRUE 의 경우 0 과 1 로 써도 된다. 0 = FALSE , 1 = TRUE