마지막으로 일치하는 값 조회하기

대부분의 조회는 찾은 첫 번째 일치 항목을 반환합니다. 하지만 제품의 마지막 가격, 가장 최근 상태 업데이트 또는 고객의 최종 기록처럼 마지막 항목을 원할 때도 있습니다.

목록이 시간에 따라 늘어나고 같은 키가 여러 번 나타나는 경우, 가장 아래쪽 행이 대개 가장 최신 기록입니다. 일반적인 VLOOKUP이나 정확히 일치하는 MATCH는 그 대신 고집스럽게 맨 위의 행을 가져옵니다.

이 레슨에서는 마지막으로 일치하는 값을 가져오는 신뢰할 수 있는 여러 방법을 살펴봅니다.

MATCH(value, range, 0)은 위에서 아래로 검색하다가 정확히 일치하는 항목을 처음 발견하는 즉시 멈춥니다. "Apple"이 2행, 5행, 9행에 나타나면 MATCH는 2를 반환합니다.

키가 고유할 때는 완벽하지만, 더 최근에 추가된 행은 무시합니다. 마지막 항목에 도달하려면 아래에서 위로 검색하거나 마지막 일치 항목의 위치를 반환하는 기법이 필요합니다.

최신 버전의 Excel 또는 Google 스프레드시트를 사용한다면 XLOOKUP으로 쉽게 처리할 수 있습니다. 다섯 번째와 여섯 번째 인수가 일치 모드와 검색 방향을 제어합니다.

검색 모드 인수로 -1을 전달하면 마지막 항목부터 첫 번째 항목까지 검색합니다. 그러면 XLOOKUP은 가장 아래쪽에서 일치하는 키에 연결된 값을 반환합니다.

이 예에서는 G1의 제품을 A2:A10에서 찾고, 검색을 아래쪽에서 시작하여 B2:B10에서 일치하는 가격을 반환합니다.

오래된 스프레드시트에서는 LOOKUP과 숫자 2, 그리고 조건을 이용한 나눗셈을 조합하는 잘 알려진 기법을 사용할 수 있습니다.

1/(A2:A10=G1)이라는 식은 일치하는 행에는 1을, 일치하지 않는 행에는 나누기 오류를 만듭니다. LOOKUP은 2(현재 존재하는 어떤 값보다 큰 값)를 검색하면서 오류를 지나쳐 유효한 1 중 마지막 항목에 도달하고, B2:B10에서 일치하는 값을 반환합니다.

1/(A2:A10=G1)을 단계별로 살펴보겠습니다.

• 키가 일치하는 행에서는 1/TRUE = 1이 됩니다.
• 일치하지 않는 행에서는 1/FALSE = #DIV/0! 오류가 됩니다.

LOOKUP은 오류를 무시하며, 대상 값인 2를 찾지 못하면 오류가 아닌 항목 중 마지막 항목에 대응하는 결과를 반환합니다. 모든 일치 항목이 1이므로 마지막 1이 선택되고, 그 결과 마지막으로 일치하는 행의 값을 얻게 됩니다.

INDEX-MATCH 계열의 수식만으로도 처리할 수 있습니다. 핵심은 마지막 일치 항목의 위치를 찾은 다음 그 위치를 INDEX에 전달하는 것입니다.

같은 나눗셈 기법을 MATCH 안에서 사용하여 1/(A2:A10=G1)에서 2를 검색하면 마지막 일치 항목의 행 위치를 얻을 수 있습니다. 그 위치를 반환 열에 적용한 INDEX에 전달하세요.

MATCH의 세 번째 인수를 생략하면 기본값은 1이며, 이는 오름차순 데이터에서 근사 일치를 의미합니다. 그러면 MATCH는 2보다 작거나 같은 값 중 가장 큰 값을 찾습니다.

1/(A2:A10=G1) 배열에는 1과 오류만 들어 있습니다. 2 이하에서 가장 큰 값은 1이므로 MATCH는 그러한 1 중 마지막 항목의 위치를 반환합니다. 그 위치가 바로 마지막으로 일치하는 행입니다.

A2:A10에 시간순으로 기록된 "Order-7"의 주문 상태가 있고, B2:B10에 상태 텍스트가 있다고 가정해 보겠습니다. "Order-7"은 3행, 6행, 9행에 나타납니다.

• 일치 배열은 3행, 6행, 9행에 1을 표시하고 나머지는 오류로 표시합니다.
• MATCH(2, ...)는 범위의 시작부터 세어 마지막 일치 항목의 위치인 9를 반환합니다.
• INDEX는 해당 마지막 행의 상태, 즉 가장 최근 상태를 반환합니다.

어떤 방법을 사용해야 할까요?

• XLOOKUP with -1: 사용 중인 앱에서 지원한다면 가장 깔끔하고 읽기 쉽습니다.
• LOOKUP(2, 1/...): 특정 버전이 필요하지 않아 거의 어디서나 작동합니다.
• INDEX-MATCH(2, 1/...): 위치도 필요하거나 다른 열에서 값을 반환하려 할 때 유용합니다.

세 방법 모두 같은 결과를 제공하므로, 사용 중인 도구와 수식을 얼마나 읽기 쉽게 만들고 싶은지에 따라 선택하세요.

다음 문제에 주의하세요.

• 서로 다른 범위 크기: 조건 범위와 반환 범위의 높이가 같아야 행이 어긋나지 않습니다.
• 숨겨진 중복: 뒤에 붙은 공백 때문에 "Apple "이 "Apple"과 달라질 수 있습니다. 먼저 TRIM으로 텍스트를 정리하세요.
• 일치 항목이 전혀 없음: 일치하는 항목이 없으면 이 기법은 오류를 반환합니다. IFERROR로 감싸 알기 쉬운 대체 결과를 지정하세요.

마지막 일치 항목을 찾는 기법을 두 가지 조건과 결합할 수 있습니다. 나눗셈 안에서 조건 테스트를 곱하면 두 키를 모두 만족하는 행만 1을 생성합니다.

예를 들어 제품이 G1과 같고 동시에 지역이 G2와 같은 경우의 가장 최근 가격을 찾아 보겠습니다. LOOKUP(2, ...) 기법은 여전히 마지막으로 조건을 충족하는 행에 도달합니다.

같은 제품이 여러 지역에 나타나는 시간 기록 로그에서 특히 유용합니다.

마지막 일치 항목 조회에 대한 이해도를 확인해 보세요.

첫 번째 일치 값 대신 마지막 일치 값을 반환하려면 다음 방법을 사용합니다.

• 가능한 경우 XLOOKUP(..., -1)을 사용하여 아래에서 위로 검색합니다.
• 모든 버전에서 고전적인 LOOKUP(2, 1/(range=key), result) 기법을 사용합니다.
• 위치도 필요할 때는 INDEX(result, MATCH(2, 1/(range=key)))를 사용합니다.

범위의 크기를 같게 유지하고, 불필요한 공백을 정리하며, 안전을 위해 수식을 IFERROR로 감싸는 것을 잊지 마세요.