엑셀 조건을 만족하는 가장 마지막 값 찾기 (LOOKUP, VLOOKUP)

 

VLOOKUP 함수에 익숙하신 분들이라면 배열에서 값을 검색하면 일치하는 것 중 가장 상단의 값을 반환하는 것을 알고 계실 것입니다. 때문에 동일한 레코드가 여러 개라도 수식의 결과는 항상 최상단의 값이 나타나게 됩니다. 하지만 가장 마지막 방문 기록이나 입고 내용 같이 최하단에 위치한 값을 얻어야 할 때가 있습니다. 이번 글에서는 이럴 때 사용할 수 있는 방법에 대하여 알아보고자 합니다.

 

위의 예시는 제품들의 입고 수량을 일자 순으로 나열해 두고 있습니다. 여기서 입고 일자를 수식에 활용하지 않는다는 가정하에 가장 마지막에 입고된 마우스의 수량을 얻으려면 어떤 수식을 사용해야 할까요? 흔히 사용하는 VLOOKUP 수식을 쓰면 제일 상단에 위치한 7번 행의 마우스 입고 수량인 '2'가 나오게 될 것입니다. 아래는 여기에 대한 해답 두 가지가 있습니다.

 

 

 

LOOKUP을 활용하는 방법

 

다음 LOOKUP 함수를 활용하면 일치하는 최하단의 값을 구할 수 있습니다.

 

# =LOOKUP(2,1/(lookup_vector=lookup_value),result_vector)
예) =LOOKUP(2,1/($B$2:$B$16="마우스"),$D$2:$D$16)

 

이때 lookup_vector 와 result_vector는 반드시 같은 크기여야 합니다. 아래에는 이 수식이 어떤 식으로 동작하는지 알아보았는데, 내용이 조금 난해하기 때문에 복잡한 것을 싫어하시는 분이라면 위의 수식만 그대로 활용하시면 될 것 같습니다. 아니면 이다음에 설명할 방법을 활용하셔도 동일한 결과를 얻을 수 있습니다.

 

 

수식 동작

'수식 - 수식 계산' 을 확인해 보면 수식이 어떤 식으로 연산되는지 확인할 수 있습니다. 수식이 진행되는 단계를 밑줄로 표시하고 있어 이 부분을 따라가면 진행되는 단계를 파악하기 용이합니다.

 

 

우선 lookup_vector 와 lookup_value의 일치 여부를 판별해 TRUE와 FALSE로 구분 지어집니다.

 

 

이 값들이 분모가 되어 1/1 또는 1/0(#DIV/0!, 0으로 나누기 오류)로 구성된 배열이 만들어지게 됩니다.

 

 

이 배열들은 첫 번째 인수인 2와 대조되게 되며, 일치하는 값이 없기 때문에 2와 가장 유사한 값인 1을 가지는 것 중 가장 마지막 위치를 result_vector에 대입한 결과가 나타나게 됩니다.

 

 

여기에서 궁금한 점이 생겼습니다. 첫 번째 인수에 찾으려는 1이 아닌 2가 들어가는 이유, 기본적인 lookup 식형을 그대로 쓰지 않는 이유 "=LOOKUP(lookup_value, lookup_vector, result_vector)" 궁금하지 말았어야 했는데... 이걸로 많은 시간을 허비하고 내린 결론은 데이터에 따라 이 방식들은 동일한 결과가 나올 수도 있고 그렇지 않을 수도 있다는 것을 알게 되었습니다.

 

삽질

 

사진은 네 개지만 이것보다 훨씬 많은 시도를 했습니다. 여러 데이터에 대해서 lookup 기본 식형을 사용한 방법과 본문에 소개드린 수식, 그리고 첫 번째 인수를 1로 바꾸어서 시도해본 결과 여러 수식에서 같은 값이 나올 때도 또한 그렇지 않을 때도 있었습니다. 

 

관련해 해외 포럼들을 확인해 보았고 LOOKUP 함수는 기본적으로 '오름차순'으로 정렬된 벡터에서 정상적으로 동작할 수 있는 수식이며 그 이유가 '이진 검색(binary search)' 방식을 취하고 있다는 내용을 접하게 되었습니다. 이진 검색과 비교되는 방식이 '순차 검색(sequential search)'으로 제가 알게 된 내용을 표현해 보았습니다.

 

 

순차 검색은 배열의 처음부터 차례로 찾으려는 값과 일치 여부를 판단하지만 이진 검색은 처음이 아닌 중간값과 찾으려는 값의 대소를 판단하고 그것에 따라 추가적인 검색의 방향성을 찾는 것을 반복합니다. 그렇기 때문에 순차 검색은 데이터의 정렬 여부가 관계없으나 이진 검색은 전제 자체가 정렬된 데이터에 대한 검색방식이 되는 것이죠.

 

그렇기 때문에 LOOKUP 기본 식형을 그대로 활용하려면 데이터를 오름차순으로 우선 정렬해야 올바른 값이 나오게 되며 그렇지 않을 경우엔 원하는 값이 나올 수도, 그렇지 않을 수도 있게 됩니다.

 

1이 아닌 2를 첫 번째 인수로 사용하는 것 또한 이진 검색과 연관이 있는데 전체를 검색하는 것이 아니라 일부일부를 슬라이스 해서 검색하기 때문에 일치하는 값의 위치에 따라 의도치 않은 결과가 나올 수 있게 됩니다. 그래서 찾으려는 값보다 큰 값을 지정해 전체 벡터의 최하단으로 이동시키는 것이라고 생각하시면 될 것 같습니다. 그래서 사실 첫 번째 인수에 2 뿐만 아니라 1보다 큰 수인 1.1, 15, 6 등의 수가 와도 동일한 결과가 나타나게 됩니다.

 

물론 위의 내용에서 해결되지 못한 석연찮은 부분이 남아있기는 하지만 LOOKUP의 상세한 로직을 알 수 없었기 때문에 기본적으로 '오름차순'으로 정렬된 데이터를 검색하도록 고안된 함수라는 점을 이해하고 넘어가기로 했습니다. LOOKUP 함수에 대해 좀 더 자세히 알고 싶으시다면 아래의 Microsoft 공식문서 링크를 확인해 보실 것을 추천드립니다.

 

LOOKUP 함수 - Office 지원 (microsoft.com)

 

 

 

VLOOKUP을 활용하는 방법

 

위의 수식이 어렵다면 익숙한 VLOOKUP을 이용하는 방법도 있습니다. 다만 이 방식은 데이터 테이블을 역순으로 뒤집는 단계가 필요해 원본을 수정하기 어려운 경우 별도 시트로 데이터를 복사해야 할 수도 있습니다.

 

 

데이터를 역순으로 뒤집기 위해 우선 'No' 열을 추가하여 각 행의 번호를 붙여줍니다. 이때 추가되는 열의 위치는 상관없으며 기존에 사용하시던 인덱스가 있다면 그것을 활용해도 됩니다.

 

 

상단 메뉴의 '정렬 및 필터 - 사용자 지정 정렬' 항목을 차례로 클릭합니다. 

 

 

'정렬' 창이 뜨면 정렬 기준에 'No' 정렬 방식은 '내림차순'으로 지정하고 확인 버튼을 누릅니다.

 

 

No 열을 확인해 보시면 데이터가 역순으로 정렬된 것을 확인하실 수 있습니다. 이 상태에서 VLOOKUP 함수를 사용해 필요한 값을 찾으면 됩니다. 이후 필요에 따라 데이터 테이블을 오름차순으로 재 정렬해 원상복구 하시면 됩니다. 감사합니다.