엑셀에서 VLOOKUP 함수를 완벽하게 활용하는 방법
엑셀의 VLOOKUP 함수 는 너무 커서 수동으로 스크롤하기 어려운 데이터 세트를 다룰 때 정말 유용한 도구입니다.누구나 한 번쯤은 경험해 봤을 법한데, 방대한 표에서 특정 정보를 찾느라 애를 먹었던 적이 있을 겁니다. VLOOKUP은 강력한 함수이지만, 특히 매개변수를 사용할 때 어려움을 겪는 경우가 많습니다.함수가 제대로 작동하지 않거나 오류가 발생하는 경우, 범위 설정, 열 인덱스 또는 범위 조회 매개변수에 문제가 있을 가능성이 높습니다.이 가이드를 통해 이러한 문제를 해결하는 방법을 알아보세요.
VLOOKUP 함수를 제대로 사용하면 스프레드시트의 여러 부분에서 관련 정보를 추출하여 중복이나 수동 오류를 방지하고 시간을 크게 절약할 수 있습니다.다만, 일부 환경에서는 특히 수식을 복사하는 경우 적용이 제대로 되지 않아 스프레드시트를 새로 고치거나 재부팅해야 할 수도 있습니다.자, 그럼 VLOOKUP 함수를 제대로 활용하는 일반적인 해결 방법과 팁을 살펴보겠습니다.
엑셀에서 VLOOKUP 함수가 작동하지 않을 때 해결 방법
방법 1: 범위와 열 번호를 다시 확인하세요
가장 흔한 문제 중 하나는 테이블 배열 이나 열 인덱스를 잘못 지정하는 것입니다.범위가 시트의 내용과 정확히 일치하지 않거나 열 인덱스가 잘못되면 VLOOKUP 함수는 올바른 값을 반환하지 않습니다.따라서 다음 사항을 확인하십시오.
- 참조하는 범위가 실제로 데이터를 포함하는지 확인하세요.예를 들어 데이터가 B 열 부터 D 열까지 있다면 수식은 다음과 같아야 합니다
=VLOOKUP(G3, B:D, 3, 0). - column_index 는 table_array 의 첫 번째 열에 대해 1부터 시작합니다.세 번째 열에서 데이터를 가져오려면 3을 사용하면 됩니다.
- 범위에 오류가 발생할 수 있는 빈 열이나 숨겨진 열이 포함되어 있지 않은지 확인하십시오.
이것이 도움이 되는 이유: 마치 GPS가 올바른 방향을 가리키는 것과 같습니다.그렇지 않으면 길을 잃거나 엉뚱한 곳으로 안내될 수 있습니다.적용 사례: VLOOKUP 함수가 #N/A를 반환하거나 잘못된 데이터를 반환할 때.모든 설정이 일치하면 정확한 결과를 얻을 수 있습니다.때로는 범위나 열 번호를 수정하는 것만으로 문제가 해결되는 경우도 있습니다.저도 그런 실수를 여러 번 경험했습니다.
방법 2: 범위 조회를 통한 정확한 일치 확인
또 다른 흔한 오류는 range_lookup 매개변수입니다.이 매개변수를 TRUE 로 설정 하거나 비워두면 VLOOKUP 함수가 근사치를 계산하게 되는데, 이로 인해 잘못된 정보가 나오거나 전혀 일치하는 항목이 없는 경우가 많습니다.정확한 일치를 위해서는 range_lookup 매개변수를 명시적으로 FALSE (또는 0 ) 로 설정해야 합니다.
- 공식을 확인하세요:
=VLOOKUP(G3, B:D, 3, FALSE)또는=VLOOKUP(G3, B:D, 3, 0). - 이는 데이터 세트가 정렬되어 있지 않거나 주문 번호 또는 ID와 같이 정확한 정보가 필요한 경우에 적용됩니다.
왜 도움이 될까요? 엑셀은 종종 불필요하게 복잡한 계산 방식을 사용하며, 근사값 계산을 기본값으로 사용하는 경우가 있습니다.이 설정을 명시적으로 지정하면 정확한 계산을 보장할 수 있습니다.이렇게 하면 오류가 줄어들고 더욱 일관된 결과를 얻을 수 있습니다.일부 컴퓨터에서는 명시적으로 설정을 변경하지 않으면 수식이 초기화되는 경우가 있습니다.
방법 3: 숨겨진 셀 또는 병합된 셀 확인
때때로 데이터 자체에 문제가 있을 수 있습니다.숨겨진 열, 병합된 셀 또는 서식 오류로 인해 VLOOKUP 함수가 범위를 읽는 방식이 잘못될 수 있습니다.시트를 열고 확인해 보세요.
- 검색 범위에 병합된 셀이 있는 경우 병합을 해제하세요.
- 데이터에 불일치를 유발할 수 있는 빈 행이나 열이 없는지 확인하십시오.
왜 이것이 중요할까요? Excel은 숨겨진 셀이나 병합된 셀을 범위에 포함되지 않은 것으로 오인하여 건너뛸 수 있습니다.어떤 설정에서는 이로 인해 일치하는 항목이 없거나 잘못된 결과가 나올 수 있습니다.셀의 가시성을 수정하거나 셀 구조를 조정하면 수식이 갑자기 제대로 작동하는 경우가 많습니다.정확한 이유는 모르겠지만, 이렇게 하니 한두 번 문제가 해결되었습니다.
방법 4: 간단한 정적 조회를 사용하여 수식을 테스트합니다.
동적 셀 참조를 사용하기 전에, 먼저 알려진 값을 수식에 직접 입력해 보세요.예를 들면 다음과 같습니다.
=VLOOKUP("X10003", B:D, 3, FALSE)이 기능을 사용하면 데이터 세트에 문제가 있는지 아니면 수식 구문에 문제가 있는지 파악할 수 있습니다.예상대로 결과가 반환되면 설정에는 문제가 없으며, 조회 값이나 원래 수식에서 참조를 사용하는 방식에 문제가 있을 가능성이 높습니다.
방법 5: INDEX-MATCH를 백업으로 사용
VLOOKUP 함수가 제대로 작동하지 않을 때는 INDEX-MATCH 조합으로 바꾸는 것이 더 나은 경우가 많습니다.특히 유연성이 더 필요하거나 대규모 데이터 세트를 다룰 때 그렇습니다.다음은 간단한 예입니다.
=INDEX(C:C, MATCH(G3, B:B, 0))처음 설정하는 데 약간 번거로움이 있지만, 일부 스프레드시트에서는 더 안정적이고 빠릅니다.게다가 첫 번째 열에서만 검색해야 하는 제한도 없습니다.엑셀이 가끔씩 복잡한 과정을 요구할 때, 이 기능은 당신의 비밀 병기가 될 수 있습니다.
이러한 해결책이 효과를 발휘할 시기는 언제일까요?
위의 방법들을 시도해 본 후에도 수식에서 오류가 계속 발생하면, 특히 조회 값의 데이터 일관성을 다시 한번 확인하세요.(예: 추가 공백, 서식 차이, 대소문자 구분) 또한, 일부 수식은 새로 고침하거나 시트를 다시 계산해야 할 수 있습니다( Ctrl+CF9 키 누르기 ).또는 Excel을 저장하고 다시 열어야 할 수도 있습니다.일시적인 오류일 가능성도 있습니다.
일부 기기에서는 재부팅 전까지 수식이 제대로 작동하지 않을 수 있으므로, 재부팅을 통해 일시적으로 문제가 해결되더라도 놀라지 마십시오.
이 글이 도움이 되었나요?