Od 2010 r. prowadzę blog SkuteczneRaporty.pl poświęcony wizualizacji danych, dashboardom i nowościom w programie Excel. Od czasu do czasu pojawiają się tutaj inne zagadnienie i tak artykuł o WYSZUKAJ.PIONOWO do dziś jest na tej stronie najczęściej odwiedzanym miejscem. Dlatego nie sposób przejść obojętnie obok nowych formuł w Excelu 365, które są już publicznie dostępne: X.WYSZUKAJ i X.DOPASUJ. Czy to prawdziwa rewolucja po 30 latach VLOOKUP-u?

Krótka historia WYSZUKAJ.PIONOWO

W 1979 r. w poprzedniku Excela – programie VisiCalc – pojawiła się formuła LOOKUP, która w latach 80. stała się częścią Excela. Na kombinacji WYSZUKAJ.PIONOWO i tabelach przestawnych funkcjonuje nadal większość spotkanych przeze mnie w firmach raportów Excel. I tak w 2020 r. do szerokiego grona użytkowników trafia jej następczyni – formuła X.WYSZUKAJ (XLOOKUP w angielskiej wersji Excela).

Co jeśli nie mam jeszcze formuły X.WYSZUKAJ?

Jeśli masz Office 365, a formuła nie jest jeszcze dostępna, musisz poczekać na aktualizację. Na początek była ona dostępna dla uczestników programu Insider, teraz jest już dostępna standardowo, ale niektóre organizacje stosują opóźnioną, 4-miesięczną metodę aktualizacji. Jest to jedno z ustawień w panelu administracyjnym Office 365.

Ograniczenia WYSZUKAJ.PIONOWO

Większość użytkowników korzysta z WYSZUKAJ.PIONOWO z ostatnim argumentem jako 0 lub FAŁSZ. Mamy także świadomość, że sama formuła ma ograniczenia. Oto niektóre z nich:

Wyszukuje tylko w prawo (chyba, że ktoś zastępuje ją kombinacją formuł INDEKS i PODAJ.POZYCJĘ)
Znajduje zawsze pierwszy element od góry
Zwraca błąd przy niepowodzeniu
Przy dużych plikach bywa wolna (choć ponoć w Excelu 365 miało się to poprawić)

Jak działa X.WYSZUKAJ vs WYSZUKAJ.PIONOWO?

Składnia obu formuł jest bardzo podobna, jednak nowa formuła ma więcej opcji (6 argumentów vs 4).

Różnica 1: Jeśli pominiemy argument typ wyszukiwania, wyszukiwanie jest dokładne, czyli domyślnie mamy 0.

Różnica 2: Oddzielnie podajemy szukaną tablicą i zwracaną tablicę.

Dzięki temu można wyszukiwać w lewo, a także w poziomie. Poprzednio trzeba było stosować kombinację formuł INDEKS i PODAJ.POZYCJĘ.

Różnica 3: Można podać wartość w przypadku błędu

Odpowiada za to czwarty argument X.WYSZUKAJ. Pomijając ten argument nadal uzyskujemy błąd #N/D. Poprzednio potrzebowaliśmy dodatkowej formuły JEŻELI.BŁĄD.

Różnica 4: W przypadku braku znalezienia dokładnej wartości, możemy znaleźć komórkę niżej.

Przy zwykłym zastosowaniu WYSZUKAJ.PIONOWO można znaleźć tylko komórkę wyżej, posługując się ostatnim argumentem jako 1 lub pomijając ten argument. Dane nadal muszą być posortowane. Z nową formułą mamy 3 opcje: 0 – dokładne, -1 – idź w górę, 1 – idź w dół.

Różnica 5: Jasno widać, że możemy wyszukiwać w sposób niedokładny.

W Excelu takie zachowanie WYSZUKAJ.PIONOWO było mocno schowane i niewielu użytkowników Excela o tym wie. Ja sam nie wiedziałem, dopóki po pierwotnej publikacji artykułu nie napisał do mnie czytelnik Krzysztof (dzięki!). Zachowanie takie więcej osób zna dla okienka wyszukiwania (Ctrl + F), gdzie możemy wpisywać symbole:

? – jeden znak

* – wiele znaków

~ – ?, * lub ~ (jeśli właśnie te 3 symbole miałyby być częścią tekstu)

Różnica 6: X.WYSZUKAJ jest formuła tablicową.

Oznacza to, że możemy podać więcej niż 1 komórkę do zwrócenia, a wynik rozleje się na kolejne kolumny. W poniższym przykładzie, stojąc na komórce B2, zaznaczyłem, że chcę uzyskać wyniki z 2 kolumn – G i H – i jedno kliknięcie Enter rozlało formułę na komórkę obok – C2.

Różnica 7: Sposób wyszukiwania pozwala na zmiany.

Ostatni argument X.WYSZUKAJ (nieobowiązkowy) pozwala nam sterować kierunkiem wyszukiwania.

Dostępne opcje:

1 – od początku do końca (domyślna)

-1 – od końca do początku

2 i -2 – wykorzystują szybszy mechanizm wyszukiwania binarnego, ale dane muszą być posortowane

Różnica 8: Wyszukiwanie jednocześnie w pionie i w poziomie.

W Excelu funkcjonuje także formuła WYSZUKAJ.POZIOMO, która także może zostać zastąpiona X.WYSZUKAJ. Działanie formuły X.WYSZUKAJ przypomina trochę w działaniu formułę INDEKS.

Różnica 9: Możliwość tworzenia warunków logicznych.

Ponieważ X.WYSZUKAJ działa jako formuła tablicowa, możemy testować, czy spełnione są warunki i zwracać pierwszy znaleziony element, który je spełnił.

Wady wykorzystywania nowej formuły X.WYSZUKAJ

Nie wszyscy mają Excel 365
Niewielu użytkowników będzie znało tę formułę

Zalety X.WYSZUKAJ

Na co dzień wolę korzystać ze scalania w Power Query, ale widzę 5 powodów, dla których będę z tej formuły korzystał w scenariuszach Excelowych:

Domyślne wyszukiwanie jest precyzyjne
Jedna formuła do wszystkiego
Wyszukiwanie fragmentu tekstu, które, choć było dostępne przy WYSZUKAJ.PIONOWO, dopiero teraz stało się dla mnie widoczne
Wyszukiwanie od końca
Wyszukiwanie wielu informacji na raz poprzez działanie formuły tablicowej (jedno kliknięcie Enter)

X.DOPASUJ

X.WYSZUKAJ to nie jedyna formuła wyszukująca. Obok PODAJ.POZYCJĘ (w angielskiej wersji MATCH) mamy od teraz formułę X.PODAJ.POZYCJĘ, ale przetłumaczoną jako X.DOPASUJ (XMATCH). I bądź tu człowieku mądry. Jej argumenty są bardzo podobne i po przeczytaniu artykułu łatwo możesz się w niej odnaleźć.