안녕하세요 모닝수잔입니다 :)
업무를 처음 배울 때 사수가 VLOOKUP 함수를 쓰시는게 어찌나 멋있어 보이던지, 아직도 그 기억이 생생합니다. 하지만 그 뒤로도 몇 년의 시간이 흐르는 동안 엑셀을 공부해야겠다는 생각은 못했는데요, 막상 엑셀을 쓸 일이 많아지니 스스로 찾아서 배우게됩니다. 지금은 VLOOKUP 함수를 아무렇지 않게 사용하는 저를 보면서 스스로 신기해요. 그래서 오늘은 복잡할 것 같지만 알아두면 너무도 유용한 VLOOKUP 함수에 대해 알아보겠습니다.
간단한 예제 표를 만들어봤습니다. 좌측 표에는 상품명과 수량이 기재되어 있고, 우측 표에는 상품명과 가격이 기재되어 있습니다. 이 2개의 표를 좌측에 하나로 합치려고 합니다. 주황색 셀 부분에 가격을 넣어주면될텐데요, 아래 표만 보시면 G열을 그대로 복사해서 ▶ D열에 붙여넣으면 될 것 같죠?
그/렇/다/면,
이번엔 어떠신가요? 아래 표에서도 G열을 그대로 복사해서 ▶ D열에 붙여넣으면 될까요? 아닙니다. 이번에는 아래 화면 빨간칸처럼 제가 상품명을 섞었기 때문에 각 상품명에 맞는 가격을 찾아서 넣어줘야 합니다. 굉장히 번거롭겠죠? 이때 유용하게 사용할 수 있는 함수가 바로 VLOOKUP 함수입니다.
VLOOKUP 함수
=VLOOKUP(기준 값, 기준 범위, 찾는 값의 열 번호, 찾는 방법)
VLOOKUP 함수란, 첫번째 열을 기준으로 오른쪽에 있는 항목을 찾을 때 사용할 수 있는 함수를 말합니다.
VLOOKUP 함수를 사용할 때는
1) 셀(Cell)에서 "=VLOOKUP"이라고 기재하시거나,
2) 상단 메뉴에서 수식 - 찾기/참조 영역 - VLOOKUP을 선택하시면 됩니다.
함수 인수
VLOOKUP 함수를 오류 없이 정확하게 사용하는 방법은 어떤 인수를 넣어야하는지 정확하게 이해하는 것입니다. 앞서 VLOOKUP은 =VLOOKUP(기준 값, 기준 범위, 찾는 값의 열 번호, 찾는 방법)이라고 말씀드렸는데요, 즉 4개의 인수를 () 안에 넣어줘야 합니다. 하나씩 살펴보겠습니다.
먼저, 왼쪽 표의 B3 셀에 있는 "투버튼 장식 H라인 린넨 자켓"의 "가격"을 오른쪽 표의 G열에서 찾아서 D3 셀에 넣어보겠습니다. 저는 VLOOKUP 함수를 사용하는 2) 방법을 선택해서 D3셀에 커서를 두고 상단 메뉴의 수식 - 찾기/참조영역 - VLOOKUP을 선택하여 "함수 인수"라는 새로운 창을 띄웠습니다.
▶ 기준 값(Lookup_value) : 기준 값은 "투버튼 장식 H라인 린넨 자켓"이 됩니다. 아래 화면 오른쪽 빨간칸을 보시면 Lookup_value란에 B3 셀이 들어가 있고 "투버튼 장식 H라인 린넨 자켓"라고 표기되어 있다는 것을 확인하실 수 있습니다.
▶ 기준 범위(Table_array) : 우리에게 필요한건 "투버튼 장식 H라인 린넨 자켓"의 "가격"인데요, 이 가격은 어디에 기재되어 있나요? 바로 오른쪽 표 G열에 기재되어 있습니다. 그렇다면 기준 범위는 G열이 될까요? 아닙니다.
앞서 살펴본 VLOOKUP 함수의 정의를 다시 한번 살펴보면, "첫번째 열을 기준으로 오른쪽에 있는 항목을 찾을 때 사용할 수 있는 함수"입니다. 즉, 기준 범위는 1) "기준 값"과 동일한 값을 포함하는 범위여야 하고, 2) 그 값이 가장 왼쪽(첫번째 열)에 있는 범위여야하고, 3) "찾는 값"을 포함하는 범위여야 합니다. 1) & 2) & 3)을 모두 충족하려면 아래 노란색으로 표기된 F열과 G열을 모두 기준 범위로 선택해야 합니다. 1) "기준 값"과 동일한 "투버튼 장식 H라인 린넨 자켓"이 있는 F4셀을 포함하는 범위이면서, 2) 그 값이 가장 왼쪽(첫번째 열)인 F열에 있는 범위이고, 3) "찾는 값"인 "가격"이 있는 G열까지 포함하기 때문입니다.
이때, "기준 범위"는 꼭 동일한 시트(Sheet)나 파일(Worksheet)이 아니어도 상관없습니다. "함수 인수" 창에서 Table_array 부분에 커서를 뒀다가 다른 시트나 파일에서 마우스 드래그로 범위를 지정하면 해당 범위가 "기준 범위"로 지정됩니다.
※ "기준 범위(Table_array)"는 인수의 오류가 가장 많이 발생하는 부분입니다. "기준 값"과 "찾는 값"을 잘 구분하시면 이해에 도움이 되실텐데요, "기준 값"은 양쪽에 "동일하게 존재"하기 때문에 기준이 될 수 있는 값입니다. 예제 표에서 왼쪽 표와 오른쪽 표는 위치만 다를 뿐 동일한 "상품명"을 가지고 있기 때문에 "상품명"이 곧 "기준 값"이 됩니다. 이때 우리가 찾으려는 값은 "상품명"이라는 "기준 값"에 맞는 "가격"이므로, "찾는 값"은 "가격"이 됩니다.
▶ 찾는 값의 열 번호(Col_index_num) : 우리가 선택한 기준 범위는 F열과 G열, 2개의 열(Column)로 구성되어 있습니다. 그 중에서도 "찾는 값"은 2번째 G열에 있는 "가격"이므로 2를 입력해주시면 됩니다.
▶ 찾는 방법(Range_lookup) : 찾는 방법에는 2가지가 있습니다. 100% 정확한 값을 찾을 것인가? 어느 정도 근사치의 값을 찾을 것인가? 정확하게 일치하는 값을 찾는 경우에는 0 이나 False를, 비슷한 근사치의 값을 찾을 경우에는 1 이나 True를 입력해주시면 됩니다.
※ 현재 예제에서는 정확하게 일치하는 값을 찾는 것이 맞기 때문에 0 이나 False를 입력해주셔야 합니다. 반대로, 1~100점까지는 A등급 101~200점까지는 B등급 201~300점까지는 C등급처럼 "구간"이 있는 경우에는 비슷한 근사치 값을 찾는 1 이나 True를 입력해주시면 됩니다.
여기까지 4개의 인수를 모두 입력해주신 후에 "확인"을 클릭하시면 아래 화면처럼 D3셀에 정확한 값이 들어간 것을 확인하실 수 있습니다. 그대로 마우스를 끌어서 아래로 D7셀까지 내려주시면 모든 셀에 값이 채워집니다. 왼쪽 표 B열에 있는 "기준 값"을 기준으로 오른쪽 표 F열에 동일한 "기준 값"이 있는 경우, 오른쪽으로 2번째 값인 G열의 "가격"을 가져와서 D열에 넣어주는 방법이죠. 이것이 바로 VLOOKUP 함수의 기능입니다.
기본 개념만 잘 잡으시면 진짜 회사 업무에서 200% 활용해서 유용하게 사용하실 수 있는 함수가 바로 VLOOKUP 함수입니다. 저도 처음에는 너무 어렵다고만 생각했는데, 막상 기본 개념을 익히고 여러번 사용하다보니 이제는 오류 없이 익숙하게 사용할 수 있게 되었답니다. 그럼 오늘도 좋은 하루 되세요 :)
※ "기준 범위" 설정의 추가 TIP
VLOOKUP 함수에서 기준 범위(Table_array) F열과 G열을 F:G로 표기할 수도 있지만 "이름 정의" 기능을 통해 이름을 지정해주는 것도 편리할 수 있습니다. 방법도 매우 간단합니다. 오른쪽 표를 전체 선택한 뒤 "이름 상자"에 이름을 입력해주면 바로 "이름 정의"가 가능합니다. 저는 "오른쪽표"라고 정의해봤는데요, VLOOKUP 함수에서 =VLOOKUP(B3, F:G, 2, 0) 대신에 ▶ =VLOOKUP(B3, 오른쪽표, 2, 0)라고 표현할 수 있답니다.
단축키 F3을 눌러보시면 내가 설정한 "이름 상자" 리스트를 찾으실 수도 있고 원하는 이름을 클릭하면 해당 이름 정의가 되어 있는 영역이 블록으로 자동 지정됩니다. 혹시나 이름 정의를 삭제하고 싶으시다면 상단 메뉴에서 수식 - 이름관리자에서 삭제를 클릭해주세요 :)
