A Excel VLOOKUP funkció egy hatékony eszköz, amely segít megkeresni egy megadott értéket úgy, hogy a táblázat vagy egy tartomány első oszlopában függőlegesen illeszkedik, majd visszaadja a megfelelő értéket egy másik oszlopból ugyanabban a sorban. Bár a VLOOKUP hihetetlenül hasznos, a kezdők számára néha nehéz lehet megérteni. Ennek az oktatóanyagnak az a célja, hogy segítsen a VLOOKUP elsajátításában az érvek lépésről lépésre történő magyarázata, hasznos példákat és a megoldások a gyakori hibákra találkozhat a VLOOKUP funkció használatakor.

• Az érvek lépésről lépésre történő magyarázata
• Szintaxis és érvek
• VLOOKUP Példák
  Pontos egyezés vs. hozzávetőleges egyezés   |   Vlookup több feltétellel
• VLOOKUP Gyakori hibák és megoldások
  #N/A hiba   |   #VALUE hiba   |   #REF hiba   |   Helytelen érték

VLOOKUP példák .xlsx VLOOKUP hibák .xlsx

Kapcsolódó videók

Az érvek lépésről lépésre történő magyarázata

Amint a fenti képernyőképen látható, a VLOOKUP funkció egy e-mail megkeresésére szolgál egy adott azonosítószám alapján. Most részletes magyarázatot adok a VLOOKUP használatára ebben a példában, lépésről lépésre lebontva az egyes argumentumokat.

1. lépés: Indítsa el a VLOOKUP funkciót

Válasszon ki egy cellát (ebben az esetben H6) az eredmény kimenetéhez, majd indítsa el a VLOOKUP funkciót a következő tartalom beírásával Formula Bar.

