Dla tych z was, którzy są dobrze zorientowani w programie Excel , najprawdopodobniej dobrze znacie funkcję WYSZUKAJ.PIONOWO^(VLOOKUP) . Funkcja WYSZUKAJ.PIONOWO^(VLOOKUP) służy do znajdowania wartości w innej komórce na podstawie pasującego tekstu w tym samym wierszu.

Jeśli nadal jesteś nowym użytkownikiem funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) , możesz zapoznać się z moim poprzednim postem na temat korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel^{(how to use VLOOKUP in Excel)} .

Tak potężny, jak jest, funkcja WYSZUKAJ.PIONOWO^(VLOOKUP) ma ograniczenia dotyczące struktury pasującej tabeli referencyjnej, aby formuła działała.

Ten artykuł pokaże Ci ograniczenie, w którym nie można użyć funkcji WYSZUKAJ.PIONOWO , i wprowadzi inną funkcję w programie ^(VLOOKUP)Excel o nazwie INDEX-MATCH , która może rozwiązać problem.

INDEKS DOPASUJ Przykład Excela

Korzystając z poniższego przykładowego arkusza kalkulacyjnego Excel , mamy listę właścicieli samochodów i nazwę samochodu. W tym przykładzie spróbujemy pobrać  identyfikator samochodu^{(Car ID)} na podstawie modelu samochodu^{(Car Model)} wymienionego pod wieloma właścicielami, jak pokazano poniżej:

Na osobnym arkuszu o nazwie CarType mamy prostą bazę danych samochodów z  ID , Modelem^{(Car Model)} i Kolorem^(Color) .

Przy tej konfiguracji tabeli  funkcja WYSZUKAJ.PIONOWO^(VLOOKUP) może działać tylko wtedy, gdy dane, które chcemy pobrać, znajdują się w kolumnie po prawej stronie tego, co próbujemy dopasować ( pole Model samochodu^{(Car Model )} ).

Innymi słowy, przy tej strukturze tabeli, ponieważ próbujemy dopasować ją na podstawie modelu samochodu^{(Car Model)} , jedyną informacją, jaką możemy uzyskać, jest kolor^{(Color )} (nie ID , ponieważ kolumna ID  znajduje się po lewej stronie  kolumny Model samochodu ).^{(Car Model )}

Dzieje się tak, ponieważ w przypadku funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) wartość wyszukiwania musi pojawić się w pierwszej kolumnie, a kolumny wyszukiwania muszą znajdować się po prawej stronie. Żaden z tych warunków nie jest spełniony w naszym przykładzie.

Dobra wiadomość jest taka, że ​​INDEX-MATCH  będzie w stanie nam w tym pomóc. W praktyce jest to połączenie dwóch funkcji Excela^(Excel) , które mogą działać indywidualnie: funkcji INDEKS^(INDEX) i funkcji PODAJ.POZYCJĘ^(MATCH) .

Jednak na potrzeby tego artykułu omówimy tylko połączenie tych dwóch w celu odtworzenia funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) .

Formuła może początkowo wydawać się nieco długa i onieśmielająca. Jednak po kilkukrotnym użyciu nauczysz się składni na pamięć.

Oto pełna formuła w naszym przykładzie:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Oto podział dla każdej sekcji

=INDEX( – “=” wskazuje początek formuły w komórce, a INDEKS^(INDEX) to pierwsza część używanej przez nas funkcji programu Excel .^(Excel)

CarType!$A$2:$A$5 – kolumny arkusza CarType  , w których znajdują się dane, które chcielibyśmy pobrać. W tym przykładzie identyfikator^(ID)  każdego modelu samochodu.^{(Car Model.)}

PODAJ.POZYCJĘ(^{(MATCH( )} – Druga część funkcji Excela^(Excel) , której używamy.

B4 – Komórka zawierająca wyszukiwany tekst, którego używamy ( Model samochodu^{(Car Model)} ) .

CarType!$B$2:$B$5 – Kolumny w arkuszu CarType   z danymi, których użyjemy do dopasowania do wyszukiwanego tekstu.

0)) – Aby wskazać, że wyszukiwany tekst musi dokładnie pasować do tekstu w pasującej kolumnie (tj CarType!$B$2:$B$5 ). Jeśli dokładne dopasowanie nie zostanie znalezione, formuła zwróci #N/A .

Uwaga: pamiętaj o podwójnym nawiasie zamykającym na końcu tej funkcji „))” i przecinkach między argumentami.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Osobiście odszedłem od funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) i teraz używam INDEX-MATCH, ponieważ jest on w stanie zrobić więcej niż WYSZUKAJ.PIONOWO^(VLOOKUP) .

