Excel(엑셀)

[엑셀 함수] 데이터 참조 hlookup, vlookup 함수

데이터뱁새 2023. 1. 6. 09:50

 

특정 데이터값에 맞는 데이터를 "찾고" 싶을 때 쓰면 유용한 hlookup, vlookup 함수. 

VLOOKUP(기준값, 참고테이블, 몇 번째, FALSE)
HLOOKUP(기준값, 참고테이블, 몇번째, FALSE)

사실 두 함수는 같은 의미의 찾기/참조 함수이다. 다른 점은 기준값을 기준으로 가로로 읽을 거냐(Vertical), 세로로 읽을 거냐(Horizontal)의 차이일 뿐.

 

 

VLOOKUP 사용법

 

예를 들어, 이름이 "가링이"인 사람의 성적을 알고 싶다! 할 때 아래의 표를 참고해 보자.

 

만약 평균성적이 80점 이상이어야 합격을 한다면 「시험성적파일」에서의 국어, 영어, 수학의 데이터는 필요가 없다. 또한 「합격 여부 확인」이라는 또 다른 표에서는 가랑이, 뱁새, 찢기의 성적만 필요하기 때문에 이때의 값을 「시험성적파일」에서 일일이 찾아서 입력할 수도 있겠지만 이때 필요한 게 바로 vlookup이다. 

 

>>> 내가 찾고자 하는 값은 "가랑이"의 "평균성적"이기 때문에

① 기준이 되는 값은 "가랑이"(=성명열=C열)

② 값을 가져오고 싶은 참조 테이블은「시험성적파일」이므로 해당 테이블 전체를 드래그해 준다. 근데 이때 뱁새나 찢기도 동일한 테이블을 참조할 것이기 때문에 절댓값(F4)을 씌워주자. 만약 절대값 안 씌워준다면 뱁새에서는 해당 테이블이 한 칸씩 밑으로 내려간다.(I5:M12→I6:M13).

③ 「시험성적파일」에서 가져오고 싶은 값인 "평균성적"은 참조표를 기준(I열부터 시작하니까 I열부터 M열까지는 5개 차이)으로 5번째에 있다.

④ "가랑이"를 가져와야지 "가방이"나 "아궁이"처럼 유사해 보이는 값이라고 가져오면 안 되니 FALSE로!!

 

 

 

쉽쥬? 처음에는 헷갈려도 여러 번 써보면 금방 익숙해질 거다. 그냥 기준값과 테이블의 숫자!! 만 기억하면 된다.

 

 

 

 

HLOOKUP 사용법

그렇다면 hlookup은 vlookup의 행렬 반전 버전이라고 보면 된다. 만약 표가 위가 아니라 아래처럼 되어있을 때 hlookup을 적용하면 된다.

"가랑이"라는 이름을 기준으로 5칸 밑으로 내려가니 =HLOOKUP(C5,$J$4:$Q$8,5, FALSE)로 입력하면 간단!

 

 

 

 

VLOOKUP/HLOOKUP 오류 케이스

1. 테이블 다 못 채워서 #N/A 오류

사진처럼 테이블 참조가 잘못되어 "가랑이"라는 데이터값이 아예 포함되지 않을 경우, 해당값없음 오류가 뜬다. 주로 테이블 참조에 절댓값을 하지 않아 수식을 복사할 때 발생하는 오류이기 때문에 꼭 테이블이 제대로 선택되었는지, 절댓값으로 입력되었는지 확인하자!

 

 

2. 테이블에 포함되지 않은 숫자로 #REF 오류 발생

테이블에 포함되지 않은 열의 수를 입력했을 때 발생하는 참조오류. 테이블은 I열을 기준으로 5까지만 가능한데, 6을 입력해 버려서, 6이 나올만한 테이블이 없는데?라는 오류이다. 이때는 불러오는 값의 숫자를 5로 다시 수정해주면 된다.

 

 

3. 참조 기준값 없어서 #N/A 오류 발생

1번 오류와 비슷한데 이건 아예 테이블 자체를 잘못 참고한 거다. 성명의 값을 기준점으로 잡고 싶다면 꼭 성명열부터 테이블을 시작할 것!! 그리고 위의 오류는 2번의 내용도 포함한다. 테이블 열의 개수가 4개뿐인데, 불러오는 값으로 5를 넣었으니 #REF 오류도 나올 것이다.

 

 

결국 정확한 테이블 참조가 중요하니 테이블 범위와 절댓값 주의하도록 하자!!

 

 

 

4. 엑셀 값의 함정

 

그렇다면 아래의 "가랑이"는 왜 오류가 떴을까...? 일단 #N/A 오류가 떴다는 건 값 오류이므로 기준값이 되는 "가랑이"에 무슨 문제가 있는 건지를 확인해봐야 한다. (#REF 오류였다면 참조 테이블이나 불러오는 값 숫자 확인하기)

비밀은 바로... 성명 가운데 정렬 해제가 힌트다. "가랑이"가 딱 보기에는 "가랑이"처럼 보이지만 실제로 더블클릭해서 값을 확인해 보면 "가랑이_____"이다. 따라서 이 뒤의 띄어쓰기 부분을 삭제해줘야 한다. 띄어쓰기는 뭐... 일일이 눌러서 지워줄 수도 있지만 내가 주로 쓰는 방법은, 성명열을 전부다 드래그한 후 Ctrl + F(찾기 단축기) 누르고, 옆의 바꾸기 탭으로 이동해 아래와 같이 적용한다. 이러면 띄어쓰기 한 칸을 전부 공란으로 바꾸기!! 가 되므로 뒤의 공란이 다 사라진다. 공란이나 띄어쓰기 한 번에 없애기 쌉가능.

 

 

이외에도 숫자가 문자형식으로 설정되어 있거나, 참조 테이블(ex.「시험성적파일」)의 기준값이 공백이 있거나, 다른 형식이거나 한 경우 등 많은 오류가 있으니 오류의 원인이 무엇인지 파악하고 해결해보도록 하자!