=VLOOKUP(

2. lépés: Adja meg a keresési értéket

Először adja meg a keresési értéket (ez az, amit keres) a VLOOKUP függvényben. Itt a G6 cellára hivatkozom, amely egy bizonyos 1005-ös azonosítószámot tartalmaz.

=VLOOKUP(G6

3. lépés: Adja meg a táblatömböt

Ezután adjon meg egy cellatartományt, amely tartalmazza mind a keresett, mind a visszaadni kívánt értéket. Ebben az esetben a B6:E12 tartományt választom. A képlet most a következőképpen néz ki:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

4. lépés: Adja meg azt az oszlopot, amelyből értéket szeretne visszaadni

Ezután adja meg azt az oszlopot, amelyből értéket szeretne visszaadni.

Ebben a példában, mivel az e-mailt azonosítószám alapján kell visszaküldenem, itt egy 4-es számot írok be, hogy a VLOOKUP-nak megmondjam, hogy az adattartomány negyedik oszlopából adjon vissza értéket.

=VLOOKUP(G6,B6:E12,4

5. lépés: Keressen hozzávetőleges vagy pontos egyezést

Végül döntse el, hogy közelítő vagy pontos egyezést keres.

• Találni egy pontos egyezés, használnia kell HAMIS utolsó érvként.
• Találni egy hozzávetőleges egyezés, Használata TRUE utolsó argumentumként, vagy hagyja üresen.

Ebben a példában a FALSE-t használom a pontos egyezéshez. A képlet most így néz ki:

=VLOOKUP(G6,B6:E12,4,FALSE

Nyomja meg az Enter billentyűt az eredmény eléréséhez

A fenti példában az egyes argumentumok egyenkénti magyarázatával a VLOOKUP függvény szintaxisa és argumentumai most sokkal könnyebben megérthetők.

Szintaxis és érvek

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Keresési_érték (kötelező): A keresett érték (valódi érték vagy cellahivatkozás). Ne feledje, hogy ennek az értéknek a table_array első oszlopában kell lennie.
• Táblázat_tömb (kötelező): Egy cellatartomány tartalmazza a keresési érték oszlopát és a visszatérési érték oszlopát is.
• Oszlop_index (kötelező): Az egész szám a visszatérési értéket tartalmazó oszlop számát jelenti. 1-gyel kezdődik a table_array bal szélső oszlopában.
• Tartomány_keresés (opcionális): Logikai érték, amely meghatározza, hogy a VLOOKUP hozzávetőleges vagy pontos egyezést keres-e.
  • Hozzávetőleges mérkőzés - Állítsa be ezt az argumentumot TRUE, 1 vagy hagyja üres.
    fontos: A hozzávetőleges egyezés megtalálásához a table_array első oszlopában lévő értékeket növekvő sorrendbe kell rendezni arra az esetre, ha a VLOOKUP rossz eredményt ad vissza.
  • Pontos egyezés - Állítsa be ezt az argumentumot HAMIS or 0.

Példák

Ez a rész néhány példát mutat be, amelyek segítenek a VLOOKUP funkció átfogóbb megértésében.

1. példa: Pontos egyezés és hozzávetőleges egyezés a VLOOKUP-ban

Ha a VLOOKUP használatakor zavarban van a pontos egyezést és a hozzávetőleges egyezést illetően, ez a szakasz segíthet tisztázni ezt a zavart.

Pontos egyezés a VLOOKUP-ban

Ebben a példában az E6:E8 tartományban felsorolt ​​pontszámok alapján fogom megtalálni a megfelelő neveket, ezért beírom a következő képletet az F6 cellába, és lehúzom az Automatikus kitöltés fogantyúját az F8-ra. Ebben a képletben az utolsó argumentum így van megadva HAMIS pontos egyezés keresésének végrehajtásához.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Mivel azonban a 98-as pontszám nem létezik az adattartomány első oszlopában, a VLOOKUP #N/A hibaeredményt ad vissza.

Hozzávetőleges egyezés a VLOOKUP-ban

Továbbra is a fenti példát használva, ha az utolsó argumentumot erre módosítja TRUE, a VLOOKUP hozzávetőleges egyezéskeresést hajt végre. Ha nem található egyezés, akkor megkeresi a következő legnagyobb értéket, amely kisebb, mint a keresési érték, és visszaadja a megfelelő eredményt.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Mivel a 98-as pontszám nem létezik, a VLOOKUP megkeresi a következő legnagyobb értéket, amely kisebb, mint 98, ami 95, és a legközelebbi eredményként a 95-ös pontszám nevét adja vissza.

2. példa: Használja a VLOOKUP-ot több feltétellel

Ez a rész bemutatja, hogyan használható a VLOOKUP több feltétellel az Excelben. Az alábbi képernyőképen látható módon, ha egy megadott név (a H5 cellában) és a részleg (a H6 cellában) alapján próbál keresni, kövesse az alábbi lépéseket ennek végrehajtásához.

1. lépés: Adjon hozzá egy segédoszlopot a keresési oszlopokból származó értékek összefűzéséhez

Ebben az esetben létre kell hoznunk egy segítő oszlopot az értékek összefűzéséhez Név oszlop és a osztály oszlop.

1. Adjon hozzá egy segédoszlopot az adattartomány bal oldalán, és adjon fejlécet ennek az oszlopnak. Lásd a képernyőképet:
   
   
2. Ebben a segítő oszlopban válassza ki a fejléc alatti első cellát, és írja be a következő képletet a Formula sáv, és nyomja meg a gombot belép.
   

   =C6&" "&D6

   
   Megjegyzések: Ebben a képletben egy „és” jelet (&) használunk, hogy a szöveget két oszlopban egyesítsük, így egyetlen szövegrészt állítunk elő.

   • C6 a keresztneve a Név oszlophoz csatlakozni, D6 az első osztálya a osztály oszlophoz csatlakozni.
   • E két cella értékeit egy szóköz köti össze.
3. Jelölje ki ezt az eredménycellát, majd húzza a AutoFill fogantyú lefelé, hogy alkalmazza ezt a képletet ugyanabban az oszlopban lévő többi cellára.
   
   

2. lépés: Alkalmazza a VLOOKUP függvényt a megadott feltételekkel

Jelöljünk ki egy cellát, ahova ki szeretnénk írni az eredményt (itt az I7-et választom), írjuk be a következő képletet a Formula sáv, majd nyomja meg a gombot belép.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Eredmény

Gyakori VLOOKUP hibák és megoldások

Ez a szakasz felsorolja azokat a gyakori hibákat, amelyekkel a VLOOKUP használata során találkozhat, és megoldásokat kínál a javításukra.

#N/A hiba jelenik meg

A VLOOKUP leggyakoribb hibája a #N/A hiba, ami azt jelenti, hogy az Excel nem találta a keresett értéket. Íme néhány ok, amiért a VLOOKUP #N/A hibát adhat vissza.

1. ok: A keresési érték nem található a table_array első oszlopában

Az Excel VLOOKUP egyik korlátja, hogy csak balról jobbra nézhet. Tehát a keresési értékeknek a table_array első oszlopában kell lenniük.

Az alábbi képernyőképen látható módon a megadott munkakör alapján szeretnék nevet adni. Itt a keresési érték (értékesítési menedzser) a táblázat_tömb második oszlopában található, a visszatérési érték pedig a keresési oszlop bal oldalán található, így a VLOOKUP #N/A hibát ad vissza.

Megoldások

A hiba kijavításához az alábbi megoldások bármelyikét alkalmazhatja.

• Rendezd át az oszlopokat
  
  Az oszlopokat átrendezheti úgy, hogy a keresőoszlop a table_array első oszlopába kerüljön.
• Használja együtt az INDEX és a MATCH függvényeket
  
  Itt az INDEX és a MATCH függvényeket együtt használjuk a VLOOKUP alternatívájaként a probléma megoldására.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Használja az XLOOKUP függvényt (elérhető az Excel 365, Excel 2021 és újabb verziókban)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

2. ok: A keresési érték nem található a keresési oszlopban (pontos egyezés)

Az egyik leggyakoribb ok, amiért a VLOOKUP #N/A hibát ad vissza, az az, hogy a keresett érték nem található.

Ahogy az alábbi példában is látható, az E98-ban megadott 6-as pontszám alapján fogjuk megtalálni a nevet. Ez a pontszám azonban nem létezik az adattartomány első oszlopában, ezért a VLOOKUP #N/A hibaeredményt ad vissza.

Megoldások

A hiba kijavításához próbálkozzon az alábbi megoldások egyikével.

• Ha azt szeretné, hogy a VLOOKUP a következő legnagyobb értéket keresse, amely kisebb, mint a keresési érték, módosítsa az utolsó argumentumot HAMIS (pontos egyezés) -hoz TRUE (hozzávetőleges egyezés). További információért lásd: 1. példa: Pontos egyezés és hozzávetőleges egyezés a VLOOKUP használatával.
• Az utolsó argumentum megváltoztatásának elkerülése érdekében, és emlékeztetőt kapjon, ha a keresési érték nem található, beillesztheti a VLOOKUP függvényt az IFERROR függvénybe:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

3. ok: A keresési érték kisebb, mint a keresési oszlop legkisebb értéke (hozzávetőleges egyezés)

Ahogy az alábbi képernyőképen látható, Ön hozzávetőleges egyezéskeresést hajt végre. A keresett érték (ebben az esetben az 1001-es azonosítószám) kisebb, mint a keresési oszlop legkisebb 1002-es értéke, ezért a VLOOKUP #N/A hibát ad vissza.

Megoldások

Íme két megoldás az Ön számára.

• Győződjön meg arról, hogy a keresési érték nagyobb vagy egyenlő, mint a keresési oszlop legkisebb értéke.
• Ha azt szeretné, hogy az Excel emlékeztesse Önt arra, hogy a keresési érték nem található, csak helyezze be a VLOOKUP függvényt az IFERROR függvénybe az alábbiak szerint:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

4. ok: A számok szövegként vannak formázva

Amint az alábbi képernyőképen látható, ebben a példában a #N/A hiba az eredeti táblázat keresőcellája (G6) és keresési oszlopa (B6:B12) közötti adattípus-eltérésből adódik. Itt a G6 értéke egy szám, a B6:B12 tartományban lévő értékek pedig szövegként formázott számok.

Megoldások

A probléma megoldásához a keresési értéket vissza kell konvertálnia számmá. Íme két módszer az Ön számára.

• Alkalmazza a Számmá konvertálás funkciót
  
  Kattintson arra a cellára, amelyben a szöveget számmá szeretné alakítani, és válassza ki ezt a gombot  a cella mellett, majd válassza ki Átalakítás számra.
  
• Használjon egy praktikus eszközt a szöveg és a szám közötti kötegelt konvertáláshoz
  
  A Konvertálás szöveg és szám között jellemzője Kutools az Excel számára segít könnyen konvertálni egy sor cellát szövegből számmá és fordítva. 30 napos, teljes értékű ingyenes próbaverzió most!
  

5. ok: A table_array nem állandó, amikor a VLOOKUP képletet más cellákba húzza

Amint az alábbi képernyőképen látható, két keresési érték van az E6-ban és az E7-ben. Miután megkapta az első eredményt az F6-ban, húzza a VLOOKUP képletet az F6 cellából az F7-be, és egy #N/A hibaeredmény jelenik meg. Ennek az az oka, hogy a cellahivatkozások (B6:C12) alapértelmezés szerint relatívak, és a sorokon lefelé haladva módosíthatók. A táblázattömb lejjebb került a B7:C13-ba, amely már nem tartalmazza a 73-as keresési pontszámot.

Megoldás

Zárolnia kell a táblatömböt, hogy állandó maradjon az a hozzáadásával $ jel a sorok és oszlopok előtt a cellahivatkozásokban. Ha többet szeretne megtudni az Excel abszolút hivatkozásáról, tekintse meg ezt az oktatóanyagot: Excel abszolút referencia (hogyan kell elkészíteni és használni).

#VALUE hiba jelenik meg

A következő feltételek miatt a VLOOKUP #VALUE hibaeredményt ad vissza.

1. ok: A keresési érték meghaladja a 255 karaktert

Amint az alábbi képernyőképen látható, a H4 cellában lévő keresési érték meghaladja a 255 karaktert, így a VLOOKUP #VALUE hibaeredményt ad vissza.

Megoldások

A korlátozás megkerüléséhez alkalmazhat egy másik keresési függvényt, amely képes kezelni a hosszabb karakterláncokat. Próbálja ki az alábbi képletek egyikét.

• INDEX és MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• XLOOKUP funkció (elérhető Excel 365, Excel 2021 és újabb verziókban):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

2. ok: A col_index argumentum kisebb, mint 1

Az oszlopindex a visszaadni kívánt értéket tartalmazó táblatömb oszlopszámát adja meg. Ennek az argumentumnak pozitív számnak kell lennie, amely megfelel a táblázattömb egy érvényes oszlopának.

Ha olyan oszlopindexet ad meg, amely kisebb, mint 1 (azaz nulla vagy negatív), a VLOOKUP nem fogja tudni megtalálni az oszlopot a táblázattömbben.

Megoldás

A probléma megoldásához győződjön meg arról, hogy a VLOOKUP képlet oszlopindex argumentuma pozitív szám, amely megfelel a táblatömb egy érvényes oszlopának.

#REF hiba visszaküldve

Ez a szakasz felsorolja az egyik okot, amiért a VLOOKUP #REF hibát ad vissza, és megoldásokat kínál erre a problémára.

Ok: A col_index argumentum nagyobb, mint az oszlopok száma

Amint az alábbi képernyőképen látható, a táblázattömbnek csak 4 oszlopa van. A VLOOKUP képletben megadott oszlopindex azonban 5, ami nagyobb, mint a táblázattömb oszlopainak száma. Ennek eredményeként a VLOOKUP nem tudja megtalálni az oszlopot, és végül #REF hibát ad vissza.

Megoldások

• Adjon meg egy helyes oszlopszámot
  Győződjön meg arról, hogy a VLOOKUP képlet oszlopindex argumentuma olyan szám, amely megfelel a táblázattömb egy érvényes oszlopának.
• Az oszlopszám automatikus lekérése a megadott oszlopfejléc alapján
  Ha a táblázat sok oszlopot tartalmaz, akkor gondot okozhat a helyes oszlopindexszám meghatározása. Itt beágyazhatja a MATCH függvényt a VLOOKUP függvénybe, hogy megkeresse az oszlop pozícióját egy bizonyos oszlopfejléc alapján.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Megjegyzések: A fenti képletben a MATCH("E-mail",B5:E5, 0) A függvény a "E-mail" oszlopban a B6:E12 dátumtartományban. Itt az eredmény 4, amelyet a VLOOKUP függvény col_indexeként használ.

Helytelen értéket adunk vissza

Ha úgy találja, hogy a VLOOKUP nem a megfelelő eredményt adja vissza, azt a következő okok okozhatják

1. ok: A keresési oszlop nincs növekvő sorrendben rendezve

Ha az utolsó argumentumot a TRUE (Vagy üresen hagyta) hozzávetőleges egyezés esetén, és a keresési oszlop nincs növekvő sorrendben rendezve, előfordulhat, hogy a kapott érték helytelen.

Megoldás

Ha a keresési oszlopot növekvő sorrendbe rendezi, az segíthet a probléma megoldásában. Ehhez kövesse az alábbi lépéseket:

1. Jelölje ki az adatcellákat a keresési oszlopban, lépjen a dátum fülre kattintva Rendezés a legkisebbtől a legnagyobbig a Rendezés és szűrés csoport.
2. A Rendezés figyelmeztetés párbeszédpanelen válassza a ikont Bontsa ki a választékot opciót, és kattintson rá OK.

2. ok: Egy oszlop beillesztésre vagy eltávolításra került

Ahogy az alábbi képernyőképen is látható, az eredetileg visszaadni kívánt érték a táblázattömb negyedik oszlopában található, ezért a col_index számot 4-ben adom meg. Új oszlop beszúrásakor az eredmény oszlop a táblázat ötödik oszlopa lesz. tömböt, aminek következtében a VLOOKUP rossz oszlopból adja vissza az eredményt.

Megoldások

Íme két megoldás az Ön számára.

• Manuálisan módosíthatja az oszlop indexszámát, hogy megfeleljen a visszatérési oszlop pozíciójának. Az itt található képletet a következőre kell módosítani:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Ha mindig egy certia oszlopból szeretné visszaadni az eredményt, például ebben a példában az E-mail oszlopból. Az alábbi képlet segíthet az oszlopindex automatikus egyeztetésében az adott oszlopfejléc alapján, függetlenül attól, hogy az oszlopokat beszúrjuk vagy eltávolítjuk a táblázattömbből.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Egyéb funkciók megjegyzései

• A VLOOKUP csak balról jobbra haladva keresi az értéket.
  A keresési érték a bal szélső oszlopban található, az eredményértéknek pedig a keresési oszloptól jobbra lévő bármely oszlopban kell lennie.
• Ha az utolsó argumentumot üresen hagyja, a VLOOKUP alapértelmezés szerint hozzávetőleges egyezést használ.
• A VLOOKUP kis- és nagybetűket nem érzékeny keresést hajt végre.
• Több egyezés esetén a VLOOKUP csak a táblázattömbben talált első egyezést adja vissza, a táblatömbben lévő sorok sorrendje alapján.

Kapcsolódó cikkek