Co zrobić gdy WYSZUKAJ.PIONOWO zwraca 0 lub #N/D! ?
Kiedy korzystasz z funkcji WYSZUKAJ.PIONOWO, możesz natrafić na 2 trudności. Pierwszą jest sytuacja kiedy wybrana kolumna ma pustą komórkę w miejscu spełnienia warunków wyszukiwania (funkcja zwraca 0).
Drugim sytuacja, kiedy szukanego elementu na ma na liście (funkcja zwraca błąd #N/D!)
WYSZUKAJ.PIONOWO gdy nie ma na liście lub komórka jest pusta — sztuczki #16
Najpierw zajmijmy się 0. Wystarczy wynik funkcji WYSZUKAJ.PIONOWO przyrównać do 0 i umieścić go jako test logiczny funkcji JEŻELI
WYSZUKAJ.PIONOWO zwraca zero lub błąd
Jeśli warunek ten będzie spełniony, czyli funkcja WYSZUKAJ.PIONOWO zwróci 0 dla pustego wiersza, możesz podstawić dowolną wartość np: "Brak ID", kiedy warunek nie będzie spełniony to wstawić znowu wynik wyszukiwania:
JEŻELI(WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0)=0;"Brak ID";WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0))
WYSZUKAJ.PIONOWO zamiast zera zwraca Brak ID
Pierwsza trudność rozwiązana, teraz zajmijmy się drugą. W Excelu 2007 i nowszym możesz wykorzystać z funkcji JEŻELI.BŁĄD. Wystarczy wcześniejszą formułę wstawić jako jej parametr i ustawić wartość, kiedy pojawi się błąd np: "Brak na liście" i już obie trudności rozwiązane:
=JEŻELI.BŁĄD(JEŻELI(WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0)=0;"Brak ID";WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0));"Brak na liście")
WYSZUKAJ.PIONOWO zamiast błędu zwraca Brak na liście
W Excelu 2003 i wcześniejszych jest trudniej, bo nie ma funkcji JEŻELI.BŁĄD, trzeba wykorzystać funkcję CZY.BRAK. Zwróci ona wartość PRAWDA, gdy poda jej się błąd #N/D!, czyli np: Excel nie znajdzie szukanego elementu na liście.
Teraz trzeba odpowiednio opatulić w funkcje JEŻELI:
=JEŻELI(CZY.BRAK(WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0));"Brak na liście";JEŻELI(WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0)=0;"Brak ID";WYSZUKAJ.PIONOWO(D2;$A$2:$B$14;2;0)))
Formuły te możesz uprościć gdy powpisujesz wartości w puste komórki. Żeby to zrobić szybko najpierw:
- zaznacz cały obszar na którym chcesz uzupełnić puste komórki,
- naciśnij F5
- wybierz opcje specjalne
- wybierz puste
- wpisz wartość, którą chcesz i zatwierdź Ctrl + Enter
Teraz formuły będą wyglądać odpowiednio dla Excela 2007 i nowszego:
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D19;$A$19:$B$31;2;0);"Brak na liście")
WYSZUKAJ.PIONOWO gdy puste pozycje zamienione na Brak ID
dla Excela 2003 i starszego:
=JEŻELI(CZY.BRAK(WYSZUKAJ.PIONOWO(D19;$A$19:$B$31;2;0));"Brak na liście";WYSZUKAJ.PIONOWO(D19;$A$19:$B$31;2;0))
P.S.
Wpis na podstawie Excel Magic Trick 990
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Bezpośredni odnośnik do filmu na youtube — WYSZUKAJ.PIONOWO gdy nie ma na liście lub komórka jest pusta — sztuczki #16
super ! o to chodziło
To mnie cieszy, że o to chodziło
W przypadku braku na liście można zrobić prościej:
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(D19;$A$19:$B$31;2;0);"Brak na liście!")
Imo prościej
Co jeżeli funkcja wyszukaj pionowo zwraca dla pustego pola czasami zero 0 a czasami błąd. Z czego to wynika. Akurat dla mnie to czy pole było puste czy zero, to istotna informacja. (Excel 2016)
Błąd powinien się pojawiać gdy szukana wartość nie zostanie odnaleziona,
a 0 wtedy kiedy została znaleziona, ale powiązana z nią komórka jest pusta.
W czym może tkwić problem, jeśli w jednej tabeli raz formuła wyszuka właściwe przypisanie, a innym razem (mówię o jednej, konkretnej, tej samej wartości), wyrzuci błąd N#D, zakres formuły jest taki sam… ?