VLOOKUP 수식을 사용하여 Excel 목록에서 데이터를 가져 오는 방법. 일이 잘못되면 VLOOKUP 문제를 해결하십시오. VLOOKUP 예제가 포함 된 단계별 비디오 및 Excel 통합 문서.
VLOOKUP 비디오
아래에 표시된 제품 가격 조회 비디오에서 VLOOKUP 수식을 만드는 단계를보십시오. 서면 지침은 비디오 아래에 있습니다. 동영상과 함께 따라갈 제품 가격 조회 샘플 파일을 가져옵니다 (다운로드 파일 # 4).
조회 테이블 위치 선택
각 조회를 저장하는 것이 좋습니다. 통합 문서의 별도 워크 시트에 테이블을 추가합니다. 그러면 조회 테이블에서 행을 추가하고 삭제할 때 다른 테이블의 행을 실수로 추가하거나 삭제하지 않습니다.
이 예에서 조회 테이블은 제품이라는 시트에 있습니다.
조회 테이블 만들기
조회 수식은 세로로 작동하여 열 아래의 값을 찾거나 가로로 작동하여 행에서 값을 찾을 수 있습니다. . 이 예에서 정보는 열 아래 값과 함께 세로로 저장되며 나중에 “VLookup 수식을 사용하여 세로 조회를 수행합니다.
- 첫 번째 행에 제목을 입력하세요.
- 첫 번째 열에는 조회 기준이 될 고유 한 키 값이 포함되어야합니다.이 예에서 다음을 찾을 수 있습니다. 특정 제품 코드의 가격입니다.
- 워크 시트에 다른 데이터가있는 경우 표 하단에 하나 이상의 빈 행을 남겨두고 표 오른쪽에 하나의 빈 열을 남겨 두어 다른 데이터에서 테이블을 조회합니다.
참고 : 테이블을보다 쉽게 참조 할 수 있도록 범위 이름을 지정할 수 있습니다. 여기에 지침이 있습니다. 범위 이름 지정 ▲ TOP
VLOOKUP 함수 인수
VLOOKUP 함수에는 4 개의 인수가 있습니다.
- lookup_value : 어떤 값을 찾으시겠습니까?이 예에서 제품 코드는 A7 셀에 있으며 해당 값을 찾으려고합니다. oduct 이름.
- table_array : 조회 테이블은 어디에 있습니까? 상대 참조 (A2 : C5) 대신 절대 참조 ($ A $ 2 : $ C $ 5)를 사용하면 수식을 다른 셀에 복사하는 것이 더 쉽습니다. 또는 조회 테이블의 이름을 지정하고 이름으로 참조하십시오.
- col_index_num : 반환 할 값이있는 열은 무엇입니까? 이 예에서 제품 이름은 룩업 테이블의 두 번째 열에 있습니다.
- : 정확히 일치 하시겠습니까? 대략적인 일치는 괜찮습니까?
TRUE를 마지막 인수로 사용하거나 마지막 인수를 생략하면 대략적인 일치가 반환 될 수 있습니다. 이 예제에는 마지막 인수로 FALSE가 있으므로 제품 코드를 찾을 수없는 경우 결과는 # N / A가됩니다. (참고 : Excel은 다소 관대하며 FALSE 대신 0, TRUE 대신 1을 허용합니다.) ▲ TOP
VLookup 수식 만들기
조회 테이블을 만든 후에는 통합 문서에 다른 수식을 만들고 제품 목록에서 정보를 가져올 수 있습니다. 예를 들어 송장을 준비하기 위해 제품 코드를 입력하면 공식이 제품 테이블에서 제품 이름 또는 가격을 가져옵니다.
이 예에서 송장은 Invoice라는 시트에 생성됩니다. VLOOKUP 수식은 제품 코드와 정확히 일치하는 항목을 찾아 제품 이름을 반환해야합니다.
제품 가격을 계산하는 VLOOKUP 수식을 만들려면 다음 단계를 따르세요.
- 인보이스 시트 선택
- A7 셀에 제품 코드 A23 입력
- B7 셀에서 VLOOKUP 수식 시작 :
= VLOOKUP ( - 클릭 셀 A7을 입력하여 참조를 입력합니다.
- 쉼표를 입력하여 첫 번째 인수를 끝냅니다.
- 제품 시트 선택
- 셀 A2 : C5 (제품 목록 )
- F4 키를 눌러 셀 참조를 절대 값으로 변경합니다. 이제 수식이 다음과 같이 표시됩니다.
= VLOOKUP (A7, Products! $ A $ 2 : $ C $ 5 - 쉼표를 입력하여 두 번째 인수를 끝냅니다.
- 제품 이름이 포함 된 조회 테이블의 열인 2를 입력합니다.
- 쉼표를 입력하여 세 번째 인수입니다.
- FALSE를 입력하여 제품 코드와 정확히 일치하는 항목이 있음을 지정하고 닫는 대괄호를 추가합니다.
- 이제 수식이 l로 표시됩니다. 이와 같이 :
= VLOOKUP (A7, Products! $ A $ 2 : $ C $ 5,2, FALSE) - Enter 키를 눌러 수식을 완성하십시오. 제품 이름이 표시됩니다.
참고 : 제품 가격을 반환하려면 조회 테이블의 열 3을 참조하는 VLOOKUP 수식을 만듭니다. 예를 들어 C7 셀에 다음 수식을 입력합니다.
= VLOOKUP (A7, Products! $ A $ 2 : $ C $ 5,3, FALSE) ▲ TOP
값 범위에 대한 VLOOKUP 수식
어떤 상황에서는 대략적인 일치가 선호되므로 여러 값이 동일한 결과를 반환합니다. 예를 들어, 학생 논문을 채점 할 때 85 점 이상의 모든 논문은 A 등급을 받아야합니다.
이 수식을 만드는 단계를 보려면 아래 표시된 VLOOKUP 비디오를 시청하십시오. 서면 지침은 동영상 아래에 있습니다.
이 예에서는 Grades라는 시트에 조회 테이블이 생성됩니다. 조회 테이블을 생성하려면 A 열에 각 등급의 최소 점수를 입력합니다. B 열에 일치하는 등급을 입력합니다. 점수를 오름차순으로 정렬합니다.
셀 A2 : B6의 이름은 GradeList입니다.
점수는 VLOOKUP 공식이 성적을 계산하는 보고서 카드라는 시트에 입력됩니다.
아래 스크린 샷에서 수식이 6 행에 복사되었으며 C6 셀에서 수식을 볼 수 있습니다.
VLOOKUP을 MATCH와 결합
VLOOKUP 수식에 열 번호를 입력하는 대신 MATCH 함수를 사용하여 조회 테이블에서 올바른 열을 찾습니다. 다음과 같은 두 가지 이점이 있습니다.
- 공식을 유연하게 만들어 워크 시트에서 수식을 더 쉽게 복사 할 수 있습니다.
- 새 열이 추가되면 문제를 방지 할 수 있습니다. 조회 테이블에서 또는 조회 열이 재정렬 된 경우
이 동영상은 단계를 보여 주며 동영상 아래에 다른 예에 대한 서면 지침이 있습니다.
브라우저에서이 프레임을 표시 할 수 없습니다. 다음은 페이지 링크입니다.
VLOOKUP With MATCH for Order Details
이 예에서 VLOOKUP 수식은 주문 ID 번호를 기반으로 룩업 테이블에서 주문 세부 정보를 반환합니다. . 다음은 tblOrders라는 조회 테이블입니다.
참고 :이 예제는 OrdersMATCH라는 시트의 Sample Workbook # 1에 있습니다.
다음은 VLOOKUP 수식이있는 워크 시트입니다. 각 주문에 대한 지역, 주문 날짜 및 주문 금액이 필요하므로 3 개의 VLOOKUP 공식이 필요합니다.
공식에 열 번호를 입력하면 각 열에 다른 수식이 필요합니다.
- 지역 : = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate : = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt : = VLOOKUP ($ B6, tblOrdersALL, 4,0)
MATCH 함수
VLOOKUP 수식에 열 번호를 입력하는 대신 MATCH 함수를 사용할 수 있습니다. MATCH 함수는 목록에서 항목의 위치를 찾고 위치 번호를 반환합니다.
아래 스크린 샷에서 MATCH 수식은 조회 테이블의 표제 셀 (A1 : D1)에서 “지역”의 위치로 2를 반환합니다.
= MATCH ( C5, Orders_ALL! $ A $ 1 : $ D $ 1, 0)
참고 :이 기술이 올바르게 작동하려면 VLOOKUP 시트의 제목은 조회 테이블 제목과 정확히 일치해야합니다. 정확한 일치를 보장하기 위해 VLOOKUP 머리글 셀은 조회 테이블 머리글 셀에 연결됩니다.
VLOOKUP에 MATCH 추가
VLOOKUP 수식에 MATCH 함수를 추가하려면 입력 된 열 번호
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
MATCH 수식 사용 :
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Orders_ALL! $ A $ 1 : $ D $ 1, 0), 0)
VLOOKUP 수식 복사
이제 각 열에 다른 수식이 필요하지 않고 수식을 복사하여 모든 열에 동일한 수식을 사용할 수 있습니다. 각 열에서 해당 열의 제목 셀을 참조하고 조회 테이블에서 해당 위치를 찾습니다.
참고 : 다른 형식으로 열에 수식을 채우는 경우 다음 단계를 따르십시오.
- 복사 할 수식이있는 셀 선택
- 선택한 셀의 채우기 핸들 (오른쪽 하단의 작은 정사각형)을 가리 킵니다.
- 마우스 오른쪽 버튼을 누르고 수식이 필요한 마지막 셀로 드래그
- 마우스 버튼을 놓고 서식없이 채우기를 클릭합니다.
IF 및 VLOOKUP 결합
IF 수식을 VLookup 수식과 함께 사용하여 찾은 경우 정확한 값을 반환하고 빈 문자열을 반환 할 수 있습니다. 찾을 수없는 경우.
IF 및 VLOOKUP 수식을 설정하는 단계를 보려면이 짧은 동영상을 시청하세요. 서면 지침은 동영상 아래에 있습니다.
IF와 VLOOKUP을 결합하여 오류를 숨기려면 다음 단계를 따르세요.
- 인보이스 시트의 A8 셀에 제품을 입력합니다. 코드 A28. 셀 B8의 VLookup 수식에 네 번째 인수가 FALSE 인 경우 결과는 # N / A입니다. 룩업 테이블의 제품 코드와 정확히 일치하는 항목이 없기 때문입니다.
- VLookup 수식을 IF 수식 (이 예에서는 제품 목록의 이름이 지정됨), ISNA 함수를 사용하여 # N / A 오류 확인 :
- Enter 키를 누르면 셀이 비어 있습니다. 정확히 일치하는 항목이 없기 때문에 VLookup 수식에서 # N / A를 반환 했으므로 ISNA 함수 결과는 TRUE입니다. IF 공식은 이것을 빈 문자열로 변환했습니다.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), “”, VLOOKUP (A8, ProductList, 2, FALSE))
IFERROR와 VLOOKUP 결합
이 공식을 제안 해 주신 Chip Pearson에게 감사드립니다.
Excel 2007에서 새로운 함수 인 IFERROR가 도입되었습니다. . VLookup과 함께 IFERROR 수식을 사용하여 여러 테이블에서 값을 확인하고 찾은 경우 관련 정보를 반환 할 수 있습니다. 이 예에서는 West, East 및 Central의 세 지역에 주문 시트가 있습니다. 각 시트에는 OrdersW, OrdersE 및 OrdersC라는 명명 된 범위가 있습니다.
주문이라는 시트에 주문 ID를 입력 한 다음 IFERROR가있는 VLOOKUP을 사용하여 각 명명 된 범위를 확인할 수 있습니다. 을 클릭하고 선택한 주문에 대한 정보를 봅니다.
- 주문 시트의 B6 셀에 주문 ID로 4를 입력합니다. 해당 주문은 중부 지역에서 이루어졌습니다.
- 동부 지역의 표를 확인하기 위해 C6 셀의 VLOOKUP 수식은 다음과 같습니다.
= VLOOKUP (B6, OrdersE, 2, FALSE) - Enter 키를 누르면 VLOOKUP 수식이 # N / A를 반환합니다. 주문 ID 4는 동부 지역 주문 테이블에 없기 때문입니다.
- 주문에 세 지역 중 하나에 배치 된 경우 각 테이블을 확인하는 공식이 필요합니다.
첫 번째 테이블에서 주문 ID를 찾을 수없는 경우 공식은 두 번째 테이블을 확인해야합니다. 주문 ID가 두 번째 테이블에 없으면 세 번째 테이블을 확인해야합니다. 주문 ID가 세 번째 테이블에 없으면 셀에 “찾을 수 없음”메시지가 나타납니다. - IFERROR 수식을 사용하면 값을 확인할 수 있습니다. 그런 다음 오류가 발견되면 수행 할 작업을 지정하십시오. 기존 수식에 IFERROR를 사용하는 경우 # N / A 오류 대신 “찾을 수 없음”을 표시 할 수 있습니다.
= IFERROR (VLOOKUP (B6, OrdersE, 2, FALSE), “찾을 수 없음”) - 세 테이블을 모두 확인하려면 다음 IFERROR 및 VLOOKUP 수식을 사용할 수 있습니다.
이것은 OrdersE 테이블을 확인하고 오류가 발견되면 OrdersW 테이블을 확인한 다음 OrdersC를 확인합니다. 세 테이블에서 OrderID를 찾을 수 없으면 셀에 찾을 수 없음 메시지가 표시됩니다.
또한 IFERROR를 사용할 수없는 이전 버전의 Excel에서 여러 테이블을 확인할 수 있습니다. 더 긴 수식 :
결합 된 값에 대한 VLOOKUP
일부 테이블에는 고유 한 값이 없을 수 있습니다. 조회 테이블의 모든 열. 예를 들어, 아래 표에서 Jacket은 A 열에 두 번 나열되어 있습니다. 그러나 각 재킷 및 크기 조합에 대한 레코드는 4 행에 Jacket Medium, 행 5에 Jacket Large가 하나뿐입니다.
큰 재킷의 가격을 찾아야하는 경우 A 열만 기반으로하는 VLOOKUP은 나열된 첫 번째 재킷의 가격을 반환합니다 (중간 ). 재킷의 가격을 낮게 책정하여 65.00 대신 60.00에 판매합니다.
고유 한 조회 값을 생성하려면 표 왼쪽에 새 열을 삽입하고 수식을 사용하여 제품과 크기를 결합 할 수 있습니다. A2 셀에서 수식은 B2의 값과 파이프 문자 및 C2의 값을 결합합니다.
= B2 & “|” & C2
이 수식을 데이터의 마지막 행까지 복사하여 각 행이 A 열에 고유 한 값을 갖도록합니다.
참고 : 파이프 문자 대신 “데이터에 포함되지 않은 다른 문자를 사용할 수 있습니다.
그런 다음 VLOOKUP 공식에서 제품과 크기를 Lookup_value로 결합합니다. H1 셀에서 공식은 F1의 값과 파이프 문자 및 G1의 값을 결합합니다.
= VLOOKUP (F1 &”| “& G1, $ A $ 2 : $ D $ 5,4, FALSE)
참고 : 현재 가격은 3 열 대신 4 열. ▲ TOP
VLOOKUP 수식 문제 해결
VLOOKUP 수식 찾고있는 값이 조회 테이블에있는 것처럼 보이지만 # N / A를 반환 할 수 있습니다. 일반적인 원인은 다음과 같습니다.
텍스트 대 숫자
공백 문자
기타 문자
텍스트 대 숫자
이 오류의 일반적인 원인은 값 중 하나가 숫자이고 다른 하나가 텍스트이기 때문입니다. 예를 들어, 조회 테이블에 “123 (텍스트)이 포함될 수 있고 조회 할 값은 123 (숫자)입니다.
- 가능하면 다음 중 하나를 사용하여 텍스트를 숫자로 변환하십시오. 여기에 표시된 방법 : 텍스트를 숫자로 변환
- 데이터를 변환 할 수없는 경우 아래 솔루션 중 하나를 사용하여 VLOOKUP 수식 내에서 조회 값을 변환 할 수 있습니다.
- 값 조회 텍스트, 테이블에 숫자 있음
- 조회 값은 숫자, 테이블에 텍스트 있음
- 어떤 값이 텍스트이고 어떤 값이 숫자인지 알아 보려면 자세한 번호 또는 텍스트 문제 해결 단계
1. 조회 값은 텍스트이고 테이블에는 숫자가 포함되어 있습니다.
조회 테이블에 숫자가 포함되어 있고 조회 할 값이 텍스트 인 경우 다음과 유사한 공식을 사용하십시오.
= VLOOKUP (–A7, 제품!$ A $ 2 : $ C $ 5,3, FALSE)
이중 단항 (-)은 텍스트를 숫자로 변환하며 조회 값이 숫자 인 경우에도 올바르게 작동합니다.
2. 조회 값은 숫자이고 테이블에는 텍스트가 포함됩니다.
조회 테이블에 텍스트가 포함되어 있고 조회 할 값이 숫자 인 경우 다음과 유사한 공식을 사용합니다.
= VLOOKUP (A7 & “”), Products! $ A $ 2 : $ C $ 5,3, FALSE)
OR
= VLOOKUP (TEXT (A7, “00000”) , Products! $ A $ 2 : $ C $ 5,3, FALSE)
TEXT 함수는 숫자를 텍스트로 변환하며 조회 값이 텍스트 인 경우에도 올바르게 작동합니다. 첫 번째 예에서 & 연산자는 형식이 지정되지 않은 숫자에서 텍스트 문자열을 만듭니다. 두 번째 예에서 선행 0으로 형식이 지정된 숫자 (예 : 00123)는 선행 0이있는 텍스트 “숫자”와 일치합니다.
조회 테이블에 텍스트 값이있을 때 VLOOKUP 문제를 해결하는 단계를 보려면, 이 짧은 동영상 자습서를 시청하세요.
한 값에 공백이 있고 다른 값이 없음
일치하는 값이없는 또 다른 잠재적 원인은 공백의 차이입니다. 값 중 하나는 선행 공백 (또는 후행 또는 포함 된 공백)을 포함 할 수 있고 다른 하나는 포함하지 않습니다. 값을 테스트하려면 LEN 함수를 사용하여 각 값의 길이를 확인할 수 있습니다.
예 : = LEN (A7)은 A7 셀의 문자 수를 반환합니다. 조회 테이블에서 일치하는 셀의 문자 수와 같아야합니다.
가능하면 불필요한 부분을 제거합니다. 공백 및 VLOOKUP 수식이 올바르게 작동해야합니다. 공백을 제거 할 수없는 경우 VLOOKUP에서 TRIM 함수를 사용하여 선행, 후행 또는 중복 공백을 제거하십시오. 예 :
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
기타 문자
TRIM 기능만으로 문제가 해결되지 않는 경우 , 다음 제안 중 하나를 시도 할 수 있습니다.
- SUBSTITUTE 함수
- CLEAN 함수
- 웹 문자를 제거하는 매크로
SUBSTITUTE 함수
SUBSTITUTE 함수를 사용하여 원하지 않는 문자를 제거합니다. Contextures 블로그에 TRIM 및 SUBSTITUTE를 사용하여 Excel 데이터 정리
CLEAN 함수 h4의 예가 있습니다. >
VLOOKUP 문제를 해결하는 또 다른 방법은 텍스트에서 원하지 않는 문자를 제거 할 수있는 CLEAN 함수를 사용하는 것입니다.이 Contextures 블로그 게시물에 CLEAN 함수에 대한 자세한 정보가 있습니다. 30 일 내에 30 개의 Excel 함수 : 29-CLEAN
다른 값이 아닌 한 값의 웹 문자
웹 페이지에서 데이터를 복사 한 경우 HTML 비 분리 공백이 포함될 수 있습니다 (& nbsp) 문자. David McRitchie는 o와 함께이를 제거하는 매크로를 작성했습니다. 공백 문자
VLOOKUP 수식 정렬시 문제
VLOOKUP 수식은 처음에는 올바른 결과를 반환 할 수 있지만 항목 목록이 정렬되면 잘못된 결과를 표시합니다. 조회 값에 대한 참조에 시트 이름이 포함 된 경우 이러한 문제가 발생할 수 있습니다. 예 :
= VLOOKUP ( “Order Form”! B5, Products! $ B $ 2 : $ C $ 6,2, FALSE)
참고 :이 문제는 다른 함수에서 발생할 수 있습니다. INDEX / MATCH 조회 공식과 같은 것입니다.
이 비디오를보고 문제 해결 단계를 확인하고 VLOOKUP 정렬 문제 샘플 파일을 다운로드하여 따라 가십시오. 서면 지침은 비디오 아래에 있습니다.
참조의 시트 이름
이 유형의 참조는 수식을 작성하는 동안 다른 시트를 클릭하면 생성됩니다. 이렇게하면 Excel은 수식의 모든 후속 참조에 시트 이름을 추가합니다.
스크린 샷에서 위에서 Dress는 B9 셀에 있고 C9 셀에는 $ 30의 정확한 가격이 표시됩니다.
그러나 제품 AZ를 정렬 한 후 Dress는 셀 B5로 이동하지만 C5 셀의 수식은 계속 참조합니다. 셀 B9로. 참조의 시트 이름 때문에 Excel은 현재 행에 대한 참조를 유지하는 대신 원래 참조를 유지합니다. Cell C5는 드레스 대신 스웨터 가격을 보여주고 있습니다. ▲ TOP
문제 해결
문제를 해결하려면 불필요한 시트 이름을 VLOOKUP 셀 참조. 다음은 C5 셀의 수정 된 수식입니다.
= VLOOKUP (B5, Products! $ B $ 2 : $ C $ 6,2, FALSE)
불필요한 시트 이름을 제거한 후 목록을 안전하게 정렬 할 수 있으며 각 항목에 대해 올바른 결과가 표시됩니다.
샘플 파일 다운로드
1. VLOOKUP 샘플 통합 문서 (Excel 2007 이상)를 가져옵니다. 압축 된 파일은 xlsx 형식이며 매크로를 포함하지 않습니다.
2. Excel 2003의 경우이 버전의 VLOOKUP 샘플 파일을 가져옵니다. 압축 파일은 xls 형식이며 매크로를 포함하지 않습니다.
3. VLOOKUP으로 정렬 할 때 발생할 수있는 문제를 확인하려면이 VLOOKUP 정렬 문제 샘플 파일을 받으십시오. 압축 파일은 xlsx 형식이며 매크로를 포함하지 않습니다.
4. 비디오 자습서에 사용 된 제품 가격 조회 통합 문서를 가져옵니다.압축 된 파일은 xlsx 형식이며 매크로를 포함하지 않습니다. ▲ TOP
더 많은 자습서
VLOOKUP 번호 / 텍스트 문제 해결
다른 통합 문서의 VLOOKUP
HLOOKUP
CHOOSE 함수
조회-2 기준
조회
INDEX / MATCH
조회 함수 비교
함수 목록