엑셀 VLOOKUP 함수 사용법 을 찾고 계신가요? 이 글에서 VLOOKUP 함수 기본 사용법을 알아보고 조건에 맞는 중복값을 모두 한번에 찾아오는 방법까지 알아보겠습니다. 지금 바로 아래에서 VLOOKUP 함수 사용법과 VLOOKUP 조건에 맞는 여러개 값 반환하는 방법을 확인해보세요.
위엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
엑셀 VLOOKUP 함수 사용법
| 엑셀 VLOOKUP 함수 사용법 VLOOKUP 함수는 첫 번째로 찾을 범위에서 찾을 값을 찾습니다. 그 후 오른쪽으로 쭉 가면서 지정한 열 번호에 해당되는 값을 반환합니다. =VLOOKUP(찾을값, 찾을 범위, 열 번호, 일치여부) |
1.엑셀 VLOOKUP 함수 기본 사용법
우선 엑셀 VLOOKUP 함수 기본 사용법을 알아보겠습니다. 아래 사진에서 ‘1과’ 에 해당되는 직원명을 찾는다고 해볼게요. 그러기 위해 아래처럼 함수식을 입력합니다.
=VLOOKUP(J3,B2:H14,3,0) → 김길동
=VLOOKUP(찾을값, 찾을 범위, 열 번호, 일치여부)
찾을 값은 ‘J3(1과)’, 찾을 범위는 J3 셀이 포함된 B2:H14 입니다. 열 번호는 반환할 값(직원명)이 포함된 열인 3열이므로 3을 입력합니다. 그리고 우리는 정확히 일치하는 값을 찾을 것이므로 0을 입력합니다. 그럼 ‘김길동’ 이라는 결과값이 나옵니다.
2.목록 선택으로 값 한번에 찾아오기
이번에는 하위목록을 선택할 수 있도록 만든 후 값을 한번에 찾아오는 방법입니다. K6 셀에 아래처럼 함수식을 입력합니다.
=COLUMNS($B$2:C2) → 2
=COLUMNS(범위)
COLUMNS 함수는 열번호를 반환하는 함수입니다. ‘직원아이디’는 B2:C2 범위에서 2번째 열에 위치하고 있죠? 그래서 2라는 값이 반환됩니다. K6셀에서 오른쪽으로 자동채우기를 할 때 B2셀은 움직이면 안됩니다. 따라서 B2 에만 F4 키를 눌러서 절대참조를 해줍니다.
오른쪽으로 자동채우기를 하면 각 열 번호가 2,3,4,5… 이런식으로 반환됩니다.
위에서 열 번호를 COLUMNS 함수를 통해 자동으로 입력되게 해 놓았습니다. 이제 COLUMNS 함수식 앞에 VLOOKUP 함수를 입력해서 아래와 같이 함수식을 입력해 줍니다.
=VLOOKUP($J$6,$B$2:$H$14,COLUMNS($B$2:C2),0)
=VLOOKUP(찾을값, 찾을 범위, 열 번호, 일치여부)
찾을 값은 ‘J6(1과)’ 이고, 찾을 범위는 ‘B2:H14’ 입니다. 오른쪽으로 자동채우기를 할 때 셀, 범위 위치가 바뀌면 안되니까 F4 를 눌러 절대참조를 합니다.
열번호는 CLOUMNS 함수를 이용해서 미리 입력했으므로 그대로 둡니다. 네 번째 인수로 0을 입력해서 정확히 일치하는 값을 찾도록 합니다.
그럼 위 사진처럼 해당되는 값이 모두 바로 나오는 것을 확인할 수 있습니다.
이번엔 ‘1과’, ‘2과’, ‘3과’… 등등 목록을 만들어서 목록 중 원하는 것을 선택해서 값이 바로 찾아와지게 하는 방법입니다.
아래 사진처럼 J6셀을 클릭 한 후 데이터 → 데이터 유효성 으로 들어갑니다.
아래 사진처럼 ‘허용’ 을 ‘목록’ 으로 선택합니다. 윈도우의 경우 ‘제한대상’ 을 ‘목록’ 으로 선택 해줍니다. 그 후 ‘원본’ 아래 칸에 커서를 두고 B3:B14 범위를 선택합니다. 그 후 확인 버튼을 누릅니다.
그럼 아래 사진처럼 드롭다운 목록이 만들어 집니다. 원하는 목록을 선택하면 해당되는 결과값이 한 번에 나옵니다.
3.VLOOKUP 함수 중복값 한번에 찾아오기
이번에는 중복값이 있는 경우 중복값을 VLOOKUP 함수로 한번에 다 찾아오는 방법입니다.
아래 사진을 보면 ‘담당 과’ 부분에 1과, 2과… 등등 중복되는 값이 많습니다. 그래서 각 값을 고유한 값으로 바꿔줘야 하는데요. COUNIF 함수를 이용해서 1과, 2과 뒤에 1,2… 처럼 숫자를 붙여서 고유한 값으로 만들 수 있습니다. B3 왼쪽 셀에 커서를 두고 아래처럼 함수식을 입력합니다.
=B3&COUNTIF($B$2:B3,B3) → 1과1
=COUNTIF(범위, 조건)
아래로 자동채우기를 할 때 B2 셀 위치는 바뀌면 안되므로 B2 셀만 절대참조를 해줍니다. 그리고 B3 셀(1과, 2과…) 옆에 숫자 1,2.. 를 붙일것이므로 & 기호를 입력해서 연결을 해줍니다.
그 후 아래 셀에도 자동채우기를 해줍니다. 그럼 중복되는 값 없이 모두 고유한 값으로 바뀌게 됩니다.
이제 VLOOKUP 함수를 이용해서 중복값을 모두 찾아와보겠습니다. 아래와 같이 함수식을 입력합니다.
=VLOOKUP($J10&K$8,$A$2:$H$14,4,0) → 김길동
=VLOOKUP($J10&K$8,$A$2:$H$14,4,0) → J10에서 J열은 움직이면 안됩니다. 10~15행만 바뀌어야 합니다. 따라서 J열만 절대참조를 합니다. F4 키를 3번 누르면 J열만 절대참조가 됩니다.
=VLOOKUP($J10&K$8,$A$2:$H$14,4,0) → K8~P8은 열번호를 입력해 둔 것입니다. 자동채우기를 했을 때 열번호는 자동으로 바뀌어야 합니다. 따라서 K열은 움직일 수 있도록 그대로 두고요. 8행은 F4키를 눌러 고정시킵니다.
=VLOOKUP($J10&K$8,$A$2:$H$14,4,0) → 찾을 범위로 A2:H14 를 선택합니다. 이 때 COUNTIF 함수를 이용해서 구했던 고유값도 모두 범위에 포함해 줍니다. F4 키를 눌러 범위를 고정합니다.
=VLOOKUP($J10&K$8,$A$2:$H$14,4,0) → 반환할 값인 ‘직원명’ 은 A2:H14 범위 중 4번째 열에 위치하고 있습니다. 따라서 4를 입력해줍니다. 정확한 값을 찾기 위해 네 번째 인수에 0을 입력합니다.
엔터키를 누른 후 ‘김길동’ 셀에 커서를 두고 Ctrl + C 를 눌러 복사합니다. 그 후 아래 사진처럼 전체 범위를 지정한 후 Ctrl + V 를 눌러 붙여 넣기 합니다. 그럼 아래 사진과 같은 결과가 나옵니다. 근데 오류값이 있어 보기가 안 좋습니다. 이 경우 오류값은 IFERROR 함수를 이용해서 보이지 않게 할 수 있습니다.
오류값을 제거하기 위해 K10에 입력 해 놨던 VLOOKUP 함수 앞에 커서를 두고 IFERROR 함수를 추가로 입력해 줍니다.
=IFERROR(VLOOKUP($J10&K$8,$A$2:$H$14,4,0),” “)
=IFERROR(오류를 검사할 인수, 해당 값이 오류일 경우 반환할 값)
오류값이 뜰 경우 공백으로 표시하기 위해서 ‘큰 따옴표, 스페이스바, 큰 따옴표’ 를 IFERROR 함수 두번째 인수에 입력했습니다.
이후 K10 셀을 Ctrl + C 로 복사한 후, 위 사진처럼 전체 범위를 선택해서 Ctrl + V 로 붙여 넣기 합니다. 그럼 위 사진처럼 오류값이 나온 부분에 아무런 표시도 나오지 않게 됩니다.
| ▶︎아래는 같이 보면 좋은 글입니다. 엑셀 VLOOKUP 다른 시트 값 가져 오기 방법 VLOOKUP 함수 오류 6가지 해결법 엑셀 XLOOKUP 함수 사용법 5가지 VLOOKUP SUMIF 같이 사용해 조건에 맞는 값과 합 구하기 |
이상 엑셀 VLOOKUP 함수 사용법 에 관한 글이었습니다.