들어가며
지정한 범위에서 원하는 값을 찾고자 할 때 사용할 수 있는 함수가 찾기/참조 함수입니다. "실무에서 가장 많이 사용하는 함수가 무엇"인지 물어보면 빠지지 않고 등장하는 VLOOKUP 함수가 이 범주에 속합니다. 지금까지 배운 함수보다 사용하는 방법이 살짝 복잡한 반면, 배워둘 가치는 충분합니다. 3개의 함수만 완전히 내 것으로 만들어 놓으면 실무에서 발생하는 많은 문제를 해결할 수 있습니다. 준비 되셨나요? 출발합니다.
지정한 조건에 맞는 데이터 찾기
무언가를 찾고자 할 때 가장 먼저 떠오르는 함수가 VLOOKUP입니다. VLOOKUP은 V(Vertical) + LOOKUP(검색, 색인)으로 이루어져 있습니다. 세로 방향(열)으로 값을 찾아주는 함수죠. 예를 들어, 왼쪽 표에서 '단가'는 오른쪽의 '단가 테이블'에 있는 '단가' 정보를 가지고 옵니다.
어떤 품목의 단가가 변동될 경우, '단가 테이블'만 수정하면 '날짜별/품목별 실적' 테이블은 자동으로 업데이트되어야 합니다. 이런 경우에 사용할 수 있는 함수가 VLOOKUP입니다.
VLOOKUP 함수 기본
VLOOKUP 함수는 다른 함수에 비해 조금 복잡해보입니다. 몇 차례 반복하다 보면 이해하게 됩니다. 믿고 따라오세요.
=VLOOKUP(검색할_값, 표_범위, 열_인덱스, 번호, [범위 검색]) ' [ ]는 생략 가능
' lookup_value: 검색할 값—테이블의 첫 열에서 검색할 조건이 되는 값(검색할_값)
' table_array: 표 범위—조건에 대항하는 값을 검색할 테이블(표_범위)
' col_index_num: 열_인덱스_번호—table_array 내의 열 번호
' [range_lookup]: 범위 검색—정확하게 일치하는 값을 찾으려면 0(또는 False), 비슷한 값을 찾으려면 1(또는 생략)
수식을 작성하기에 앞서, [날짜별/품목별 실적] 테이블의 '단가' 셀(D4)에 원하는 값을 가지고 오려면 어떻게 접근하면 될 지 말로 설명해 보세요. 길동이에게 말로 전달한다면 이런 식이 될 겁니다.
"(D4 셀에는) C4 셀의 품목을 [단가 테이블]에서 일치하는 값을 찾은 다음, 2번째 열의 단가 정보를 입력하세요"
이 정도로 설명하면 길동이가 알아들을 수 있지 않을까요? 이것을 엑셀의 함수 엔진이 알아듣게 변환하면 됩니다.
(1) 결과를 표시할 셀(여기서는 D4 셀)을 선택합니다.
(2) =vlookup()을 입력하고 수식 입력줄 왼쪽에 있는 [함수 삽입] 아이콘을 클릭합니다.
(3) 위의 네모 상자 안의 내용을 토대로 VLOOKUP 함수 인수를 지정합니다.
- 검색할_값: C4
- 표_범위: $H$4:$I$13
- 열_인덱스_번호: 2
- 범위_검색: 0
'표_범위'인수의 경우, H4:I13을 범위로 지정하고 F4를 눌러서 절대 주소 형태로 바꿔준 점에 유의하세요. 완성된 수식을 다른 셀로 복사했을 때 '표 범위'는 변하지 않아야 하므로 절대 주소로 바꿔준 겁니다.
(4) [확인]을 누르면 결과가 구해집니다. D4 셀 우측 하단의 까만색 점(채우기 핸들)을 더블 클릭하여 수식을 나머지 셀에도 채워 넣기 합니다.'금액'은 '단가 * 수량'으로 구할 수 있습니다(F4 셀: =D4*E4).
=VLOOKUP(C4,$H$4:$I$13,2,0)
참조할 테이블이 가로 방향으로 되어 있다면?
VLOOKUP은 V + LOOKUP이라고 했습니다. 참조할 테이블(표_범위)이 세로 방향으로 정리되어 있는 경우에 유효했습니다. 만약 참조할 테이블이 다음과 같이 가로 방향으로 되어 있다면 어떻게 할까요?
이럴 때 사용할 수 있는 함수가 HLOOKUP이며, H는 horizontal을 뜻합니다. VLOOKUP과 HLOOKUP은 참조 테이블이 정리된 방향이 다를 뿐, 함수를 사용하는 방법은 동일합니다. HLOOKUP 함수를 사용하면 다음과 같이 되며, 수식의 결과는 VLOOKUP 함수의 그것과 동일합니다.
=HLOOKUP(C4,$L$15:$U$16,2,0)
오류(#N/A)가 발생할 경우 대처 방법
이달의 신제품으로 '슈크림 라떼'가 출시되었습니다. [날짜별/품목별 실적] 테이블에는 실적이 있지만 [단가 테이블]에는 신제품 정보가 없습니다. 이런 경우 VLOOKUP 함수를 사용하면 '#N/A'오류가 나타납니다. 'Not Available'의 줄임말로, 유효하지 않은 값이 들어왔다는 뜻입니다.
데이터 중간에 이런 오류가 나타나면 합계나 평균 같은 다른 수식에도 영향을 미치므로 반드시 처리하는 것이 좋습니다. IFERROR 함수를 VLOOKUP과 중첩하면 해결할 수 있습니다.
(1) D4 셀을 선택합니다.
(2) =iferror()를 입력하고 수식 입력줄 왼쪽에 있는 [함수 삽입] 아이콘을 클릭합니다.
(3) Value 인수 입력란에 vlookup()이라고 입력하고 수식 입력줄에서 vlookup 함수를 클릭합니다.
(4) VLOOKUP 함수 인수 대화상자가 나타납니다. 앞에서 작성했던 것과 같이 VLOOKUP 함수의 각 인수를 지정합니다.
(5) VLOOKUP 함수 인수 지정이 끝나면 수식 입력줄에서 IFERROR 함수를 선택합니다. IFERROR 함수 인수 대화상자로 바뀝니다. Value_if_error 인수란을 클릭하고 표시하기를 원하는 값을 입력합니다. 여기서는 0을 입력해 보죠.
(6) [확인] 버튼을 클릭하면 결과가 표시됩니다. 다른 셀에 대해서도 수식을 채워 넣습니다.
=IFERROR(VLOOKUP(C4,$H$4:$I$13,2,0),0)
단가 테이블에 정보가 없는 품목인 경우에도 오류가 표시되지 않고 지정한 값(0)이 표시되므로 다른 수식에 영향을 미치지 않습니다. 사용자는 나중에 단가가 0인 품목만 확인해서 단가 테이블에 업데이트를 하면 됩니다. 물론 수식의 범위에 수정이 있다면 수식에도 반영해야 합니다.
[응용] 비슷하게 일치하는 값 찾기
지금까지 살펴본 내용은 "VLOOKUP 함수를 이용하여 정확하게 일치하는 값 찾기"였습니다. 단가 테이블에서는 비슷하게 일치하는 값을 찾아봐야 아무런 의미가 없습니다. 하지만 다음과 같은 경우에는 이야기가 조금 달라집니다.
'평균'을 기준으로 '등급'을 매기는데, 90 이상이면 S, 80 이상이면 A, 70 이상이면 B, 이런 식으로 특정한 점수 구간에 따라 등급을 책정하고자 합니다. 이런 문제도 VLOOKUP 함수를 이용하여 해결할 수 있습니다.
(1) VLOOKUP 함수로 '비슷하게 일치하는 값'을 찾기 위해서는 수식 작성을 하기 전에 한 가지 해야 할 일이 있습니다. 참조 테이블을 낮은 값부터 높은 값으로, 즉 오름차순으로 테이블을 정리해 두어야 합니다.
(2) 결과를 표시할 K4 셀을 선택합니다.
(3) =vlookup()을 입력하고 수식 입력줄 왼쪽에 있는 [함수 삽입] 아이콘을 클릭합니다.
(4) '표_범위'를 (1)에서 작성한 테이블로 지정하고, '범위_검색'을 생략(또는 1)하는 점이 앞에서와 다릅니다.
- 검색할_값: C4
- 표_범위: $P$4:$Q$8
- 열_인덱스_번호: 2
- 범위_검색: 1 또는 생략
(5) [확인] 버튼을 클릭하면 결과가 표시됩니다. 다른 셀에 대해서도 수식을 채워 넣기하여 완성합니다.
=VLOOKUP(J4,$P$4:$Q$8,2)
찾기/참조 세계의 팔방미인—INDEX + MATCH
VLOOKUP 함수가 편리하고 좋은데 문제가 있습니다. VLOOKUP은 첫 번째 열에서 일치하는 값을 찾아서 그 오른쪽에 있는 열부터 값을 찾습니다. 따라서 기준 열(첫 번째 열) 왼쪽에 있는 값은 찾지 못합니다. 무슨 소리냐고요? 그럴 줄 알고 준비했습니다.
VLOOKUP 함수의 아킬레스건(?)
K4 셀에는 이런 수식이 들어 있습니다. 아무리 눈에 힘을 주고 봐도 앞에서 작성했던 수식과 별 차이가 없는데 오류가 표시됩니다. 실무에서 이런 경우와 맞닥뜨리면 참으로 난감합니다. 전문 용어(?)로 미치고 팔짝 뛸 지경입니다.ㅎㅎ
=VLOOKUP(J4,$M$4:$N$8,2)
문제는 수식에 있지 않습니다. VLOOKUP 함수의 '참조 테이블(표_범위)'에 있습니다. 이전의 경우와 비교해 보면, 앞에서는 '평균' 다음에 '등급'이 있었는데, 이번에는 반대로 되어 있기 때문입니다. 다시 한 번 강조하지만, VLOOKUP 함수는 기준이 되는 열(일치하는 값이 있는 열)의 왼쪽에 있는 값은 찾지 못합니다.
그러면 어떻게 해야 하느냐... (1) 테이블에서 열의 위치를 바꾸거나(평균 | 등급 순서) (2) 다른 함수를 사용해야 합니다. (1)은 논외로 하고 여기서는 (2), 그 중에서도 INDEX와 MATCH 함수를 조합하여 해결하는 방법에 대해 소개합니다.
INDEX, MATCH 함수 기본
INDEX와 MATCH 함수는 매우 매우 중요한 엑셀의 기본 함수입니다. 확실하게 알아둘 필요가 있습니다.
INDEX는 참조 영역에서 행 번호와 열 번호를 지정하여 원하는 값을 가져오는 함수로, 사용 형식은 다음과 같습니다.
=INDEX(array, row_num, column_num)
' array: 셀 범위
' row_num: 찾을 행
' column_num: 찾을 열. 생략 가능
[단가 테이블]에서 '품목' 중에서 3번째 행에 있는 값을 가지고 오려면 '=INDEX(J4:J13,3)'라고 하면 됩니다.
MATCH 함수는 지정한 값이 참조 영역에서 몇 번째에 있는지 위치를 구해줍니다.
= MATCH(lookup_value, lookup_array, match_type)
' lookup_value: 찾을 값
' lookup_array:셀 범위
' match_type:일치 타입. 생략 가능. -1, 0, 1중 선택. 찾을 값고 같은 값을 찾으려면 0
[단가 테이블]에서 '품목' 중에서 3번째 행에 있는 값을 가지고 오려면 '=MATCH("유기농 말차 라떼",J4:J13,0)'라는 수식을 사용합니다.
기본적인 내용은 이 정도로 정리해 둡니다. 함수는 조합해서 쓸수록 강력해집니다. INDEX와 MATCH 함수도 그러합니다.
INDEX와 MATCH 함수 조합
INDEX와 MATCH 함수의 기본 사용법에 대해 알았으므로 두 개의 함수를 조합하여 VLOOKUP 함수의 한계를 극복해 보겠습니다. 예제 데이터를 보면 왼쪽 테이블에 '유형'이 추가되었습니다. 이것은 단가 테이블의 정보를 가져오긴 합니다만, 기준이 되는 열(품목)의 왼쪽에 있는 정보이므로 VLOOKUP 함수로는 해결할 수 없습니다.
(1) 결과를 표시할 셀(C4 셀)을 선택합니다.
(2) =index()를 입력하고 수식 입력줄 왼쪽에 있는 [함수 삽입] 아이콘을 클릭합니다.
(3) 인수 선택 대화상자에서 'array,row_num,column_num'을 선택하고 [확인]을 누릅니다.
(4) Array 인수에는 [단가 테이블]에서 '유형' 정보가 들어 있는 J4:J13 영역을 지정하고 F4 키를 이용하여 절대 주소 형태로 바꿔줍니다.
(5) 함수 인수 대화상자에서 Row_num 인수 입력란을 선택합니다. MATCH 함수를 중첩하기 위해 match()를 입력하고 수식 입력줄에서 MATCH 함수를 클릭합니다.
(6) MATCH 함수 인수 대화상자가 나타납니다. 앞에서 살펴본 것처럼 MATCH 함수의 인수를 지정합니다. '검색할_범위'를 [단가 테이블]의 J열 해당 범위로 지정하고 절대 주소로 지정한 것과, '일치_유형'을 0(정확하게 일치하는 값 찾기)으로 지정한 점에 유의하시면 되겠습니다.
- 검색할_값: D4
- 검색할_범위: $J$4:$J$13
- 일치_유형:0
(7) 수식 입력줄에서 INDEX 함수를 선택하면 INDEX 함수 대화상자로 바뀝니다. [확인] 버튼을 클릭하면 C4 셀에 유형('커피')이 표시됩니다. 다른 셀에 대해서도 수식을 채워 넣기하여 완성합니다. 최종 완성 수식은 아래와 같습니다.
C4: =INDEX($I$4:$I$13,MATCH(D4,$J$4:$J$13,0))
함수 인수 대화상자를 이용하여 수식을 작성했지만 조금만 숙달되면 셀에 직접 입력하는 것이 더욱 빠르고 편리할 수 있습니다.
나가며
엑셀에서 가장 많이 사용되는 함수 중 하나(VLOOKUP), 매우 중요하고 활용도가 높은 기본 함수 2개(INDEX, MATCH)를 알게 되신 것, 축하합니다.
'Excel' 카테고리의 다른 글
Copilot Pro—Excel용 코파일럿 활용 방법 4가지 (59) | 2024.03.20 |
---|---|
Excel 파일 크기를 줄이는 6가지 방법 (5) | 2024.03.20 |
Excel에서 공백을 제거하는 3가지 방법 (7) | 2024.03.18 |
Excel과 MS Forms 자동 동기화 하는 방법 (0) | 2024.03.14 |
Excel에서 저장을 취소하는 방법 (6) | 2024.03.13 |