오늘 시간에는 엑셀 함수의 꽃이라고도 불리는 VLOOKUP 함수와 HLOOKUP 함수에
대해 배워보도록 할게요!
VLOOKUP과 HLOOKUP 함수는 "수많은 데이터 속에서 내가 원하는 데이터를 한번에
찾게 해주는 강력한 함수"입니다!
먼저 함수를 사용하는 공식부터 볼게요!
VLOOKUP (lookup_value,table_array,col_index_num,[range_lookup])
범위(table_array)의 첫번째 열을 수직(VLOOKUP의 “V”: Vertical)으로 내려가면서 키값(lookup_value)를 찾은 다음, 같은 행에 있는 지정된 열(col_index_num)의 값을 반환한다.
무슨 말인지 잘 감이 안오시죠? 하지만 예제를 보면 알 수 있으니 일단
인수들 하나하나 짚고 넘어갑시다!
인수 설명
1. lookup_value: 범위에서 원하는 값을 찾기 위한 키값
2. table_array: 값을 찾을 범위
3. col_index_num: 값을 찾을 범위에서 가져올 값이 있는 열의 위치
4. range_lookup: 정확히 일치하는 키값을 찾을 것인지 근사값을 찾을 것인지 결정
(TRUE-근사값, FALSE-일치하는 값)
일단 간단한 예를 들어볼게요!
왼쪽 표에 코드번호 그리고 재고를 채워넣어야 한다고 가정했을 때
지역, 코드번호, 재고에 관한 정보가 오른쪽 표에 정리되어 있네요!
지금은 표가 6행으로 이루어져 굉장히 짧아서 일일이 비교 대조하며
채워 넣을수도 있겠죠!
그런데 100행 1000행이 넘어버린다면? 일일이 채워넣다가는
하루가 다 가겠죠?
이럴 때 필요한 게 VLOOKUP 함수입니다!
일단 코드번호를 채운다고 가정해볼게요!
저는 지금 오른쪽 표의 대구 코드번호를 왼쪽 표에서 찾고 싶은거에요!
왼쪽 표 첫째 ‘지역’열에서 ‘대구’를 찾고 그 옆의 열인 ‘코드번호’열에서 ‘D111-2’값을 찾아오면 되는거 아닌가요?
이제 아까 VLOOKUP 공식을 다시 가져올게요
“범위(table_array)의 첫번째 열을 수직(VLOOKUP의 “V”: Vertical)으로 내려가면서 키값(lookup_value)를 찾은 다음, 같은 행에 있는 지정된 열(col_index_num)의 값을 반환한다.”
키값이 되는 값은 “대구”입니다, 그리고 범위는 오른쪽 표 전체인 L10:N15가 되겠죠?
오른쪽 표 첫째 행에서 “대구”라는 값을 찾을 것이니까요!
그러면 이제 대구의 코드번호를 가져와야 하니 오른쪽 표의 2열 값을 가져와야 겠죠!
따라서 col_index_num은 2가 되는 것입니다. 마지막으로 대구의 코드번호를 가져올 때 정확히 대구의 코드번호를 딱 가져와야 하므로 range_lookup 인수는 FALSE로 지정해줄게요!
이 range_lookup 인수에 관한 설명은 잠시 후 자세히 하겠습니다!
대구의 코드번호를 찾기 위해 VLOOKUP 함수의 인수들을 다 입력해볼게요!
이렇게 대구의 코드번호만 쏙 가져왔습니다!
자동 채우기 핸들로 다른 지역의 코드번호도 다 채워볼게요!
밑으로 자동 채우기 핸들을 사용할 거라 G11에는 절대 참조나 혼합참조를
해줄 필요는 없지만
오른쪽 표는 고정시켜주어야 겠죠?
이제 재고 부분을 채워볼게요!
감이 오는 분들은 아까의 식에서 하나만 바꾸면 된다는 것을 알거에요!
바로 col_index_num 만 말이죠!
왜냐하면 이제 코드번호가 아닌 재고의 값을 원하기 때문에
이렇게요!
이번에도 table_array를 고정시켜 밑에 셀들도 자동으로 채워볼게요!
완성되었습니다!
다른 예제를 만들어 많이 연습해보세요! 연습만이 살길!
그러면 이제 아까 range_lookup 인수에는 TRUE와 FALSE가 있다고 했는데
그것에 대해 자세히 알아보도록 하죠!
먼저 TRUE는 유사한 값이라도 찾아오는 거고
FALSE는 정확히 해당하는 값이 아닌 이상 찾아오지 않습니다!
예를 들어볼게요!
위에서 예로 들었던 그 표에서 왼쪽 표의 부산을 '상주'로 바꿨는데요
이런 결과가 나옵니다!
상주의 코드번호와 재고에 오류가 뜨네요?
왜 이럴까요?
이유는 우리가 아까 range_lookup 인수를 FALSE로 설정했기 때문입니다!
상주라는 키값을 범위로 설정한 오른쪽 표에서 찾으려고 하면 찾을 수가 없잖아요!
그래서 정확한 값을 돌려주지 못하는 겁니다!
FALSE로 인수를 설정하면? 범위에서 무조건 정확한 그 키 값을 찾아낸 다음
열 인수에 해당하는 열에 가 값을 반환하는 것이죠!
그러면 TRUE 인수는 어떨 때 쓸까요?
데이터에 따라 없는 키 값일 때 오류가 표시되는게 맞을 수도 있고 (위 처럼),
찾고자 하는 값이 없을 때 비슷한 값으로 대신 가져와야 할 수도 있겠죠
그런데 여기서 한 가지 주의할 점이 있어요!
TRUE 인수를 쓸 때는 키 값을 찾는 그 범위가
즉 제가 위에서 상주를 찾으려고 했잖아요? 그러면
이 L열의 데이터들이
오름차순으로 정렬되어야 합니다.
왜그럴까요? 상주라는 값을 키 값으로 설정했는데
정확하게 일치하는 데이터가 없잖아요? 그러니까 비슷하게 유사한 데이터를 찾아야하는데
엑셀은 상주와 가장 유사한 데이터가 ‘가나다’순으로
‘상주보다 작은 값 중 가장 큰 값’이 가장 상주와 유사한 데이터라고 생각하거든요!
말이 굉장히 어렵죠? 예를 보여드릴게요
일단 하라는대로 오른쪽 표의 지역 열을 오름차순으로 정렬해볼게요
이렇게요! 그러면 ‘가나다’ 순으로 오름차순으로 정리가 되었죠?
이랬을 때 제가 ‘상주’라는 값을 키값으로 설정하고 마지막 인수를 TRUE로 설정했다면
엑셀은 오름차순 되어있는 L열에서 상주와 가장 비슷한 값을 찾기 시작합니다
아까 제가 엑셀은 가장 유사한 데이터로 “그 값보다 작은 값 중 가장 큰 값”을 인식한다고 했죠?
상주보다 작은 값들은 대구, 부산이 있습니다. 서울과 수원은 같은 ‘ㅅ’이지만 모음에서 ‘ㅏ’보다 크니까요!
대구와 부산 중 가장 큰 값은 부산이죠? 그래서 부산을 키값으로 생각하게 됩니다.
상주의 코드번호를 입력해볼게요!
마지막 인수를 TRUE로 설정했어요!
그러면 방금 제가 말한대로 상주와 가장 유사한 값인 부산의 코드번호가 결과로 나오겠죠?
맞네요!!
위의 예제는 TRUE 인수를 쓰는 것보다 FALSE 인수를 쓰는 게 더 적절하지만
이렇게 TRUE인수를 써야할 때도 있어요!
TRUE 인수를 사용할 때는 키 값을 찾아주는 범위를 오름차순으로 정리하는 것이 가장 중요합니다!
② HLOOKUP 함수
그러면 이제 HLOOKUP 함수를 배워볼게요!
앞에서 VLOOKUP 함수를 이해했다면! (한 순간에 이해가 되는 함수는 아닙니다!
쉬운 예제부터 만들어서 연습해야해요!)
HLOOKUP 함수는 자매품?이라고 생각해도 좋은데요
HLOOKUP과 VLOOKUP의 단 한가지 차이는 키값을 범위를 수직으로 훑으며 찾아오냐 (Vertical)
아니면 수평으로 훑으며 찾아오냐 (Horizontal) 입니다!
위에서 VLOOKUP 함수를 훑을 때 사용했던 예를 그대로 가져와볼게요!
VLOOKUP 때 예제는 이랬죠!
HLOOKUP 함수를 쓰려면
대구의 코드번호를 찾으려고 하면
이렇게 수평선으로 훑으면서 찾아야하는 겁니다!
위의 표와 무슨 차이가 나는지 아시겠죠?
HLOOKUP 함수를 써볼게요!
이렇게요! 대구의 코드번호를 구할 때 달라지는 건 VLOOKUP이 아니라 HLOOKUP이라는 것과 표의 범위밖에 없죠?
VLOOKUP을 완전 이해하신 분이라면 HLOOKUP은 쉽게 이해가 될꺼에요
오늘은! 아주 아주 중요하고 처음에 접할 땐 약간은 어려운
VLOOKUP과 HLOOKUP 함수에 대해 살펴보았는데요!
저도 처음 이 함수를 공부할 땐 어려웠지만 확실히 알고나니
정말 여러군데서 쓰이더라구요!
꼭 시간을 가지고 연습해보시길 바라요!
