Microsoft Excel에서 VLOOKUP 기능을 사용하는 방법 [+ 비디오 자습서]

Microsoft Excel에서 막대한 양의 데이터를 조정하는 것은 시간이 많이 걸리는 골치 아픈 일입니다. 다행히도 그럴 필요는 없습니다. VLOOKUP 기능을 사용하면 이 작업을 자동화하고 많은 시간을 절약할 수 있습니다.

VLOOKUP의 기능은 정확히 무엇인가요? 간단한 설명은 다음과 같습니다. VLOOKUP 함수는 데이터에서 특정 값을 검색하고 해당 값을 식별한 후 해당 값과 관련된 다른 정보를 찾아서 표시할 수 있습니다.

Microsoft Excel의 VLOOKUP 기능은 생각보다 사용하기 쉽습니다. 게다가 이는 믿을 수 없을 정도로 강력하며 확실히 분석 무기고에 갖고 싶은 것입니다.

건너뛰기:

VLOOKUP은 어떻게 작동하나요?

VLOOKUP은 "수직 조회"를 의미합니다. Excel에서 이는 스프레드시트의 열과 해당 열 내의 고유 식별자를 검색의 기초로 사용하여 스프레드시트에서 수직으로 데이터를 조회하는 행위를 의미합니다. 데이터를 조회할 때 해당 데이터가 있는 위치마다 세로로 나열되어야 합니다.

VLOOKUP Excel 수식

Microsoft는 VLOOKUP 수식 또는 함수를 다음과 같이 설명합니다.

=VLOOKUP(조회 값, 조회 값이 포함된 범위, 반환 값이 포함된 범위의 열 번호, 대략 일치(TRUE) 또는 정확히 일치(FALSE)).

조회하려는 값이 찾으려는 반환 값의 왼쪽에 있도록 데이터를 구성하는 데 도움이 됩니다.

수식은 항상 오른쪽으로 검색합니다.

Excel에서 VLOOKUP을 수행할 때 기본적으로 현재 스프레드시트의 이전 데이터와 연결된 다른 스프레드시트에서 새 데이터를 찾고 있습니다. VLOOKUP이 이 검색을 실행하면 항상 현재 데이터 오른쪽 에서 새 데이터를 찾습니다.

예를 들어, 한 스프레드시트에는 이름의 세로 목록이 있고 다른 스프레드시트에는 해당 이름 과 이메일 주소가 정리되지 않은 목록이 있는 경우 VLOOKUP을 사용하여 첫 번째 스프레드시트에 있는 순서대로 해당 이메일 주소를 검색할 수 있습니다. 해당 이메일 주소는 두 번째 스프레드시트의 이름 오른쪽 열에 나열되어야 합니다. 그렇지 않으면 Excel에서 해당 주소를 찾을 수 없습니다. (그림을 이동 … )

데이터를 검색하려면 수식에 고유 식별자가 필요합니다.

VLOOKUP 작동 방식의 비밀은 무엇입니까? 고유 식별자.

고유 식별자는 두 데이터 소스가 공유하는 정보의 일부이며 이름에서 알 수 있듯이 고유합니다(즉, 식별자는 데이터베이스의 하나의 레코드에만 연결됩니다). 고유 식별자에는 제품 코드, SKU(재고 관리 단위) 및 고객 연락처가 포함됩니다.

자, 설명은 충분합니다. VLOOKUP이 실제로 작동하는 또 다른 예를 살펴보겠습니다.

VLOOKUP 엑셀 예

아래 비디오에서는 VLOOKUP 함수를 사용하여 전자 메일 주소(두 번째 데이터 원본의)를 별도 시트의 해당 데이터와 일치시키는 실제 예를 보여줍니다.

작성자 참고 사항: Excel에는 다양한 버전이 있으므로 위 비디오에 표시되는 내용이 사용 중인 버전에 표시되는 내용과 항상 정확하게 일치하지 않을 수도 있습니다. 그렇기 때문에 아래 서면 지침을 따르도록 권장합니다.

Excel에서 VLOOKUP을 사용하는 방법

1. 새 데이터로 채우려는 셀 열을 식별합니다.
2. '함수'(Fx) > VLOOKUP을 선택하고 이 수식을 강조 표시된 셀에 삽입하세요.
3. 새 데이터를 검색하려는 조회 값을 입력합니다.
4. 원하는 데이터가 있는 스프레드시트의 테이블 배열을 입력하세요.
5. Excel에서 반환할 데이터의 열 번호를 입력합니다.
6. 조회 값과 정확히 일치하거나 대략적으로 일치하는 항목을 찾으려면 범위 조회를 입력하세요.
7. '완료'(또는 'Enter')를 클릭하고 새 열을 입력하세요.

