1일1함수(2) : VLOOKUP 세로방향 데이터 목록에서 값 가져오기

VLOOKUP함수는 표형태에서 첫째열에서 값을 찾아 지정된 수만큼 오른쪽으로 이동한 곳의 값을 반환하는 함수입니다.

VLOOKUP(A,범위B,숫자,옵션)

범위B에서 A의 값을 찾은 후에, 해당숫자만큼 이동한 후 값을 반환합니다. 옵션에 FALSE로 쓰면 정확한 값을 찾고, 안 쓰거나 TRUE를 쓰면 비슷한 값을 찾습니다.

학생명렬표에서 특정 학생의 이름을 입력하면, 그 학생의 주소나 전화번호등이 나오는 수식을 만들어 봅시다.

다음처럼 표를 만듭니다. 이때 주의할 것은, VLOOKUP함수는 반드시 첫째열에서 값을 검색하기 때문에, 찾을 고유의 이름이 있는 열을 맨 첫열로 보내어두는 습관을 들이는 것이 좋습니다. 지금처럼, 이름을 검색해서 찾는다면 이름이 들어있는 열을 맨 첫열로 보내는 것이 찾기가 쉽겠지요.

그리고 검색할 폼을 만듭니다. 허접하지만, 다음과 같이 만들어 보았습니다.

파란색 사각형 속에 학생의 이름을 넣으면, 번호, 주소, 학생전화번호, 계발활동반이 입력됩니다.

번호란 (F3셀)에 들어가는 수식은 다음과 같습니다.

=VLOOKUP($H$3,sheet1!$A$1:$H$11,2,FALSE)

=VLOOKUP(
$H$3	H3셀의 값을
,sheet1!$A$1:$H$11,	SHEET1의 A1:H11의 범위의 첫열(여기서는 A열)에서 찾아서
2,	오른쪽으로 첫번째 열의 값을 출력하라
FALSE)	단, H3셀의 값과 똑같은 값을 찾아야한다(비슷한 값을찾으면안된다)

여기서 주의하실 것은, 숫자가 2가 입력되었으니까 오른쪽으로 "두 칸 가서" C열의 주소를 출력한다고 생각하면 안된다는 것입니다. 1이면 범위의 첫열이, 2이면 오른쪽으로 한 칸 옆의 열이, 3이면 오른쪽으로 두 칸 옆의 열이 출력됩니다. 수식에 입력하는 숫자와 "오른쪽으로 이동한 칸의 숫자"가 다른 것입니다. 그러므로 VLOOKUP함수에서 세번째 인수의 의미는 "범위의 첫열을 1로 셈한다"고 생각해두시면 편합니다.


그럼, 주소를 보여주는 B5셀의 수식은 어떻게 될까요. 아래를 긁으면 답이 나옵니다만, 답을 보기전에 한번 생각해 보세요.
=VLOOKUP($H$3,sheet1!$A$1:$H$11,3,FALSE)

나머지 셀의 수식은 오늘의 연습문제입니다. 
학생전화번호 
계발활동반 

*참고* 찾은 값의 오른쪽으로만 참조하게 되어있는 게 불편해서, 찾은 값의 왼쪽의 열을 참조하는 방법이 없을까하고 연구해 보았는데, 결론은 "없다"입니다. VLOOKUP함수를 사용하려면, 찾을 값은 반드시 첫열에 놓아야한다는 것을 다시한번 명심해야합니다. 
(정 모양상 이름이 맨앞에 오는게 부담스러우면 첫열을 숨김처리하셔도 수식은 이상없이 작동되니, 이름이 나오는 열을 A열과 C열 두군데에 넣으시고, A열은 숨겨두셔도 되겠습니다)

p.s. 2011.2.15 위에서는 그런 방법은 "없다"고 했는데, 좀 더 연구해보니 완벽하게는 아니지만 비슷한 효과를 내는 방법이 있네요. index함수와 match함수를 이용하는 방법입니다. 참고하세요


2009/03/20 - [컴퓨터/엑셀] - 1일1함수 보충(2-1): VLOOKUP 함수에서 주의할 점
2009/03/25 - [컴퓨터/엑셀] - 1일1함수(6) : HLOOKUP 함수 -- 가로방향 데이터 목록에서 값 가져오기
2010/08/31 - [컴퓨터/엑셀] - [함수추가] INDEX함수 : 범위속에서 지정한 만큼의 위치에 있는 값 [오른쪽 열 기준으로 왼쪽열을 찾을때 (VLOOKUP함수의 반대 방향)]