[엑셀함수 1편] 직장인 필수 VLOOKUP (MATCH, INDEX 활용)

실무에서 데이터 관리할 때 가장 많이 쓰는 함수 중 하나인 VLOOKUP을 소개하려고 합니다. 

 

또한 VLOOKUP 함수는 한 가지 조건만 찾을 수 있지만, 다중조건일 때 활용할 수 있는 방법도 설명하겠습니다. 

 

1. VLOOKUP 함수 사용법

많은 데이터에서 원하는 값을 찾아주는 유용한 함수입니다. 

 

(1) VLOOKUP 함수는 아래와 같은 순서로 입력해 주면 됩니다. 

(찾을 값 선택, 전체 범위 선택 후 F4, 열 번호 입력, 0 입력 후 엔터)

 

(2) 아래 그림과 같이 제품명이 입력됩니다. 

 

(3) 만약 찾는 값이 없는 경우 아래 그림과 같이 에러값이 표출되는데 보고서로 쓰기 좋게 에러값을 "-"로 바꾸는 법을 아래에서 소개하겠습니다. 

 

(4) 입력한 VLOOKUP함수 앞에 IFERROR를 입력하고 , "-"를 추가합니다. 

 

(5) 아래 그림과 같이 에러값이 깔끔하게 표출되는 것을 확인할 수 있습니다. 

 

2. VLOOKUP함수 주의사항

VLOOKUP함수는 간단하지만 2가지 주의사항이 있습니다. 

 

① 찾을 값은 반드시 범위 첫 번째 열에 있어야 합니다. 

② VLOOKUP 함수 마지막 입력 값인 일치옵션은 대부분의 경우 0으로 사용합니다.  

(찾을 값 선택, 전체 범위 선택 후 F4, 열 번호 입력, 0 입력 후 엔터)

 

3. VLOOKUP 함수 다중조건

기본적으로 한 가지 조건의 값만 찾을 수 있지만, 다중조건을 찾을 수 있는 방법도 있습니다. 

가. 배열 계산 방법 

먼저, 서로 다른 조건의 범위 2개를 배열로 계산하는 방법을 알아야 합니다. 

 

(1) 전체 데이터에 나와있는 방문일과 구하고 싶은 값의 방문일이 동일한지 알아보기 위해 아래와 같이 값을 입력해 줍니다.  

 

(2) 아래와 같이 다른 셀들도 똑같이 값을 구해줍니다. 

 

(3) 이번엔 셀을 하나 더 추가하여 똑같은 방법으로 이름도 같은지 값을 구해보겠습니다. 

 

(4) 대부분의 프로그램에서는 논리값을 계산할 때 TRUE는 1로 FALSE는 0으로 계산합니다. 

 

(5) 두 개의 조건을 모두 만족하는 경우를 구해야 할 때 이 경우에만 곱셈으로 계산하면 두 값이 1로 표현된다. 

 

(6) 하나라도 만족하는 경우를 구해야 할 때는 덧셈으로 계산하면 0보다 큰 값으로 계산됩니다. 

 

 

 나. 다중조건 입력방법 (MATCH, INDEX함수)

이제 본격적으로 다중조건에 대한 값을 찾는 방법을 소개하겠습니다. 

(1) 방문일과 이름이 둘 다 같은 값을 구하기 위해 수식에 곱셈을 활용합니다.

  ※ 수식 앞에 -를 두 번 입력해 줍니다. 논리 값의 계산결과가 숫자(1 또는 0) 대신 TRUE/FALSE로 반환될 경우 오류가 발생할 수 있기 때문에 강제로 숫자로 변환해 주는 방법입니다.  

 

(2) 아래와 같이 값이 입력됩니다. 

 

(3) 찾으려는 구매물품 셀에 아래와 같이 MATCH와 INDEX함수를 활용하여 값을 구해줄 겁니다. 

▶ MATCH 함수 : 범위에서 찾는 값의 위치 순번을 반환합니다. 

 = MATCH(찾을 값, 찾을 범위, [일치옵션])

▶INDEX 함수 : 범위에서 순번에 위치한 값을 반환합니다. 

= INDEX(범위, 행번호, [열번호])

(4) 아래 그림과 같이 MATCH 함숫값을 입력해 줍니다. 방문일과 이름이 같다면 곱하기 1이 나오기 때문에 1로 나오는 값의 위치를 알려줍니다.  

 

(5) 왼쪽 셀에서 먼저 구해뒀던 위치와 똑같이 6번째라는 값이 나옵니다. 

 

(6) INDEX를 함수로 6번째 행에 있는 구매물품 값을 구해보도록 하겠습니다. 

MATCH함수로 이미 6번째 행을 구했기 때문에 앞에 INDEX함수를 추가하고 범위를 입력합니다. 

 

(7) 아래와 같이 구매물품 값이 반환됩니다. 

 

 

VLOOKUP 함수 기본 사용법과 다중조건에서 값을 구하기 위해 MACH와 INDEX를 함수를 활용하는 방법을 소개했습니다. 

처음엔 어렵게 느껴지더라도 원리를 알면 간단하므로 연습해서 숙지하셔서 업무에 꼭 활용하실 수 있기를 바랍니다. 

 

아래 링크에서 자세한 설명과 예제파일을 다운로드하실 수 있습니다. 

 

감사합니다 :)

 

VLOOKUP 다중 조건 공식, 5분 완벽 정리! | 진짜쓰는 실무엑셀 - 오빠두엑셀