왼쪽 및 아래쪽부터 검색하기

VLOOKUP의 가장 큰 제약 중 하나는 조회 열의 오른쪽에 있는 값만 반환할 수 있었다는 점입니다. XLOOKUP에는 이런 규칙이 없습니다.

조회 배열과 반환 배열을 따로 지정하므로 결과는 검색하는 열의 왼쪽, 오른쪽, 위쪽 또는 아래쪽 어디에나 있을 수 있습니다.

ID가 B열에 있고 이름이 왼쪽인 A열에 있다고 가정해 보겠습니다. VLOOKUP에서는 열을 재배치하지 않고는 이 작업을 할 수 없습니다.

XLOOKUP에서는 B열을 검색하고 A열을 반환하기만 하면 됩니다. 조회 열의 왼쪽에 있는 값도 문제없이 반환합니다.

XLOOKUP에는 검색 방향을 제어하는 선택 사항인 다섯 번째 인수 search_mode가 있습니다.

• 1 - 처음부터 끝까지 검색(기본값)
• -1 - 끝에서 처음까지 검색
• 2 - 오름차순 데이터에서 이진 검색
• -2 - 내림차순 데이터에서 이진 검색

기본적으로 XLOOKUP은 위에서 아래로 검색하며, 찾은 첫 번째 일치 항목을 반환합니다.

목록에 같은 값이 여러 번 나타나면 이 기본 설정은 가장 먼저 나온 항목을 반환합니다. 대부분의 경우에는 충분하지만, 때로는 가장 최근 항목을 원할 수 있습니다.

검색 방식을 -1로 설정하면 아래에서 위로 검색합니다. 그러면 XLOOKUP은 마지막으로 일치하는 값을 반환합니다.

찾지 못한 경우의 값과 일치 방식이 검색 방식보다 앞에 있으므로, 비어 있더라도 앞선 인수를 자리 표시자로 포함해야 합니다.

각 행에 주문 상태 변경을 기록하고 가장 오래된 기록이 위에 있는 기록표를 생각해 보세요.

위에서 아래로 검색하면 주문의 최초 상태가 나옵니다. -1을 사용해 아래에서 위로 검색하면 최신 상태가 나옵니다. 같은 데이터라도 방향에 따라 결과가 크게 달라집니다.

인수는 위치로 정해지므로 앞선 인수를 지정하지 않고 여섯 번째 인수로 건너뛸 수 없습니다.

=XLOOKUP(D2, A2:A20, B2:B20, "Not found", 0, -1)에서 "Not found"는 찾지 못한 경우의 값이고 0은 일치 방식(정확히 일치)입니다. 이 값들이 자리를 채워 -1이 검색 방식 위치에 오게 합니다.

다섯 번째 인수와 함께 사용되는 일치 방식은 일치 작업의 방식을 제어합니다.

• 0 - 정확히 일치(기본값)
• -1 - 정확히 일치하거나 다음으로 작은 값
• 1 - 정확히 일치하거나 다음으로 큰 값
• 2 - * 및 ?를 사용하는 와일드카드 일치

왼쪽 및 아래쪽 검색에서는 보통 이 값을 0으로 유지합니다.

가격 변경 기록표에 제품별 가격 업데이트 내역이 기록되어 있다고 가정해 보겠습니다. 현재 가격을 얻으려면 해당 제품의 마지막 기록이 필요합니다.

-1을 사용해 아래에서부터 검색하면 정렬하거나 추가 보조 열을 만들지 않고도 가장 최근 가격을 반환합니다.

2와 -2 검색 방식은 더 빠른 이진 검색을 사용하지만, 조회 배열이 정렬되어 있어야 합니다. 2에서는 오름차순, -2에서는 내림차순이어야 합니다.

정렬된 대규모 목록에서는 훨씬 빠릅니다. 데이터가 실제로 정렬되어 있지 않으면 이진 검색이 잘못된 결과를 반환할 수 있으므로 주의해서 사용하세요.

이제 XLOOKUP이 어디를 어떤 방향으로 검색할지 모두 제어할 수 있습니다.

• 왼쪽에 있는 값도 자유롭게 반환
• 1은 첫 번째 일치 항목(기본값)
• -1은 마지막 일치 항목
• 2 또는 -2는 정렬된 데이터에서 빠른 이진 검색

검색 방식보다 앞에 있는 찾지 못한 경우의 값과 일치 방식 자리를 채워야 한다는 점을 기억하세요.

검색 방식 인수에 대한 이해도를 확인해 보세요.

XLOOKUP의 방향 제어 기능을 배웠습니다.

• 반환 배열은 조회 배열의 왼쪽에 있을 수 있습니다
• 검색 방식(6번째 인수)으로 방향을 설정합니다
• -1은 마지막 일치 항목을 찾아 최신 기록 조회에 적합합니다
• 2와 -2는 정렬된 데이터에서 빠른 이진 검색을 사용하게 합니다

다음에는 하나의 XLOOKUP으로 전체 행이나 열을 반환합니다.