엑셀에 행 또는 열에 데이터가 채워져 있는데 중간에 듬성듬성 있는 경우가 있습니다. 또는 어떤 건 3번째 칸까지 채워져 있고 어떤 건 5번째 칸 까지 채워져 있고 제각각일 때, 각 행 또는 열에서 가장 마지막에 데이터가 채워져 있는 값을 불러오는 방법 알아보겠습니다.
LOOKUP 함수
오늘 사용할 엑셀 함수는 LOOKUP 인데요. 어떤 식으로 동작하는지 간단히 말씀드리겠습니다. LOOKUP은 ‘배열이나 한 행 또는 한 열에서 값을 찾는다’ 라고 설명에 씌여 있습니다.
함수에 사용하는 인수는 다음과 같습니다.
- Lookup_value : 찾을 값입니다. 지정한 검색 범위에서 해당하는 값을 찾습니다.
- Lookup_vector : 찾을 값을 검색할 범위입니다. 오늘 말씀드리는 기능을 이용하기 위해선 일반적으로 한 개의 열 또는 행을 지정합니다.
- Result_vector : 출력 범위를 뜻합니다. 찾을 값이 있는 순서를 찾은 뒤 출력 범위에서 동일한 순서에 있는 값을 찾습니다.
LOOKUP 함수 활용법은 보통은 A열에서 이름을 찾은 뒤 그 옆에 B열에 있는 데이터를 가져오라고 할 때 사용하는데요. 이걸 응용해서 채워진 마지막 데이터를 가져오는 방법 알아보겠습니다.
마지막 데이터 찾기
다음과 같이 연도별 데이터가 있는데 이빨 빠진 형태라고 가정해 보겠습니다.
중간중간 무작위로 채워진 데이터 시트입니다. 앞부분이 없기도 하고 뒷부분이 없기도 합니다. 여기서 노란색으로 하이라이트 표시한 제일 오른쪽 데이터만 불러오는 것이 목적입니다.
만약 빈 칸에 – 같은 기호들로 채워진 데이터 형태라면, 바꾸기 (Ctrl+H) 메뉴에서 찾을 내용에 – , 바꿀 내용에 아무것도 없는 상태로 모두 바꾸기를 해서 전부 빈칸으로 만들어주시면 됩니다.
그 다음 우측에 다음과 같이 엑셀 함수를 입력합니다.
=LOOKUP(1,1/(B2:F2<>""),B2:F2)<>”” 이 부등호 연속 입력의 의미는 같지 않을 시 TRUE 를 반환한다는 뜻입니다.
옆에 “” 이건 아무것도 없는 빈칸을 뜻하므로, 위 예시에서 B2:F2 범위의 셀 중에 빈 칸일 경우 0 (FALSE), 데이터가 무언가 있을 경우 1 (TRUE)를 반환합니다.
즉 이렇게 하면 각 칸별로 데이터가 있으면 1, 없으면 0인 새로운 배열이 하나 생기게 됩니다.
이걸 1에서 나눠주는데요. 만약 데이터가 있으면 1/1=1이니까 그대로 1이 표시되고, 데이터가 없으면 1/0=#DIV/0! 오류로 반환됩니다. 데이터가 {없고 있고 있고 있고 없고} 이런 형태라면 {#DIV/0!, 1, 1, 1, #DIV/0!} 이런 배열을 하나 만들게 됩니다.
그 다음에 가장 오른쪽에 있는 1을 LOOKUP 함수로 찾아주는 것인데요. LOOKUP 첫번째 인수에 1을 입력했는데, 이렇게 하면 검색 범위에서 끝쪽부터 올라오면서 첫번째 1을 찾습니다.
즉 우리가 원하는 마지막 데이터가 있는 위치입니다. (LOOKUP 함수는 VLOOKUP, HLOOKUP과 달리 첫번째부터 탐색하는게 아니라 마지막부터 탐색합니다.) 그 위치 값을 얻은 뒤 다시 3번째 인수인 출력범위에서 동일한 위치에 해당하는 값을 최종적으로 가져오게 되는 것입니다.
이렇게 0으로 나눌 시 오류가 발생하는 점을 응용한 센스있는 함수 이용법이라고 할 수 있습니다.
(엑셀 하실 때 많은 함수와 차트 사용법을 아는 것도 중요하지만 간단한 기능을 센스있게 응용하는 것이 더 중요합니다. 마치 외국어 공부할 때처럼요. 쉬운 말로 필요한 상황에 잘 사용하는 게 실력이죠!)
이 함수로 모든 칸을 적용하면 이렇게 가장 오른쪽에 있는 값만 뽑아서 가져오는 것을 알 수 있습니다.
혹시나 제대로 동작하지 않고 오류가 난다 하시면 LOOKUP 함수의 첫번째 인수를 1이 아닌 2 이상의 보다 큰 숫자로 바꿔보시기 바랍니다.