참고로 VLOOKUP 함수의 구문은 다음과 같습니다.

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

아래 단계에서는 각 고객의 MRR을 찾기 위해 고객 이름을 고유 식별자로 사용하여 이러한 각 구성 요소에 올바른 값을 할당합니다.

1. 새 데이터로 채우려는 셀 열을 식별합니다.

다른 시트에서 데이터를 검색하여 이 시트에 저장하려고 한다는 점을 기억하세요. 이를 염두에 두고 자세한 정보를 원하는 셀 옆 열에 월별 반복 수익에 대한 "MRR"과 같은 적절한 제목을 맨 위 셀에 표시합니다. 이 새 열은 가져오는 데이터가 이동하는 곳입니다.

2. '함수'(Fx) > VLOOKUP을 선택하고 이 수식을 강조 표시된 셀에 삽입하세요.

스프레드시트 위의 텍스트 표시줄 왼쪽에 스크립트처럼 보이는 작은 함수 아이콘인 Fx가 표시됩니다. 열 제목 아래 첫 번째 빈 셀을 클릭한 후 이 기능 아이콘을 클릭하세요. 사용 중인 Excel 버전에 따라 수식 작성기 또는 함수 삽입이라는 제목의 상자가 화면 오른쪽에 나타납니다.

Formula Builder에 포함된 옵션 목록에서 "VLOOKUP"을 검색하고 선택합니다. 그런 다음 확인 또는 함수 삽입을 선택하여 VLOOKUP 작성을 시작합니다. 현재 스프레드시트에서 강조표시한 셀은 이제 다음과 같습니다: " =VLOOKUP() "

위의 굵은 텍스트를 원하는 셀에 정확하게 입력하여 이 수식을 호출에 수동으로 입력할 수도 있습니다.

첫 번째 셀에 =VLOOKUP 텍스트를 입력하고 이제 네 가지 다른 기준으로 수식을 채울 차례입니다. 이러한 기준은 Excel에서 원하는 데이터의 위치와 찾을 대상을 정확하게 좁히는 데 도움이 됩니다.

3. 새 데이터를 검색하려는 조회 값을 입력합니다.

첫 번째 기준은 조회 값입니다. 이는 Excel이 찾아서 반환할 데이터와 연결된 스프레드시트의 값입니다. 이를 입력하려면 일치 항목을 찾으려는 값이 포함된 셀을 클릭하세요. 위에 표시된 예에서는 A2 셀에 있습니다. 이 셀은 A2에 나열된 고객 이름의 MRR을 나타내므로 새 데이터를 D2로 마이그레이션하기 시작합니다.

검색 값은 텍스트, 숫자, 웹사이트 링크 등 무엇이든 될 수 있다는 점을 명심하세요. 찾고 있는 값이 참조 스프레드시트의 값(다음 단계에서 이에 대해 설명)과 일치하는 한 이 함수는 원하는 데이터를 반환합니다.

4. 원하는 데이터가 있는 스프레드시트의 테이블 배열을 입력하세요.

'테이블 배열' 필드 옆에 위 스크린샷에 표시된 형식을 사용하여 검색하려는 셀 범위와 이러한 셀이 있는 시트를 입력합니다. 위 항목은 우리가 찾고 있는 데이터가 "페이지"라는 스프레드시트에 있고 B열과 K열 사이 어디에서나 찾을 수 있음을 의미합니다.

데이터가 있는 시트는 현재 Excel 파일 내에 있어야 합니다. 이는 데이터가 현재 스프레드시트의 다른 셀 표에 있을 수도 있고 아래와 같이 통합 문서 하단에 연결된 다른 스프레드시트에 있을 수도 있음을 의미합니다.

예를 들어 데이터가 셀 C7과 L18 사이의 "Sheet2"에 있는 경우 테이블 배열 항목은 "Sheet2!C7:L18"이 됩니다.

5. Excel에서 반환할 데이터의 열 번호를 입력합니다.

테이블 배열 필드 아래에 검색하려는 테이블 배열의 "열 인덱스 번호"를 입력합니다. 예를 들어 B~K열('테이블 배열' 필드에 입력할 때 'B:K'로 표시)에 초점을 맞추고 있지만 원하는 특정 값이 K열에 있는 경우 열에 '10'을 입력합니다. "열 인덱스 번호" 필드(K열은 왼쪽에서 10번째 열이므로)

6. 조회 값과 정확히 일치하거나 대략적으로 일치하는 항목을 찾으려면 범위 조회를 입력하세요.

월 수익과 관련된 상황에서는 검색 중인 테이블에서 정확히 일치하는 항목을 찾고 싶습니다. 이렇게 하려면 "범위 조회" 필드에 "FALSE"를 입력하세요. 이는 각 판매 연락처와 관련된 정확한 수익만 찾으려는 Excel을 알려줍니다.

