Formuła ogólna
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Podsumowanie
Aby odwrócić WYSZUKAJ.PIONOWO - tj. Znaleźć oryginalną wartość wyszukiwania przy użyciu wyniku formuły WYSZUKAJ.PIONOWO - możesz użyć skomplikowanej formuły opartej na funkcji WYBIERZ lub prostszych formuł opartych na INDEKS i PODAJ.LUB lub XLOOKUP, jak wyjaśniono poniżej. W pokazanym przykładzie formuła w H10 to:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
W przypadku tej konfiguracji funkcja WYSZUKAJ.PIONOWO znajduje opcję powiązaną z kosztem 3000 i zwraca „C”.
Uwaga: to jest bardziej zaawansowany temat. Jeśli dopiero zaczynasz korzystać z funkcji WYSZUKAJ.PIONOWO, zacznij tutaj.
Wprowadzenie
Kluczowym ograniczeniem funkcji WYSZUKAJ.PIONOWO jest możliwość wyszukiwania wartości tylko po prawej stronie. Innymi słowy, kolumna z wartościami wyszukiwania musi znajdować się po lewej stronie wartości, które chcesz pobrać za pomocą funkcji WYSZUKAJ.PIONOWO. W rezultacie przy standardowej konfiguracji nie ma możliwości użycia funkcji WYSZUKAJ.PIONOWO do „spojrzenia w lewo” i odwrócenia oryginalnego wyszukiwania.
Z punktu widzenia WYSZUKAJ.PIONOWO możemy zwizualizować problem w następujący sposób:
Objaśnione poniżej obejście polega na użyciu funkcji WYBIERZ w celu zmiany kolejności tabeli w WYSZUKAJ.PIONOWO.
Wyjaśnienie
Zaczynając od początku, formuła w H5 to normalna formuła WYSZUKAJ.PIONOWO:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Używając G5 jako wartości wyszukiwania („C”) i danych w B5: D8 jako tablicy tabeli, funkcja WYSZUKAJ.PIONOWO przeprowadza wyszukiwanie wartości w kolumnie B i zwraca odpowiednią wartość z kolumny 3 (kolumna D), 3000. Uwaga zero (0) podano jako ostatni argument wymuszający dokładne dopasowanie.
Formuła w G10 po prostu pobiera wynik z H5:
=H5 // 3000
Aby przeprowadzić wyszukiwanie wsteczne, formuła w H10 to:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Trudną kwestią jest funkcja WYBIERZ, która służy do zmiany kolejności tablicy tabeli tak, aby Koszt był pierwszą kolumną, a Opcja ostatnią:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Funkcja WYBIERZ służy do wybierania wartości na podstawie indeksu liczbowego. W tym przypadku podajemy trzy wartości indeksu w stałej tablicowej:
(3,2,1) // array constant
Innymi słowy, prosimy o kolumnę 3, następnie kolumnę 2, potem kolumnę 1. Po nich następują trzy zakresy, które reprezentują każdą kolumnę tabeli w kolejności, w jakiej pojawiają się w arkuszu.
W tej konfiguracji WYBIERZ zwraca wszystkie trzy kolumny w jednej tablicy 2D w następujący sposób:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Jeśli wizualizujemy tę tablicę jako tabelę w arkuszu, otrzymamy:
Uwaga: nagłówki nie są częścią tablicy i są tutaj pokazane tylko dla przejrzystości.
W efekcie zamieniliśmy kolumny 1 i 3. Zreorganizowana tabela jest zwracana bezpośrednio do WYSZUKAJ.PIONOWO, która odpowiada wartości 3000 i zwraca odpowiednią wartość z kolumny 3, „C”.
Z INDEX i MATCH
Powyższe rozwiązanie działa dobrze, ale trudno je polecić, ponieważ większość użytkowników nie zrozumie, jak działa formuła. Lepszym rozwiązaniem jest INDEX i MATCH, wykorzystując następujący wzór:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Tutaj funkcja MATCH znajduje wartość 3000 w D5: D8 i zwraca jej pozycję, 3:
MATCH(G10,D5:D8,0) // returns 3
Uwaga: PODAJ.POZYCJĘ jest skonfigurowane na dokładne dopasowanie, ustawiając ostatni argument na zero (0).
PODAJ.POZYCJĘ zwraca wynik bezpośrednio do INDEKSU jako numer wiersza, więc formuła wygląda następująco:
=INDEX(B5:B8,3) // returns "C"
a INDEKS zwraca wartość z trzeciego wiersza B5: B8, „C”.
Ta formuła pokazuje, jak INDEKS i PODAJ.POZYCJĘ mogą być bardziej elastyczne niż WYSZUKAJ.PIONOWO.
Dzięki XLOOKUP
XLOOKUP zapewnia również bardzo dobre rozwiązanie. Równoważna formuła to:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Z wartością wyszukiwania z G10 (3000), tablicą al ookup D5: D8 (koszty) i tablicą wyników B5: B8 (opcje), XLOOKUP lokalizuje 3000 w tablicy odnośników i zwraca odpowiedni element z tablicy wyników, "DO". Ponieważ XLOOKUP domyślnie wykonuje dokładne dopasowanie, nie ma potrzeby jawnego ustawiania trybu dopasowania.
