Azok számára, akik jártasak az Excelben^(Excel) , valószínűleg nagyon jól ismerik a VLOOKUP funkciót. A VLOOKUP függvény arra szolgál, hogy értéket keressen egy másik cellában az ugyanazon sorban található egyező szöveg alapján.

Ha még nem ismeri a VLOOKUP funkciót, megtekintheti korábbi bejegyzésemet a VLOOKUP Excelben való használatáról^{(how to use VLOOKUP in Excel)} .

Bármilyen hatékony is, a VLOOKUP -nak van egy korlátja arra vonatkozóan, hogy miként kell felépíteni az egyező referenciatáblázatot ahhoz, hogy a képlet működjön.

Ez a cikk bemutatja azt a korlátozást, amikor a VLOOKUP nem használható, és bevezeti az Excel egy másik, ^(Excel)INDEX-MATCH nevű funkcióját, amely megoldhatja a problémát.

INDEX MATCH Excel Példa

A következő példa Excel táblázatot használva van egy listánk az autótulajdonosokról és az autó nevéről. Ebben a példában megpróbáljuk megszerezni az autóazonosítót a  több tulajdonos alatt felsorolt ^{(Car ID)}​​autómodell^{(Car Model)} alapján , az alábbiak szerint:

A CarType^(CarType) nevű külön lapon van egy egyszerű autóadatbázisunk az  azonosítóval ^{(Car Model)},^(ID) az autótípussal és a színnel^(Color) .

Ezzel a táblázatbeállítással a  VLOOKUP funkció csak akkor tud működni, ha a lekérni kívánt adatok az egyeztetni kívánt adatoktól jobbra található oszlopban találhatók ( Autómodell^{(Car Model )} mező).

Más szóval, ezzel a táblázatszerkezettel, mivel az Autómodell^{(Car Model)} alapján próbáljuk párosítani , az egyetlen információ, amit kaphatunk, a szín^{(Color )} (nem azonosító^(ID) , mivel az ID oszlop az ^(ID)Autómodell^{(Car Model )}  oszloptól balra található  ).

Ennek az az oka, hogy a VLOOKUP esetén a keresési értéknek az első oszlopban kell megjelennie, a keresési oszlopoknak pedig a jobb oldalon kell lenniük. Példánkban e feltételek egyike sem teljesül.

A jó hír az, hogy az INDEX-MATCH  segíthet nekünk ennek elérésében. A gyakorlatban ez valójában két Excel - függvény egyesítése, amelyek külön-külön is működhetnek: az INDEX függvény és a MATCH függvény.

Ebben a cikkben azonban csak a kettő kombinációjáról fogunk beszélni, azzal a céllal, hogy megismételjük a VLOOKUP funkcióját .

A képlet elsőre kissé hosszúnak és félelmetesnek tűnhet. Ha azonban többször használtad, akkor fejből megtanulod a szintaxist.

Ez a teljes képlet a példánkban:

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

Itt található az egyes szakaszok bontása

=INDEX( – A “=” a képlet elejét jelöli a cellában, az INDEX pedig az általunk használt Excel -függvény első része .

CarType!$A$2:$A$5 – a CarType lap azon oszlopai,  ahol a lekérni kívánt adatok találhatók. Ebben a példában  minden autómodell ^{(Car Model.)}azonosítója .^(ID)

MATCH( – Az általunk használt Excel függvény második része .

B4 – Az általunk használt keresési szöveget tartalmazó cella ( Autómodell^{(Car Model)} ) .

CarType!$B$2:$B$5 – A CarType lap oszlopai   azokkal az adatokkal, amelyeket a keresési szöveghez fogunk használni.

0)) – Annak jelzésére, hogy a keresett szövegnek pontosan meg kell egyeznie a megfelelő oszlopban található szöveggel (azaz CarType!$B$2:$B$5 ). Ha a pontos egyezés nem található, a képlet #N/A értékkel tér vissza .

Megjegyzés: ne feledje a függvény végén lévő dupla zárójelet „))” és az argumentumok közötti vesszőt.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Én személy szerint eltávolodtam a VLOOKUP -tól , és most az INDEX-MATCH-et használom, mivel többre képes, mint a VLOOKUP .

Az INDEX-MATCH funkcióknak más előnyei is vannak a VLOOKUP -hoz képest :

1. Gyorsabb számítások^{(Faster Calculations)}

Amikor nagy adathalmazokkal dolgozunk, ahol a számítás sok VLOOKUP függvény miatt hosszú ideig tarthat, azt tapasztalhatja, hogy miután az összes képletet lecseréli az INDEX-MATCH kifejezésre, a teljes számítás gyorsabb lesz.

1. Nem kell relatív oszlopokat számolni^{(No Need to Count Relative Columns)}

Ha a referenciatáblázatunk kulcsszövegét a C^(C) oszlopban szeretnénk keresni, és az adatok, amelyeket meg kell szereznünk, az AQ oszlopban vannak, akkor tudnunk kell/számolnunk kell, hogy hány oszlop van a C és az AQ oszlop között a VLOOKUP használatakor. .

Az  INDEX-MATCH függvényekkel közvetlenül kiválaszthatjuk azt az indexoszlopot (azaz AQ oszlopot), ahonnan az adatokat kell megkapnunk, és kiválaszthatjuk az egyeztetendő oszlopot (azaz C oszlopot).

1. Bonyolultabbnak tűnik^{(It Looks More Complicated)}

A VLOOKUP^(VLOOKUP) manapság meglehetősen elterjedt, de kevesen tudnak az INDEX-MATCH függvények együttes használatáról.

Az INDEX-MATCH^{(INDEX-MATCH)} függvény hosszabb karakterlánca segít abban, hogy úgy nézzen ki, mint egy szakértő az összetett és haladó Excel - függvények kezelésében. Élvezd!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of you who are wеll-versed in Excel, you are most likely 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

• A #N/A hibák javítása az Excel-képletekben, például a VLOOKUP-ban

• Az üres sorok törlése az Excelben

• Az Excel Speak Cells funkciójának használata

• Excel-munkalap beszúrása Word-dokumentumba

• Az Excel „Mi lenne, ha” elemzésének használata

• A PMT függvény használata Excelben

• A Flash Fill használata Excelben

• Hogyan készítsünk VBA-makrót vagy szkriptet az Excelben

• A Z-pontszám kiszámítása az Excelben

• 4 módszer az Excel konvertálására Google Táblázatokká

• 4 módszer a pipa használatára az Excelben

• 10 Excel tipp és trükk 2019-re

• Az Excel „Mi lenne, ha” elemző célkereső eszközének használata

• Hogyan javítsunk ki egy sort az Excelben

• Használja a billentyűzetet a sormagasság és az oszlopszélesség módosításához az Excelben

• A cellahivatkozások megőrzése képlet Excelben másolásakor

• Az abszolút hivatkozások használata az Excelben

• Mi az a VBA-tömb az Excelben, és hogyan kell programozni egyet

• 3 módszer a cellák felosztására az Excelben

• Hogyan távolítsuk el a rácsvonalakat az Excelben