궁금한 질문에 답하려면 예, Excel에서 정확한 일치 항목 대신 대략적인 일치 항목을 찾도록 허용할 수 있습니다. 이렇게 하려면 위에 표시된 네 번째 필드에 FALSE 대신 TRUE를 입력하면 됩니다.

VLOOKUP이 대략적인 일치로 설정되면 해당 값과 동일한 데이터가 아닌 조회 값과 가장 유사한 데이터를 찾습니다. 예를 들어, 웹사이트 링크 목록과 관련된 데이터를 검색할 때 일부 링크의 시작 부분에 "https://"가 있는 경우 다음과 같은 링크가 있는 경우를 대비해 대략적으로 일치하는 항목을 찾아야 할 수도 있습니다. 이 "https://" 태그가 없습니다. 이렇게 하면 초기 텍스트 태그 없이 링크의 나머지 부분이 일치하여 Excel에서 오류를 찾을 수 없는 경우 VLOOKUP 수식이 오류를 반환할 수 있습니다.

7. '완료'(또는 'Enter')를 클릭하고 새 열을 입력하세요.

1단계에서 새 열에 원하는 값을 공식적으로 가져오려면 “범위 조회” 필드를 채운 후 “완료”(또는 Excel 버전에 따라 “Enter”)를 클릭하세요. 그러면 첫 번째 셀이 채워집니다. 이 기회에 다른 스프레드시트를 살펴보고 이 값이 올바른지 확인할 수 있습니다.

그렇다면 첫 번째로 채워진 셀을 클릭한 다음 이 셀의 오른쪽 하단에 나타나는 작은 사각형을 클릭하여 각 후속 값으로 새 열의 나머지 부분을 채웁니다. 완료! 모든 값이 표시되어야 합니다.

VLOOKUP 튜토리얼

위의 단계에 따라 자체 VLOOKUP을 수행하려고 시도한 후 막히셨나요? Microsoft에서 제공하는 이 편리한 튜토리얼을 확인하세요. 기능을 올바르게 사용하는 방법을 안내하는 편리한 튜토리얼이 있습니다.

이미지 소스

위 단계를 따랐는데도 VLOOKUP이 여전히 작동하지 않으면 다음 중 하나에 문제가 있을 수 있습니다.

• 구문 (즉, 수식을 구성한 방법)
• 값 (즉, 찾고 있는 데이터가 양호하고 형식이 올바른지 여부)

VLOOKUP 구문 문제 해결

먼저 지정된 셀에 작성한 VLOOKUP 수식을 살펴보세요.

• 키 식별자에 대한 올바른 조회 값을 참조하고 있습니까?
• 검색해야 하는 값에 대해 올바른 테이블 배열 범위를 지정합니까?
• 범위에 대해 올바른 시트를 지정합니까?
• 그 시트의 철자가 정확합니까?
• 시트를 참조하기 위해 올바른 구문을 사용하고 있습니까? (예: 페이지!B:K 또는 '시트 1'!B:K)
• 올바른 열 번호가 지정되었습니까? (예: A는 1, B는 2 등)
• 시트 설정 방법에 대한 올바른 경로가 True 또는 False입니까?

VLOOKUP 값 문제 해결

구문이 문제가 아닌 경우 수신하려는 값에 문제가 있을 수 있습니다. 이는 VLOOKUP이 참조된 값을 찾을 수 없는 #N/A 오류로 나타나는 경우가 많습니다.

• 값의 형식이 수직으로, 오른쪽에서 왼쪽으로 지정되어 있습니까?
• 값이 참조하는 방식과 일치합니까?

예를 들어, URL 데이터를 조회하는 경우 각 URL은 동일한 행의 왼쪽에 해당 데이터가 있는 행이어야 합니다. 데이터가 수직으로 이동하는 열 머리글로 URL이 있는 경우 VLOOKUP이 작동하지 않습니다.

이 예에 따라 URL은 두 시트의 형식이 일치해야 합니다. 한 시트에 값에 "https://"가 포함되어 있고 다른 시트에 "https://"가 누락된 경우 VLOOKUP은 값을 일치시킬 수 없습니다.

강력한 마케팅 도구로서의 VLOOKUP

마케터는 리드 생성(및 그 이상)에 대한 완전한 그림을 얻기 위해 다양한 소스의 데이터를 분석해야 합니다. Microsoft Excel은 특히 VLOOKUP 기능을 사용하여 이 작업을 정확하고 대규모로 수행할 수 있는 완벽한 도구입니다.

편집자 주: 이 게시물은 원래 2019년 3월에 게시되었으며 포괄성을 위해 업데이트되었습니다.