Składnia XLOOKUP

XLOOKUP to nowoczesny zamiennik funkcji VLOOKUP i HLOOKUP. Wyszukuje wartość w jednej kolumnie (lub wierszu) i zwraca pasującą wartość z innej.

Jego największą zaletą jest to, że wskazujesz osobno gdzie wyszukiwać i co zwrócić, podając dwa niezależne zakresy. Nie musisz liczyć kolumn ani martwić się, po której stronie znajduje się szukana odpowiedź.

Funkcja XLOOKUP jest dostępna w Excel 365, Excel 2021 i Arkuszach Google.

Najprostsza funkcja XLOOKUP wymaga tylko trzech informacji:

• lookup_value - czego szukasz
• lookup_array - zakres, w którym należy szukać
• return_array - zakres, z którego należy pobrać odpowiedź

Formułę można odczytać następująco: znajdź tę wartość w tej kolumnie i zwróć pasujący element z tej kolumny.

Wyobraź sobie listę produktów. Kolumna A zawiera nazwy produktów, a kolumna B — ceny. Chcesz znaleźć cenę produktu "Keyboard".

Wyszukujesz nazwę w zakresie A2:A20 i zwracasz cenę z zakresu B2:B20. XLOOKUP przeszukuje lookup_array, znajduje "Keyboard", a następnie zwraca wartość znajdującą się w tym samym wierszu return_array.

Wpisywanie wartości "Keyboard" bezpośrednio w formule jest podatne na błędy. Znacznie lepiej wskazać komórkę zawierającą szukaną wartość.

Jeśli użytkownik wpisze nazwę produktu do komórki D2, formuła automatycznie zareaguje na jej zmianę. Tak działają dynamiczne narzędzia wyszukiwania i pulpity nawigacyjne.

Zakres wyszukiwania i zakres zwracania powinny mieć taką samą wysokość (taką samą liczbę wierszy). XLOOKUP dopasowuje je wiersz po wierszu.

Jeśli A2:A20 ma 19 wierszy, zakres zwracania również musi mieć 19 wierszy. Niezgodność powoduje błąd #VALUE!, ponieważ XLOOKUP nie może prawidłowo połączyć wierszy.

Zakres zwracania nie musi znajdować się obok zakresu wyszukiwania. Załóżmy, że nazwy są w kolumnie A, ale chcesz znaleźć liczbę produktów w magazynie w kolumnie E.

Wystarczy wskazać jako zakres zwracania E2:E20. XLOOKUP nie przejmuje się przerwą między kolumnami, w przeciwieństwie do VLOOKUP, który liczy kolumny w prawo.

Jedną z największych zalet XLOOKUP jest domyślne wyszukiwanie dopasowania dokładnego.

W VLOOKUP domyślnie używane było dopasowanie przybliżone, które po pominięciu końcowego FALSE bez ostrzeżenia zwracało nieprawidłowe wyniki. XLOOKUP odwraca tę zasadę: otrzymujesz dopasowanie dokładne, chyba że celowo poprosisz o inne.

XLOOKUP obsługuje również dane poziome. Jeśli nazwy miesięcy znajdują się w wierszu 1, a wartości sprzedaży w wierszu 2, możesz przeszukać wiersz zamiast kolumny.

Obowiązuje ta sama struktura formuły: szukana wartość, wiersz z miesiącami i wiersz ze sprzedażą. Jedna funkcja zastępuje zarówno VLOOKUP, jak i HLOOKUP.

Ponieważ XLOOKUP zwraca pojedynczą wartość, możesz od razu użyć jej w obliczeniu.

Możesz na przykład pomnożyć wyszukaną cenę jednostkową przez ilość znajdującą się w komórce E2. Wyszukiwanie wykona się najpierw, zwróci cenę, a następnie mnożenie użyje tego wyniku. Dzięki temu arkusz pozostaje zwięzły i czytelny.

Możesz używać odwołań do całych kolumn, takich jak A:A i B:B, aby formuła nadal działała po dodaniu nowych wierszy.

Jest to wygodne, ale w bardzo dużych arkuszach może nieco spowalniać działanie. W przypadku uporządkowanych danych dobrym nawykiem jest wskazywanie dokładnego zakresu lub użycie tabeli programu Excel.

W porównaniu z VLOOKUP funkcja XLOOKUP jest:

• niezależna od kierunku - kolumny zwracania mogą znajdować się po lewej lub po prawej stronie zakresu wyszukiwania
• bezpieczniejsza - domyślnie używa dopasowania dokładnego
• stabilniejsza - wstawianie kolumn nie powoduje jej nieprawidłowego działania
• prostsza - nie wymaga liczenia indeksu kolumny

Gdy poznasz trzy podstawowe argumenty, większość codziennych wyszukiwań stanie się bezproblemowa.

Sprawdź, jak dobrze rozumiesz podstawową składnię XLOOKUP.

Poznałeś trzy podstawowe argumenty funkcji XLOOKUP:

• lookup_value - czego szukać
• lookup_array - gdzie szukać
• return_array - co zwrócić

Funkcja domyślnie używa dopasowania dokładnego, zakres zwracania może znajdować się w dowolnym miejscu, a wyszukiwanie działa zarówno w kolumnach, jak i wierszach. W następnej części dowiesz się, jak elegancko obsługiwać brakujące wartości.