엑셀의 XLOOKUP: 정렬하지 않고 최신 값 반환하는 방법

이미 Excel에서 VLOOKUP에서 XLOOKUP으로 전환했다면, 더 나은 수식을 향한 첫 걸음을 내디딘 것입니다. 하지만 대부분의 사람들은 여전히 단순한 대체 기능으로만 사용하고 있으며, 그 강력한 기능 중 하나를 놓치고 있습니다.

덜 알려졌지만 매우 유용한 팁 중 하나는 검색 방향을 반대로 하는 것입니다. 작은 변경으로 XLOOKUP은 데이터를 아래쪽에서부터 시작하여 정렬이나 보조 열 없이 가장 최근의 일치 항목을 반환할 수 있습니다.

VLOOKUP이 가장 최근 기록을 찾지 못하는 이유

Excel의 상향식 검색 기본 설정과의 싸움

엑셀은 수십 년 동안 우리에게 위에서 아래로 사고하도록 훈련시켜 왔습니다. VLOOKUP이나 INDEX/MATCH 조합과 같은 클래식 함수를 사용하여 특정 항목을 찾을 때, 수식은 데이터의 첫 번째 행부터 스캔을 시작하고 일치 항목을 찾는 순간 멈춥니다. 만약 '사무용품'에 대한 20개의 항목이 1년에 걸쳐 분산되어 있다면, 이러한 오래된 방법은 항상 첫 번째 항목—지난 1월의 오래된 가격과 같은—을 반환하게 됩니다.

이를 해결하기 위해 많은 사람들이 보조 열을 사용하거나 조회할 때마다 데이터를 수동으로 내림차순 정렬합니다. 이는 워크북을 관리하는 파괴적인 방법이 될 수 있습니다. 소스 데이터를 계속해서 다시 정렬하면 다른 의존성이 깨지고, 피벗 테이블이 엉키며, 인적 오류의 위험이 증가할 수 있습니다. 기본 검색 수식의 한계를 충족시키기 위해 테이블의 물리적 레이아웃을 변경할 필요는 없습니다.

XLOOKUP로 검색 방향을 반대로 하기

하향식 결과를 위한 'search_mode' 인수 마스터하기

XLOOKUP은 검색 과정을 여섯 개의 별개의 인수로 나누기 때문에 이전 함수들보다 훨씬 더 유연합니다. 전체 구문은 다음과 같습니다:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

대부분의 경우, 첫 세 개의 인수만 필요합니다. 그러나 최신 항목을 찾는 핵심은 search_mode입니다. 기본적으로 Excel은 1 (첫 번째에서 마지막으로 검색)을 사용합니다. 이 동작을 반대로 하려면 -1 (마지막에서 첫 번째로 검색)을 사용하세요. 이는 Excel에게 데이터의 가장 하단에서 시작하여 위로 올라가도록 지시합니다. 이 방법은 정리되지 않은 로그에도 잘 작동합니다. 항목이 약간 순서가 뒤섞여 있어도, 마지막으로 일치하는 행이 일반적으로 가장 관련성이 높은 항목입니다.

실제 사례: 판매 기록에서 실시간 가격 가져오기

동적 업데이트를 위한 구조화된 표 참조 사용

도매 과일 가격을 추적한다고 상상해 보세요. 가격은 매일 변동하며, 새 견적을 스프레드시트 하단에 기록합니다. 셀 F2에서는 셀 E2에 입력된 항목의 최신 가격을 찾아보고 싶습니다.

공식을 작성하기 전에 데이터를 엑셀 표로 형식화하면 도움이 됩니다. 이렇게 하면 아래에 새 행을 추가할 때 XLOOKUP 공식이 셀 범위를 수동으로 업데이트하지 않아도 자동으로 새 정보를 ‘볼’ 수 있습니다.

1. 데이터 안쪽 아무 곳이나 클릭하세요.
2. 삽입 탭에서 표를 클릭합니다(또는 Ctrl+T를 누릅니다).
3. 내 테이블에 머리글이 있습니다가 선택되어 있는지 확인한 다음, 확인을 클릭하세요.
4. 테이블을 선택한 상태에서 테이블 디자인 탭을 열고, 테이블 이름 상자에 이름(예: T_Price_Log)을 입력한 후 Enter 키를 누릅니다.

이제 데이터가 정리되었으므로 F2 셀에서 XLOOKUP을 사용하여 가장 최근 가격을 가져올 수 있습니다. 필요한 수식은 다음과 같습니다:

=XLOOKUP($E$2, T_Price_Log[Item], T_Price_Log[Price], "Not found", 0, -1)

끝에 있는 -1은 Excel에게 조회를 반대로 수행하도록 지시하여 아래에서 위로 검색하게 합니다. 결과적으로 이 수식은 사과의 최신 가격이 $0.62임을 올바르게 알려줍니다.

구조화된 참조를 사용했기 때문에, 수식은 최신 입력값을 자동으로 반영합니다.

만약 VLOOKUP 또는 INDEX/MATCH를 사용했거나 search_mode 인수를 아예 생략했다면, 사과의 가장 오래된 가격($0.50, C2 셀)을 볼 수 있었을 것입니다.

추가 XLOOKUP 기능으로 작업 흐름 현대화

왜 XLOOKUP이 VLOOKUP의 궁극적인 대체 수단인지

검색 방향을 마스터하는 것은 훌륭한 첫 걸음이지만, XLOOKUP은 기존 도구보다 우수하게 만드는 여러 가지 다른 이점도 제공합니다. VLOOKUP과 달리 XLOOKUP은 오른쪽 열뿐만 아니라 어느 방향에서든 값을 반환할 수 있으며, 세로와 가로 모두에서 작동합니다.

유연성을 넘어서, XLOOKUP은 데이터 변경에 훨씬 더 강력합니다. 원본 테이블에 열을 삽입하거나 삭제하면 VLOOKUP은 종종 고정된 인덱스 번호에 의존하기 때문에 작동이 중단될 수 있습니다. XLOOKUP은 직접 범위 참조나 구조화된 테이블 이름을 사용하므로, 스프레드시트가 성장하고 발전하더라도 수식이 그대로 유지됩니다.

마지막으로, VLOOKUP은 종종 일치하는 항목이 없을 때를 처리하기 위해 IFERROR로 감싸야 하지만, XLOOKUP의 네 번째 인수를 사용하면 "찾을 수 없음" 값을 직접 수식에 입력할 수 있습니다. 즉, 중첩에 대해 걱정할 필요가 없고 논리를 훨씬 쉽게 따라갈 수 있습니다.

XLOOKUP 전문 지식을 향상시키세요

이 방향성 트릭을 완전히 익힌 후에는 XLOOKUP을 여러 조건과 함께 사용하는 법을 배우거나 양방향 조회를 만들어 더 복잡한 데이터 관계를 관리하는 한 단계 더 나아가세요. 금세 스프레드시트가 제대로 작동하지 않을 때 다른 사람들이 찾는 사람이 되어 있을 것입니다.