Funkcje INDEX-MATCH mają również inne zalety w porównaniu do funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) :

1. Szybsze obliczenia^{(Faster Calculations)}

Kiedy pracujemy z dużymi zestawami danych, w których samo obliczenie może zająć dużo czasu ze względu na wiele funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) , zauważysz, że po zastąpieniu wszystkich tych formuł funkcją INDEX-MATCH , ogólne obliczenia będą obliczane szybciej.

1. Nie ma potrzeby liczenia kolumn względnych^{(No Need to Count Relative Columns)}

Jeśli nasza tabela referencyjna zawiera tekst klucza, który chcemy przeszukać w kolumnie C , a dane, które musimy uzyskać, znajdują się w kolumnie AQ , będziemy musieli wiedzieć/policzyć, ile kolumn znajduje się między kolumną C a kolumną AQ podczas korzystania z funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) .

Za pomocą funkcji  INDEX-MATCH możemy bezpośrednio wybrać kolumnę indeksu (tj. kolumnę AQ), w której musimy pobrać dane i wybrać kolumnę do dopasowania (tj. kolumnę C).

1. Wygląda na bardziej skomplikowaną^{(It Looks More Complicated)}

WYSZUKAJ.PIONOWO^(VLOOKUP) jest obecnie dość powszechny, ale niewielu wie o używaniu funkcji INDEX-MATCH razem.

Dłuższy ciąg znaków w funkcji INDEKS-POZYCJĘ^{(INDEX-MATCH)} pomaga sprawić, że będziesz wyglądać jak ekspert w obsłudze złożonych i zaawansowanych funkcji programu Excel^(Excel) . Cieszyć się!

When to Use Index-Match Instead of VLOOKUP in Excel

For thosе of you who arе wеll-vеrsed in Excel, you are most lіkely very famіliar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• Jak naprawić błędy #N/A w formułach programu Excel, takich jak WYSZUKAJ.PIONOWO

• Jak usunąć puste linie w Excelu?

• Jak korzystać z funkcji Speak Cells programu Excel

• Jak wstawić arkusz programu Excel do dokumentu programu Word?

• Jak korzystać z analizy warunkowej programu Excel

• 10 porad i wskazówek dotyczących programu Excel na rok 2019

• Użyj klawiatury, aby zmienić wysokość wiersza i szerokość kolumny w programie Excel

• Jak korzystać z odwołań bezwzględnych w programie Excel

• Dlaczego powinieneś używać nazwanych zakresów w Excelu

• Jak utworzyć wiele połączonych list rozwijanych w programie Excel?

• Wyśrodkuj dane arkusza roboczego w programie Excel do drukowania

• Użyj nazw zakresów dynamicznych w programie Excel, aby uzyskać elastyczne listy rozwijane

• Zachowaj odwołania do komórek podczas kopiowania formuły w programie Excel

• Podstawowe jedno- i wielokolumnowe sortowanie danych w arkuszach kalkulacyjnych Excel

• Jak usunąć linie siatki w programie Excel

• Jak zrobić wykres kołowy w programie Excel

• Jak scalić komórki, kolumny i wiersze w programie Excel

• Jak stworzyć prostą tabelę przestawną w programie Excel

• 4 sposoby użycia znacznika wyboru w programie Excel

• 2 sposoby korzystania z funkcji transpozycji programu Excel