엑셀 vlookup, match, index 함수사용

[케에액]

엑셀에서 다른 셀이나 워크 시트의 자료를 가져와서 사용하는 방법입니다.

http://cafe.naver.com/excelmaster/39

엑셀 vlookup, match, index 함수사용방법....스크랩이 안되서 위주소로 가시면 됩니다.

유용하게 쓰실분이 계실까? 해서

수고하세요~

참고로 vlookup 함수는 key값이 맨 왼쪽에 있어야합니다.

====================================================================================================================

다른 셀, 워크쉬트, 워크북에 있는 값을 가져와서 사용하려면 어떻게 해야 할까요..

A1 셀에 들어 있는 정보를 참조하려면..

=A1

Sheet2에 있는 A1 셀에 있는 정보를 참조하려면..

=Sheet2!A1

C:\xlSample.xls 파일의 Sheet1에 있는 A1 셀에 있는 정보를 참조하려면..

='C:\ExcelFiles\[xlSample.xls]Sheet1'!A1

여기까지는 누구나 쉽게 알 수 있는 부분들입니다만.. 특정조건이 일치하는 행의 다른 열의 값을 가져와야 할 때는 적절한 함수를 사용해야 하는데..  vlookup, index, match 함수등을 많이 사용하게 됩니다.

데이터가 들어 있는 화면을 볼까요..

(데이터는 Northwind.mdb 에서 가져왔습니다.)

이런 경우를 생각해 보죠.. 해당 상품을 주문했을때, 주문된 상품 리스트에 재고량을 확인해야 한다면.. 위의 리스트를 보고 일일이 적어줘야 할까요..

이럴때.. vlookup 함수를 사용하면 쉽게 문제를 해결 할 수 있습니다.

일단 결과화면 먼저 볼까요..

=VLOOKUP(G2, B2:D47, 3, FALSE)  자 이런식으로 수식이 구성되어 있죠..

G2 : 찾을 값 (상품명으로 재고량을 확인하려고 하기 때문에 상품명이 들어있는 G2 셀을 참조)

B2:D47 : 상품명을 어디서 찾아야 하는지 찾을 범위를 지정해 주는 겁니다.

               그러면 B 열만 참조하면 되는 것 아닌가 하시는 분이 있을지 모르는데요.. 재고량을

               알기 위해서는 재고량이 저장되어 있는 D열까지 참고해야 합니다.

3 : 이 숫자는 B(1),C(2),D(3) 이렇게 주어진 범위의 열 번호입니다.  재고량이 D 열에 있음으로

      숫자는 당연히 3이어야 하겠죠.

FALSE : 첫 열(여기서는 B 열이겠죠)이 오름차순으로 정렬되어 있는 경우 TRUE 값을, 그렇지

            않은 경우 FALSE 값을 사용해야 합니다.  습관적으로 FALSE 값을 적어주시는 것이

            좋습니다.  TRUE 값을 적은 다음 정렬이 오름차순으로 되어 있지 않은 경우 정확한 값

            을 반환하지 못할 수도 있습니다.

이해가 되시죠..

그런데.. 이렇게 유용한 vlookup 함수도 약점이 있습니다.  찾을 값(제품 이름)과 참조하려는 값(재고량)의 순서가 항상 찾을 값이 먼저 존재해야 한다는 겁니다.

즉, 참조하려는 값이 찾을 값보다 왼쪽 열에 존재할 경우 vlookup 함수를 사용해서 값을 불러 올수 없습니다. 

또 하나는 찾을 값이 참조하려는 범위의 왼쪽 첫번째 열에 존재해야 한다는 겁니다. (위의 예제를 보면 B,C,D 열을 참조하고 있는데.. 찾을 값인 제품 이름이 B열로 첫번째에 위치하고 있죠..)

이런 규칙만 이해한다면.. vlookup 함수는 편리하게 사용할 수 있는 함수라고 할 수 있습니다.

그렇다면 위의 예외에 해당하는 사항의 경우는 어떤 함수를 사용하면 될까요?  앞에서 설명한대로 INDEX, MATCH 함수를 혼용해서 사용하면 되는데..  예제 화면을 한번 보시죠..

먼저 재고량 위치를 B 열로 이동시킨 다음 재고량을 구하는 방법에 대해 알아 보겠습니다.

아래 vlookup 함수를 보시면 재고량에 값이 제대로 표시되지 않는 걸 확인하실 수 있죠.. 그렇지만 그 아래.. index, match 함수를 혼용해 사용할 경우 재고량이 제대로 표시되죠..

 

 

수식에 대해 하나 하나 살펴볼까요.. (오늘은 화면도 있고 해서 너무 길어지네요..)

 

=INDEX($B$2:$B$11, MATCH(G8, $C$2:$C$11, 0))

 

MATCH(G8, $C$2:$C$11, 0)  이 부분을 살펴보죠..  Match 함수는 찾을값이 범위에서 몇 번째 행에 값이 들어 있는지 행번호를 반환합니다.

 

G8 : 찾을 값 인 제품 이름값이 들어 있는 셀 주소

$C$2:$C$11 : 제품 이름이 들어 있는 데이터 범위 주소

0 : 값이 일치하는 첫번째 행의 값을 반환하라는 옵션값입니다.  1, -1 등의 값이 있는데.. 각각

     오름차순, 내림차순으로 정렬된 경우에만 정확한 값을 반환합니다.

 

자 이렇게 행 번호를 안 다음, Index 함수를 사용하는데요..  Index 함수는 범위내의 행, 열번호에 맞는 값을 반환합니다.

 

=INDEX($B$2:$B$11, 3, 0)

 

위 수식은 B2:B11 범위내에서 3번째 행의 값을 반환하라는 수식입니다.  여기서 3 의 값은 Match 함수로 대치하면 위의 수식이 되겠죠..

 

0(열을 지정하는 수) 은 범위가 한개 열에 해당하므로 생략해도 됩니다.

 

좀 길긴 했지만.. 이해해 두시면 두고두고 유용하게 사용할 수 있습니다.    다음은 조건이 여러개일 경우의 값을 참조하는 방법에 대해 알아보겠습니다.