Wyszukiwanie w lewo i od dołu

Jednym z największych ograniczeń funkcji VLOOKUP było to, że mogła zwracać wartości tylko na prawo od kolumny wyszukiwania. XLOOKUP nie ma takiego ograniczenia.

Ponieważ tablicę wyszukiwania i tablicę zwracaną podaje się osobno, wynik może znajdować się w dowolnym miejscu — po lewej, po prawej, powyżej lub poniżej przeszukiwanej kolumny.

Załóżmy, że identyfikatory znajdują się w kolumnie B, a nazwy w kolumnie A, czyli po lewej stronie. Funkcja VLOOKUP nie potrafiłaby tego zrobić bez zmiany kolejności kolumn.

W przypadku XLOOKUP wystarczy przeszukać kolumnę B i zwrócić kolumnę A. Funkcja bez problemu zwraca wartość znajdującą się po lewej stronie kolumny wyszukiwania.

Funkcja XLOOKUP ma opcjonalny piąty argument, search_mode, który określa kierunek wyszukiwania.

• 1 — wyszukiwanie od początku do końca (domyślnie)
• -1 — wyszukiwanie od końca do początku
• 2 — wyszukiwanie binarne w danych uporządkowanych rosnąco
• -2 — wyszukiwanie binarne w danych uporządkowanych malejąco

Domyślnie XLOOKUP wyszukuje z góry na dół i zwraca pierwsze napotkane dopasowanie.

Jeśli dana wartość występuje na liście kilka razy, ustawienie domyślne zwraca najwcześniejsze wystąpienie. Zwykle jest to właściwe rozwiązanie, ale czasami potrzebne jest zamiast tego najnowsze wystąpienie.

Ustawienie search_mode na -1 powoduje wyszukiwanie od dołu do góry. XLOOKUP zwraca wtedy ostatnią pasującą wartość.

Ponieważ if_not_found i match_mode występują przed search_mode, należy uwzględnić wcześniejsze argumenty jako symbole zastępcze, nawet jeśli pozostają puste.

Wyobraźmy sobie dziennik, w którym każdy wiersz zawiera aktualizację statusu zamówienia, a najstarsze wpisy znajdują się u góry.

Wyszukiwanie z góry na dół zwraca pierwszy status zamówienia. Wyszukiwanie od dołu z użyciem -1 zwraca jego najnowszy status. Te same dane mogą więc dać dwie zupełnie różne odpowiedzi, zależnie od kierunku wyszukiwania.

Argumenty są umieszczane pozycyjnie, dlatego nie można przejść od razu do szóstego argumentu bez podania wcześniejszych.

W formule =XLOOKUP(D2, A2:A20, B2:B20, "Not found", 0, -1) wartość "Not found" jest argumentem if_not_found, a 0 jest argumentem match_mode (dokładne dopasowanie). Zajmują one odpowiednie miejsca, dzięki czemu -1 trafia do argumentu search_mode.

Sąsiadujący z piątym argumentem match_mode określa sposób dopasowywania:

• 0 — dokładne dopasowanie (domyślnie)
• -1 — dokładne dopasowanie lub następna mniejsza wartość
• 1 — dokładne dopasowanie lub następna większa wartość
• 2 — dopasowanie symboli wieloznacznych z użyciem * i ?

W przypadku wyszukiwania w lewo i od dołu zazwyczaj pozostawia się wartość 0.

Arkusz zmian cen zawiera każdy produkt za każdym razem, gdy jego cena zostaje zaktualizowana. Aby uzyskać bieżącą cenę, należy znaleźć ostatni wpis dotyczący tego produktu.

Wyszukiwanie od dołu z użyciem -1 zwraca najnowszą cenę bez sortowania i dodatkowych kolumn pomocniczych.

Tryby wyszukiwania 2 i -2 korzystają z szybszego wyszukiwania binarnego, ale wymagają uporządkowania tablicy wyszukiwania — rosnąco dla 2 i malejąco dla -2.

W przypadku ogromnych, uporządkowanych list takie wyszukiwanie jest znacznie szybsze. Jeśli dane nie są rzeczywiście uporządkowane, wyszukiwanie binarne może zwrócić nieprawidłowe wyniki, dlatego należy używać go ostrożnie.

Mogą już Państwo kontrolować zarówno miejsce, jak i kierunek wyszukiwania XLOOKUP:

• Swobodne zwracanie wartości z lewej strony
• 1 oznacza pierwsze dopasowanie (domyślnie)
• -1 oznacza ostatnie dopasowanie
• 2 lub -2 oznacza szybkie wyszukiwanie binarne w uporządkowanych danych

Należy pamiętać o uzupełnieniu miejsc if_not_found i match_mode przed ustawieniem search_mode.

Sprawdź swoją znajomość argumentu search_mode.

Poznali Państwo możliwości XLOOKUP związane z kierunkiem wyszukiwania:

• Tablice zwracane mogą znajdować się po lewej stronie tablic wyszukiwania
• Argument search_mode (szósty argument) określa kierunek
• -1 wyszukuje ostatnie dopasowanie, co jest idealne przy wyszukiwaniu najnowszych rekordów
• 2 i -2 włączają szybkie wyszukiwanie binarne w uporządkowanych danych

W następnej części zwrócą Państwo całe wiersze lub kolumny za pomocą jednego XLOOKUP.