VLOOKUP / MATCH / INDEX 검색 값 찾는 방법

이번 포스팅에서는 VLOOKUP / MATCH / INDEX 검색 값 찾는 방법 에 대해서 설명하겠습니다.

이 함수의 조합은 수많은 데이터에서 원하는 값을 정확하게 찾고, 데이터가 추가되면 추출값이 자동으로 업데이트할 수 있어서 한 번만 설정해 두면 편하게 원하는 값을 찾을 수 있습니다.

제 개인적으로는 엑셀을 활용하여 데이터를 관리할 때 가장 필요한 함수라고 생각합니다. 이럴 때 유용하게 사용할 수 있는 조합이 VLOOKUP/MATCH/INDEX 함수입니다.

VLOOKUP / MATCH / INDEX 검색 값 찾는 방법

일반적으로 데이터를 관리할 때는 값을 불러오는 시트와 값(데이터)를 저장하는 시트를 따로 만들어 사용합니다.

아래 예시는 다른 시트에서 데이터를 가지고 오는 형태로 설명드립니다.

 

이름 입력 시 성과급을 자동으로 찾아오는 함수 조합 만들기

1. 우선 성과급은 평가에 따라 결정되기에 데이터 시트에서 '지코'의 평가 결과를 가지고 와야 합니다.

C2셀에 아래의 수식을 입력합니다.

=VLOOKUP(B2,데이터!B2:K21, 10, 0)

• B2 : 찾으려는 값입니다.
• 데이터!B2:K21 : '데이터!'는 시트이름이 데이터인 다른 시트를 참조한다는 뜻입니다.
  '=VLOOKUP(B2,'까지 입력하고 데이터 시트를 클릭하면 자동으로 입력이 됩니다. 물론 직접 작성해도 괜찮습니다. 따라서 데이터라는 시트의 B2:K21까지의 값을 참조한다는 의미입니다.
• 10 : 데이터!B2:K21에서 10번째에 해당하는 B2값을 찾습니다.
• 0 : 정확하게 일치하는 값을 찾습니다.
• 지코의 평가 'S'가 추출되었습니다.

2. 평가가 S가 입력된 행번호를 찾기 위해 MATCH 함수를 중첩합니다.

=MATCH(VLOOKUP(B2, 데이터! B2:K21, 10, 0), B6:B9,0)

• VLOOKUP(B2, 데이터! B2:K21, 10, 0) : 위에서 추출한 'S'를 찾습니다.
• B6:B9 : 'S'의 행번호를 찾습니다.
• 0 : 'S'와 정확히 일치하는 값을 찾습니다.
• 'S'가 포함된 행 번호 1이 추출되었습니다.

 

3. 행 번호를 바탕으로 INDEX 함수를 중첩하여 최종적으로 성과급을 추출해 보겠습니다.

=INDEX(C6:C9, MATCH(VLOOKUP(B2, 데이터! B2:K21, 10, 0), B6:B9,0))

• C6:C9 : 해당 범위에서 MATCH 함수에서 추출한 '1', 즉 첫 번째 행의 값을 추출합니다.
• MATCH(VLOOKUP(B2, 데이터! B2:K21, 10, 0), B6:B9,0) : 'S'가 포함된 행 번호 1입니다.
• 최종적으로 지코의 성과급 20,000,000원이 추출되었습니다.

VLOOKUP / MATCH / INDEX 검색 값 찾는 방법 _참고

처음 이렇게 중첩하여 사용할 때는 어렵습니다.

예전에 처음 컴퓨터 활용능력 1급 시험을 공부했을 때 2개 이상의 함수를 중첩하여 사용하는데, 과연 이런 내용을 사용할까 의아했던 적도 많았습니다.

회사에서 업무를 해보니 실제 그러한 함수를 많이 사용하고 있다는 것을 확인할 수 있었습니다. 지금은 어렵지만 하나씩만 익히셔도 회사에서 어느 누구보다 엑셀을 잘하는 직원이 될 수 있습니다.

내년부터는 컴퓨터 활용능력 1급 관련한 기출문제도 하나씩 다뤄보겠습니다.

 

이상 VLOOKUP/MATCH/INDEX 검색 값 찾는 방법 에 대해 설명드렸습니다.

감사합니다.