Excel Vlookup zwraca wiele wartości
Jedną z kluczowych funkcji funkcji WYSZUKAJ.PIONOWO jest to, że będzie działać dla unikatowych wartości, a jeśli istnieją jakiekolwiek zduplikowane wartości, wówczas pierwsza znaleziona wartość zostanie zwrócona również dla wszystkich innych wartości wyszukiwania. Jest to jedna z kluczowych rzeczy, o których musimy pamiętać podczas stosowania formuły WYSZUKAJ.PIONOWO. Gdy wartość wyszukiwania pojawia się wiele razy i ma wiele wartości, musimy uwzględnić różne strategie, w tym artykule pokażemy, jak zwrócić wiele wartości za pomocą funkcji WYSZUKAJ.PIONOWO.
Jak zwrócić wiele wartości za pomocą funkcji Vlookup?
Jak powiedzieliśmy powyżej, funkcja WYSZUKAJ.PIONOWO działa dla unikalnych wartości, a dla zduplikowanych wartości zwróci pierwszą znalezioną wartość.
Możesz pobrać ten Vlookup, aby zwrócić szablon programu Excel z wieloma wartościami tutaj - Vlookup, aby zwrócić szablon programu Excel z wieloma wartościamiNa przykład spójrz na poniższe dane.
Mamy „Tabelę 1” i „Tabelę 2”, w „Tabeli 1” mamy owoce i ich ceny w różnych miastach, a dla „Tabeli 2” musimy uzyskać koszt własny z „Tabeli 1” za pomocą funkcji WYSZUKAJ.PIONOWO. Najpierw zastosuj funkcję WYSZUKAJ.PIONOWO.
- Spójrz, mamy tę samą cenę dla wszystkich miast. Na przykład w „Tabeli 1” dla „Apple” w mieście „Bangalore” mamy 108, ponieważ jest to pierwsza wartość znaleziona w tabeli dla „Apple”, która zwróciła tę samą wartość dla wszystkich miast.
- Podobnie dla „Winogrona” pierwsza wartość to 79 i to samo zostało zwrócone dla wszystkich miast, aw przypadku „Pomarańczowych” również dla wszystkich miast.
Dlatego w takich przypadkach musimy utworzyć kolumnę pomocniczą, aby utworzyć unikalną listę wartości wyszukiwania. Każdy owoc ma różne ceny dla każdego miasta, więc połączenie nazwy owocu i miasta może stworzyć unikalną listę, wstawić kolumnę pomocniczą i połączyć nazwę owocu z nazwą miasta.
Więc każda nazwa owocu jest połączona z miastem przez dodanie ukośnika (/) jako separatora między nazwą owocu a nazwą miasta.
Teraz wróć do „Tabeli 2” i otwórz funkcję WYSZUKAJ.PIONOWO.
Teraz musimy uwzględnić tutaj tę samą strategię kolumny pomocnika, aby wybrać wartość wyszukiwania, najpierw wybierz nazwę owocu.
Następnie połącz ukośnik w tył przed połączeniem z nazwą miasta.
Teraz połącz nazwę miasta.
Teraz wartość wyszukiwania jest podobna do kolumny pomocnika, teraz wybierz tablicę tabeli, zaczynając od kolumny pomocnika.
Teraz podaj numer kolumny jako 4, a wyszukiwanie zakresu jako FAŁSZ lub 0.
Proszę bardzo, mamy nowy cennik kosztów z dokładnymi liczbami, powiedzmy dzięki kolumnie pomocniczej lub kombinacji Fruit Name & City.
Użyj alternatywnych metod dla wielu wartości
Widzieliśmy, jak kolumna pomocnicza może być pomocna w pobieraniu wielu wartości za pomocą formuły WYSZUKAJ.PIONOWO. Ale wyobraź sobie sytuację poniżej.
W tym przypadku nie mamy żadnej nazwy miasta, aby utworzyć kolumnę konkatenacji, więc może być konieczne zastosowanie różnych strategii. Poniżej znajduje się złożona formuła, której możemy użyć do uzyskania wielu wartości zduplikowanych unikalnych wartości.
= INDEKS (B $ 2: B $ 11, MAŁE (JEŻELI (E3 = A $ 2: 11 $ A, WIERSZ (2 A $: 11 $) - WIERSZ (2 $ A $) +1), WIERSZ (1: 1)))
Ta formuła wygląda na długą, prawda? Mamy jednak inną alternatywną metodę, czyli połączenie nazwy owoców z ich liczbą na liście.
Zastosuj poniższą funkcję LICZ.JEŻELI, aby utworzyć kolumnę pomocniczą.
Powyższa funkcja poda liczbę każdego owocu w połączeniu z samą nazwą owocu. Na przykład spójrz na wiersz numer 4, w tym przypadku mamy liczbę „Jabłka” 2 razy, więc liczba mówi 2, aw połączeniu z nazwą owocu daje nam „2 Jabłko”. Stworzy to więc unikalną listę owoców.
Teraz utwórz tabelę przeglądową podobną do poniższej.
Teraz otwórz funkcję WYSZUKAJ.PIONOWO w tabeli przeglądowej, tj. W komórce H3.
W kolumnie pomocnika liczy się pierwsza łączna wartość, więc tutaj należy wybrać wartość liczbową, a następnie połączyć z nazwą owocu.
Teraz wybierz tabelę i wprowadź numer indeksu kolumny, aby uzyskać wynik.
Rzeczy do zapamiętania
- WYSZUKAJ.PIONOWO zwraca tę samą wartość dla wartości wyszukiwania, jeśli wartość wyszukiwania ma zduplikowane nazwy.
- Aby pobrać wiele wartości tej samej wartości wyszukiwania, musimy utworzyć kolumny pomocnicze przy użyciu dowolnej z powyższych 3 metod.
