A Vlookup képlet csak akkor működik, ha a képletben lévő táblázat tömbje nem változik, de ha új oszlopot illesztenek be a táblába, vagy ha egy oszlopot törölnek, a képlet hibás eredményt ad vagy hibát tükröz, hogy a képlet hibátlan legyen ilyen dinamikus helyzetekben az egyezés függvényt használjuk az adatok indexének való megfeleléséhez és a tényleges eredmény visszaadásához.
Kombinálja a VLOOKUP-ot a Match-tal
A vlookup képlet a leggyakrabban használt függvény, amelyet arra használnak, hogy ugyanazon értéket keresse meg és adja vissza a megadott oszlopindexben, vagy egy másik oszlopindex értékét az első oszlop megfelelő értékére hivatkozva. A vlookup használata során a legnagyobb kihívás az, hogy a megadandó oszlopindex statikus és nem rendelkezik dinamikus funkcionalitással. Különösen akkor, ha több olyan feltételen dolgozik, amely megköveteli, hogy manuálisan módosítsa a referencia oszlopindexet. Ezáltal ezt a szükségletet a „MATCH” képlet segítségével lehet kielégíteni, hogy jobb tapadást vagy irányítást biztosítsunk a VLOOKUP képlet gyakran változó oszlopindexéhez.
VLookup és Match Formula
# 1 - VLOOKUP képlet
A VLOOKUP függvény képlete az Excelben
Itt minden megadandó argumentum kötelező.
- Keresési_érték - Itt be kell írni egy idézőjelet vagy dupla idézőjelű szöveget, hogy azonosítani lehessen az oszloptartományban.
- Táblázat tömb - Ehhez az argumentumhoz meg kell adni a táblázattartományt, ahol a Keresés_értéket kell keresni, és a lekérendő adatok az adott oszloptartományban találhatók.
- Col_index_num - Ebben az argumentumban meg kell adni az oszlop indexszámát vagy az oszlop számát a referencia első oszlopból, amelyből a megfelelő értéket ugyanabból a pozícióból kell kihúzni, mint az első oszlopban keresett értéket.
- [Range_lookup] - Ez az argumentum két lehetőséget ad.
- IGAZ - Hozzávetőleges egyezés: - Az argumentumot IGAZként vagy numerikusan „1” lehet megadni, amely a referenciaoszlopnak vagy az első oszlopnak megfelelő hozzávetőleges egyezést adja meg. Ezenkívül a táblázat tömb első oszlopában szereplő értékeket növekvő sorrendben kell rendezni.
- HAMIS - Pontos egyezés: - Itt a beírandó argumentum lehet HAMIS vagy numerikus „0”. Ez az opció csak az első oszloptartomány pozíciójából azonosítandó érték pontos egyezését adja vissza. Ha nem keresi meg az értéket az első oszlopból, akkor „# N / A” hibaüzenet jelenik meg.
# 2 - Meccsképlet
Az Egyezés függvény az adott táblázat tömbhöz megadott érték cellapozícióját adja vissza.
A szintaxison belüli összes argumentum kötelező.
- Keresési_érték - Itt a megadott argumentum lehet vagy az érték cellahivatkozása, vagy dupla idézőjelű szöveges karakterlánc, amelynek cellahelyzetét meg kell húzni.
- Keresési_rajz - Meg kell adni a táblázat tömbtartományát, amelynek értékét vagy cellatartalmát azonosítani kívánja.
- [egyezés típusa] - Ez az argumentum az alábbiakban ismertetett három lehetőséget kínálja.
- „1-kevesebb, mint” - Itt a beírandó argumentum numerikus „1”, amely a keresési értéknél kisebb vagy azzal egyenlő értéket adja vissza. A keresési tömböt növekvő sorrendben kell rendezni.
- „0-Pontos egyezés” - Itt a beírandó argumentumnak numerikus „0” -nak kell lennie. Ez az opció visszaadja az egyeztetett keresési érték pontos helyzetét. A keresési tömb azonban bármilyen sorrendben lehet.
- „-1-Nagyobb, mint” - A beírandó argumentumnak numerikus „-1” -nek kell lennie. A harmadik lehetőség megtalálja a legkisebb értéket, amely nagyobb vagy egyenlő a keresési értékkel. Itt a keresési tömb sorrendjét csökkenő sorrendben kell megadni.
# 3 - VLOOKUP a MATCH képlettel
= VLOOKUP (keresési_érték, tábla_rajz, MATCH (keresési_érték, keresési_rajz, [egyezési típus]), [tartománykeresés])
Hogyan használhatjuk a VLOOKUP-ot a Match Formula-val az Excelben?
Az alábbi példa segít megérteni a vlookup és a match formula működését az összeállítás során.
Ezt a VLookup with Match Excel sablont itt töltheti le - VLookup with Match Excel sablonTekintsük az alábbi adattáblázatot, amely leírja a megvásárolni kívánt jármű specifikációit.
A vlookup és az match függvény kombinált függvényének érthetőségének megértése érdekében értsük meg, hogyan működnek az egyes képletek, majd érjük el a vlookup meccs eredményeit, amikor összeállítjuk őket.
1. lépés - Az eredmény eléréséhez alkalmazzuk egyéni szinten a vlookup képletet.
A kimenet az alábbiakban látható:
Itt a keresési értéket $ B9-re nevezzük, ez az „E” modell, és a keresési tömböt az „$” abszolút értékű adattábla tartományaként adjuk meg, az oszlopindexet a „4” oszlopra utaljuk, amely a a „Típus” oszlop és a tartománykeresés pontos egyezést kap.
Így a következő képletet alkalmazzuk az „Üzemanyag” oszlop értékének visszaadásához .
A kimenet az alábbiakban látható:
Itt a keresési érték és a lookup_array abszolút „$” karakterlánccal ellátott keresési érték segít megjavítani a referencia cellát, még akkor is, ha a képletet más cellába másolja. Az „Üzemanyag” oszlopban meg kell változtatnunk az oszlopindexet „5” -re, mivel változik az az érték, amelyből az adatokat be kell szerezni.
2. lépés - Most alkalmazzuk a Match képletet az adott keresési érték pozíciójának lekéréséhez.
A kimenet az alábbiakban látható:
Amint az a fenti képernyőképen látható, itt megpróbáljuk lekérni az oszlop helyzetét a táblázat tömbjéből. Ebben az esetben a behúzandó oszlopszámot C8 cellának nevezzük, amely a „Type” oszlop, és a keresendő keresési tartomány oszlopfejlécek tartományaként van megadva, és az egyezési típus pontos egyezést ad meg „ 0 ”.
Így az alábbi táblázat megadja a kívánt eredményt az „Üzemanyag” oszlop pozícióinak esetében.
Most itt a keresett oszlop a D8 cella lesz, a kívánt oszlopindex pedig „5” lesz.
3. lépés - Most a Match képletet fogják használni a vlookup függvényen belül, hogy megkapja az értéket az azonosított oszlop pozícióból.
A kimenet az alábbiakban látható:
A fenti képletben az illesztési függvény a vlookup függvény oszlopindex paraméterének helyére kerül. Itt az egyezés függvény azonosítja a „C8” keresési érték referencia cellát, és az oszlop számát adja vissza a megadott táblázat tömbön keresztül. Ez az oszloppozíció a vlookup függvény oszlopindex-argumentumának bemeneteként szolgál. Melyik viszont segíti a vlookup-ot abban, hogy azonosítsa a kapott oszlopindex-számból visszaadandó értéket?
Hasonlóképpen alkalmaztuk a vlookup-ot egyezési képlettel az „Üzemanyag” oszlopra is.
A kimenet az alábbiakban látható:
Ezzel a kombinációs funkcióval alkalmazhatjuk a „Type” és az „Fuel” oszlopokat is.
Dolgok, amikre emlékezni kell
- A VLOOKUP csak a legelső bal oldalon használható a keresési értékekre. Az adattábla jobb oldalán keresendő értékek visszaadják a „# N / A” hibaértéket.
- A második argumentumban megadott table_array tartománynak abszolút „$” cellahivatkozásnak kell lennie, ez fenntartja a rögzített táblázat tömbtartományt, amikor a keresési képletet más cellákra alkalmazza, különben a táblázat tömbtartományának referencia cellái a következő cellára helyezkednek el referencia.
- A keresési értékben megadott érték nem lehet kisebb, mint a táblázat tömb első oszlopában szereplő legkisebb érték, különben a függvény visszaadja a „# N / A” hibaértéket.
- Mielőtt hozzáadna egy hozzávetőleges „TRUE” vagy „1” egyezést az utolsó argumentumban, ne felejtse el rendezni a táblázat tömbjét növekvő sorrendben.
- Az egyezés függvény csak az érték pozícióját adja vissza a vlookup tábla tömbben, és nem adja vissza az értéket.
- Abban az esetben, ha az Egyezés funkció nem tudja azonosítani a keresési érték pozícióját a táblázat tömbben, akkor a képlet „# N / A” értéket ad vissza a hibaértékben.
- A Vlookup és a match függvények nem érzékenyek a kis- és nagybetűkre, ha a keresési értéket egyezik a táblázat tömbjének megfelelő szövegértékkel.