Obsługa braku dopasowania za pomocą if_not_found

Co się dzieje, gdy XLOOKUP nie może znaleźć szukanej wartości? Domyślnie zwraca błąd #N/A.

Ten błąd jest technicznie poprawny, ale w raporcie wygląda nieestetycznie i może zakłócić działanie każdej formuły korzystającej z tego wyniku. XLOOKUP oferuje wbudowany i przejrzysty sposób obsługi takiej sytuacji.

XLOOKUP ma opcjonalny czwarty argument o nazwie if_not_found. Zwracana jest jego zawartość, gdy nie istnieje dopasowanie.

To istotne ulepszenie w porównaniu z VLOOKUP, który trzeba było opakowywać w IFERROR. W XLOOKUP wartość zastępcza jest częścią tej samej funkcji.

Najczęściej używa się tego do wyświetlania czytelnego tekstu zamiast #N/A.

Jeśli produkt w komórce D2 nie znajduje się na liście, komórka wyświetla komunikat „Nie znaleziono” zamiast kodu błędu. Każda osoba czytająca arkusz od razu rozumie, co się stało.

Czasami liczba jest bardziej użyteczna niż tekst, zwłaszcza gdy wynik jest używany w obliczeniach.

W przypadku braku dopasowania można zwrócić 0, dzięki czemu późniejsze sumowanie lub mnożenie nadal działa bez generowania własnego błędu.

Aby komórka wyglądała na pustą, gdy nic nie zostanie dopasowane, należy zwrócić pusty ciąg tekstowy zapisany za pomocą dwóch cudzysłowów.

Jest to przydatne w przejrzystych pulpitach, w których puste komórki są lepsze niż tekst zastępczy. Należy pamiętać, że komórka nie jest naprawdę pusta — zawiera pusty ciąg tekstowy. Ma to znaczenie, jeśli inne formuły sprawdzają, czy komórki są puste.

Załóżmy, że osoba obsługująca sprzedaż wpisuje identyfikator zamówienia do komórki D2, aby wyszukać nazwę klienta. Identyfikatory zamówień znajdują się w kolumnie A, a nazwy w kolumnie C.

Jeśli identyfikator zostanie wpisany błędnie, formuła zwróci jasną instrukcję zamiast niezrozumiałego błędu. Komunikat zastępczy podpowie, jak poprawić wprowadzone dane.

Wartość if_not_found nie musi być wpisanym tekstem. Może wskazywać inną komórkę.

Można na przykład przechowywać domyślny region w komórce G1 i zwracać go za każdym razem, gdy wyszukiwanie konkretnej wartości zakończy się niepowodzeniem. Dzięki temu wartość zastępczą można edytować bez zmieniania formuły.

Nadal można opakować XLOOKUP w IFERROR, ale istnieje między nimi subtelna różnica.

• if_not_found obsługuje tylko przypadek braku dopasowania
• IFERROR ukrywa każdy błąd, także taki, który wynika z rzeczywistego błędu w formule

Użycie if_not_found jest bezpieczniejsze, ponieważ prawdziwe błędy pozostają widoczne i nie są maskowane.

Przydatny trik polega na ustawieniu jako wartości zastępczej jednego XLOOKUP drugiego XLOOKUP.

Najpierw należy przeszukać główną listę, a jeśli nie ma na niej szukanego elementu, przeszukać listę zapasową. Jest to znacznie przejrzystsze niż zagnieżdżone funkcje IFERROR, a całość niemal przypomina zwykłe zdanie.

Gdy wynik wyszukiwania jest używany w obliczeniach, liczbowa wartość zastępcza pozwala zachować prawidłowe działanie całości.

W tym przypadku brakująca cena zwraca 0, więc mnożenie przez liczbę sztuk w komórce E2 nadal daje liczbę zamiast błędu rozprzestrzeniającego się po arkuszu.

Wartość zastępczą należy dopasować do przeznaczenia komórki:

• Tekst, taki jak „Nie znaleziono”, w raportach przeznaczonych do czytania przez ludzi
• 0, gdy wartość będzie później dodawana lub mnożona
• Pusty ciąg tekstowy w przejrzystych układach
• Inne wyszukiwanie w przypadku danych pochodzących z wielu źródeł

Czwarty argument zmienia podatne na błędy wyszukiwania w dopracowane, profesjonalne wyniki.

Sprawdź swoją znajomość argumentu if_not_found.

Nauczyli się Państwo obsługiwać wyszukiwania, które niczego nie znajdują:

• Domyślnie XLOOKUP zwraca #N/A, gdy nie ma dopasowania
• Opcjonalny argument if_not_found dostarcza czytelną wartość zastępczą
• Można użyć tekstu, wartości 0, pustej wartości, odwołania do komórki, a nawet drugiego XLOOKUP
• Argument ten obsługuje tylko przypadki braku dopasowania, dzięki czemu prawdziwe błędy pozostają widoczne

W następnej części wyszukają Państwo wartości w lewo oraz od dołu listy.