[엑셀팁] VLOOKUP 오류 해결 및 ISNA 활용법

■ 엑셀(excel)의 막강한 기능 vlookup 과 index 

 대학생 과제 수행이나 회사에서 데이터 관리를 할때 제일 유용하게 사용하는 것에 vlookup 일 것입니다. 

기본적인 덧셈,뺄셈,곱셈, 나눗셈, SUM, AVG 등 다음으로 많이 사용하는 것이 vlookup 입니다. 서로 다르게 존재하는 데이터 set 들의 연관관계 분석을 통해서 데이터의 가치를 더욱 의미 있게 해주기 때문입니다. 

  vlookup 기존적인 사용법과 2중, 3중의 vlookup 의 활용을 위해서는 참고 글을 참고하십시요.

 ( 참고글 및 예제파일  :  [팁] 엑셀 VLOOKUP 무작정 따라하기: 다차원 VLOOKUP 및 INDEX 함수 ) 

■ 자주 발생하는 hlookup 과 vlookup  의 오류  해결 방법

 외부에서 텍스트 데이터를 가져와서 붙여 넣기를 하는 경우, vlookup 이 예상했던 것처럼 결과를 가져오지 않을때가 있습니다.  생각하기에는 분명히 맞는 데이터가 있는데, 결과 값에 #N/A 가 나와 맞는 값을 찾지 못했다는 오류 메시지가 나오신 경우가 많이 있으실 것입니다. 

 아래 파일을 함께 보겠습니다. 아래 데이터는 논산시 내에서의 세부 지역별로 월 강우량을 조사한 결과 입니다.

 저는 강경 지역의 1월(4mm), 8월(249mm), 11월(45mm) 데이터를 찾고 싶어서 구분에 1,8,11,15(단 15는 진짜 #N/A Case) 를 넣고 vlookup 수식을 넣었습니다. 그런데 결과는 모두 #N/A 가 나왔습니다. 왜 그럴까요?

( 아래 그림의 엑셀 예제 파일은 위쪽 참고글에서 다운 받으십시요. )

.

1. vlookup 의 두번째인 테이블 영역의 첫번째 칼럼의 실제 데이터를 확인 

 

 셀B4 의 값을 보면 실제로 1일 아니라 '1 임을 알수 있습니다. 즉 외부에서 데이터를 붙여넣기를 하는 과정에서 데이터의 왜국이 발생하였습니다. 

2. vlookup 수식내 첫번째 값인 lookup_value  부분의 실제 데이터를 확인 

 lookup_value 인 셀I4의 값은 1 임을 알수 있습니다. 즉 B4 와 I4 의 값이 보이게는 1로 일치하지만 실제로는 불일치 하기 대문에 vlookup으로 데이터가 정상적으로 조회 않았던 것입니다. 

3. 데이터 불일치로 인한 조회 오류 해결 방법 

 그러면, 두개의 값을 어떻게 한번에 맞추어 갈수 있을까요? 물론 수작업으로 셀마다 앞에 붙어 있는 ' 기호를 삭제해 주는 방법이 있겠지만, 데이터의 양이 많으면 쉽지 않습니다. 방법이 있습니다. 

 

 아래 그림과 같이 먼저 셀 B18 자리에 숫자 1을 입력합니다. 

4. 테이블내 데이터 값 일치 작업 

 셀B18에  1을 넣은 뒤 Ctrl + C 로 해당 셀을 복사합니다. 그리고 테이블 영역의 제일 앞 칼럼 전체를 마우스로 클릭하고 마우스 오른쪽 버튼을 누릅니다. 우클릭 메뉴중에 '선택하여 붙여넣기' 를 선택합니다. 

5. 숫자 곱하기를 통한 데이터 일치 작업

 '선택하여 붙여넣기' 팝업에서 '값' 과 '곱하기' 를 선택한후 '확인' 버튼을 눌러 줍니다. 

.

6. 데이터 일치시켜 데이터 분석 함수 활용하기 

 정상적으로 처리 되었다면 기존에 #N/A 로 보였던 부분이 강우량이 정상적으로 조회 될 것입니다. 아래 그림은 제가 테스트 하는 과정에서 3번째 col_index_num 을 3 인 '강경' 기준이 아니라 2 인 '시청' 기준으로 입력하여 '시청' 기준의 강우량이 조회 되었습니다. vlookup 함수는 정상 작동합니다. 

7. 데이터 불일치 데이터에 대한 별도 처리 

 월에 15 값이 없이 때문에 4번째는 계속해서 #N/A 로 조회 되었습니다. 기존 vlookup 함수를 좀 더 조건을 주어서 아래와 같이 수정한다면 #N/A 표시에 대해서 좀 더 깔끔하게 보여 질 수 있을 것입니다. 

 예제) IF(ISNA(VLOOKUP( ~ ), "NOT FOUND", "?")

■  vlookup 와 Index 의 막강함.

 데이데를 데이터베이스에 넣어 sql query 로 조작하는 것보다 엑셀을 통해서 간단하게 분석하는 경우가 더욱 편리하고 빠를 때가 많습니다. 데이터의 움직임이 눈에 보이고 바로바로 확인하실 수 있기 때문입니다. 단순 vlookup 에서 2중,3중 vlookup 을 활용해 깊이 있는 데이터 분석을 해보실 바랍니다. 

■  참고 엑셀 팁 

[팁] 엑셀 암호저장 암호해제 그림압축 방법 

[팁] 엑셀 그래프 그리기 및 두개 Y 축 설정하기

[팁] 엑셀 SUMIF COUNTIF 무작정 따라하기 : 조건 검색 업무 활용하기(예제파일포함)

[팁] 엑셀 VLOOKUP 무작정 따라하기: 다차원 VLOOKUP 및 INDEX 함수  

[팁] 엑셀 표 스타일 적용해 데이터 분석 및 꾸미기  

[팁] 엑셀 메모 출력 및 단숨에 깔끔한 표 만들기