Ugrás a tartalomra

20+ VLOOKUP példa kezdő és haladó Excel felhasználóknak

A VLOOKUP függvény az Excel egyik legnépszerűbb funkciója. Ez az oktatóanyag lépésről lépésre bemutatja a VLOOKUP funkció használatát az Excelben, több tucat alapvető és haladó példával.


A VLOOKUP függvény bemutatása - Szintaxis és érvek

Az Excelben a VLOOKUP függvény a legtöbb Excel-felhasználó számára hatékony funkció, lehetővé teszi, hogy értéket keressen az adattartomány bal szélén, és egy megfelelő értéket adjon vissza ugyanabban a sorban az alábbi képernyőképen megadott oszlopból. .

A VLOOKUP függvény szintaxisa:

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

érvek:

Keresési_érték (kötelező): A keresni kívánt érték. Ez lehet érték (szám, dátum vagy szöveg) vagy cellahivatkozás. A table_array tartomány első oszlopában kell lennie. 

Táblázat_tömb (kötelező): Az adattartomány vagy táblázat, ahol a keresési érték oszlop és az eredményérték oszlop található.

Oszlop_index_szám (kötelező): A visszatérési értékeket tartalmazó oszlop száma. A táblázat tömbjének bal szélső oszlopából 1-gyel kezdődik.

Tartomány_keresés (opcionális): Logikai érték, amely meghatározza, hogy ez a VLOOKUP függvény pontos vagy közelítő egyezést ad-e vissza.

  • Hozzávetőleges egyezés – 1 / IGAZ / kihagyva (alapértelmezett): Ha nem található pontos egyezés, a képlet a legközelebbi egyezést keresi – a legnagyobb értéket, amely kisebb, mint a keresési érték.
    Közlemény: Ebben az esetben a keresési oszlopot (az adattartomány bal szélső oszlopát) növekvő sorrendbe kell rendeznie, ellenkező esetben hibás vagy #N/A hiba eredményt ad vissza.
  • Pontos egyezés – 0 / HAMIS: Ez a keresési értékkel pontosan megegyező érték keresésére szolgál. Ha nem található pontos egyezés, a # N / A hibaértéket adja vissza.

Megjegyzések a funkcióról:

  • A Vlookup funkció csak balról jobbra haladva keres egy értéket.
  • A Vlookup funkció a kis- és nagybetűk megkülönböztetése nélkül hajt végre keresést.
  • Ha a keresési érték alapján több egyező érték is létezik, akkor a Vlookup függvény csak az első egyezőt adja vissza.

Alapvető VLOOKUP példák

Ebben a részben néhány gyakran használt Vlookup képletről fogunk beszélni.

2.1 Pontos egyezés és hozzávetőleges egyezés VLOOKUP

 2.1.1 Végezze el a VLOOKUP pontos egyezését

Általában, ha pontos egyezést keres a VLOOKUP függvénnyel, csak a FALSE-t kell használnia utolsó argumentumként.

Például a megfelelő matematikai pontszámok megszerzéséhez a konkrét azonosító számok alapján tegye a következőket:

Kérjük, másolja ki és illessze be az alábbi képletet egy üres cellába (itt a G2-t választom), és nyomja meg a gombot belép kulcs az eredmény eléréséhez:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Megjegyzés: A fenti képletben négy argumentum található:

  • F2 az a cella, amely tartalmazza a keresni kívánt C1005 értéket;
  • A2: D7 az a táblatömb, amelyben a keresést végrehajtja;
  • 3 az az oszlopszám, amelyből az egyező érték visszaadásra kerül; (Amint a függvény észleli a C1005 azonosítót, a táblázattömb harmadik oszlopába lép, és ugyanabban a sorban adja vissza az értékeket, mint a C1005 azonosítóé.)
  • HAMIS a pontos egyezésre utal.

Hogyan működik a VLOOKUP képlet?

Először a C1005 azonosítót keresi a táblázat bal szélső oszlopában. Felülről lefelé halad, és az A6 cellában találja meg az értéket.

Amint megtalálja az értéket, a harmadik oszlopban jobbra lép, és kivonja belőle az értéket.

Tehát az alábbi képernyőképen kapja meg az eredményt:

Jegyzet: Ha a keresési érték nem található a bal szélső oszlopban, akkor #N/A hibát ad vissza.
🤖 Kutools AI Aide: Forradalmasítsa az adatelemzést a következők alapján: Intelligens végrehajtás   |  Kód létrehozása  |  Hozzon létre egyéni képleteket  |  Adatok elemzése és diagramok létrehozása  |  A Kutools funkciók meghívása...
Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup  |   Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ...
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ...
Oszlopkezelő: Adjon hozzá adott számú oszlopot  |  Oszlopok mozgatása   |  Oszlopok elrejtése  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár   |  Munkafüzet és lapkezelő  |  Erőforrás-könyvtár   |  Dátumválasztó  |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt betűvel...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   Sok más...

A Kutools for Excel több mint 300 funkcióval büszkélkedhet, Győződjön meg arról, hogy amire szüksége van, csak egy kattintásnyira van...

 
 2.1.2 Végezzen hozzávetőleges egyezést a VLOOKUP-val

A közelítő egyezés hasznos az adattartományok közötti értékek kereséséhez. Ha a pontos egyezés nem található, a hozzávetőleges VLOOKUP a legnagyobb értéket adja vissza, amely kisebb, mint a keresési érték.

Például, ha az alábbi adatokkal rendelkezik, és a megadott megbízások nem szerepelnek a Rendelések oszlopban, hogyan kaphatja meg a legközelebbi Kedvezményt a B oszlopban?

1. lépés: Alkalmazza a VLOOKUP képletet, és töltse ki a többi cellára

Másolja és illessze be a következő képletet egy cellába, ahová az eredményt el szeretné helyezni, majd húzza le a kitöltő fogantyút a képlet más cellákra való alkalmazásához.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Eredmény:

Most megkapja a hozzávetőleges egyezéseket a megadott értékek alapján, lásd a képernyőképet:

Megjegyzések:

  • A fenti képletben:
    • D2 az az érték, amelynek relatív információját szeretné visszaadni;
    • A2: B9 az adattartomány;
    • 2 jelzi az oszlopszámot, amelyhez az egyező értéket adják vissza;
    • TRUE a hozzávetőleges mérkőzésre utal.
  • A hozzávetőleges egyezés a legnagyobb értéket adja vissza, amely kisebb, mint a konkrét keresési érték, ha nem található pontos egyezés.
  • Ha a VLOOKUP függvényt hozzávetőleges egyezési értékhez szeretné használni, akkor az adattartomány bal szélső oszlopát növekvő sorrendbe kell rendeznie, különben rossz eredményt ad vissza.

2.2 Csináljon egy kis- és nagybetűket megkülönböztető VLOOKUP-t az Excelben

Alapértelmezés szerint a VLOOKUP függvény a kis- és nagybetűket nem érző keresést hajt végre, ami azt jelenti, hogy a kis- és nagybetűket azonosként kezeli. Előfordulhat, hogy az Excelben kis- és nagybetűk közötti keresést kell végrehajtania, a normál VLOOKUP funkció nem biztos, hogy megoldja. Ebben az esetben használhat alternatív funkciókat, például INDEX és MATCH az EXACT funkcióval, vagy a LOOKUP és EXACT funkciókat.

Például a következő adattartománnyal rendelkezem, amely azonosító oszlop tartalmaz nagy- vagy kisbetűkkel ellátott szöveges karakterláncot, most vissza akarom adni az adott azonosítószám megfelelő matematikai pontszámát.

1. lépés: Alkalmazza bármelyik képletet, és töltse ki a többi cellába

Kérjük, másolja és illessze be az alábbi képletek bármelyikét egy üres cellába, ahol meg szeretné kapni az eredményt. Ezután jelölje ki a képlet cellát, húzza le a kitöltő fogantyút azokhoz a cellákhoz, amelyekbe ki szeretné tölteni a képletet.

1 képlet: A képlet beillesztése után nyomja meg a gombot Ctrl + Shift + Enter kulcsok.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

2 képlet: A képlet beillesztése után nyomja meg a gombot belép kulcs.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Eredmény:

Ezután megkapja a megfelelő eredményeket, amelyekre szüksége van. Lásd a képernyőképet:

Megjegyzések:

  • A fenti képletben:
    • A2: A10 az az oszlop, amely a keresni kívánt konkrét értékeket tartalmazza;
    • F2 a keresési érték;
    • C2: C10 az az oszlop, ahonnan az eredmény visszatér.
  • Ha több egyezés található, ez a képlet mindig az utolsó egyezést adja vissza.

2.3 VLOOKUP értékek jobbról balra az Excelben

A VLOOKUP függvény mindig az adattartomány bal szélső oszlopában keres egy értéket, és a megfelelő értéket adja vissza a jobb oldali oszlopból. Ha fordított VLOOKUP-t szeretne végrehajtani, ami azt jelenti, hogy egy adott értéket keres a jobb oldali oszlopban, és visszaadja a megfelelő értéket a bal oldali oszlopban az alábbi képernyőképen látható módon:

Kattintson a részletek részletes ismertetésére erről a feladatról ...


2.4 Keresse meg a második, n-edik vagy utolsó egyező értéket az Excelben

Általában, ha a Vlookup funkció használatakor több egyező értéket talál, csak az első egyeztetett rekord kerül visszaadásra. Ebben a részben arról fogok beszélni, hogyan kaphatom meg a második, n-edik vagy utolsó egyező értéket egy adattartományban.

 2.4.1 VLOOKUP, és adja vissza a második vagy n-edik egyező értéket

Tegyük fel, hogy az A oszlopban van egy névlista, a B oszlopban pedig az általuk megvásárolt képzés. Most az adott ügyfél által megvásárolt 2. vagy n-edik képzést keresi. Lásd a képernyőképet:

Itt a VLOOKUP függvény nem feltétlenül oldja meg közvetlenül ezt a feladatot. De alternatívaként használhatja az INDEX funkciót.

1. lépés: Alkalmazza és töltse ki a képletet más cellákra

Például, hogy a megadott feltételek alapján megkapja a második egyező értéket, alkalmazza a következő képletet egy üres cellába, és nyomja meg a Ctrl + Shift + Enter a kulcsokat össze, hogy elérjük az első eredményt. Ezután válassza ki a képlet cellát, húzza le a kitöltő fogantyút azokhoz a cellákhoz, amelyekbe ki szeretné tölteni a képletet.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Eredmény:

Most a megadott nevek alapján az összes második egyező érték egyszerre megjelenik.

Jegyzet: A fenti képletben:

  • A2: A14 a tartomány a kereséshez szükséges összes értékkel;
  • B2: B14 az egyező értékek tartománya, ahonnan vissza kíván térni;
  • E2 a keresési érték;
  • 2 jelzi a második egyező értéket, amelyet szeretne kapni, a harmadik egyező érték visszaadásához csak módosítania kell 3-ra.
 2.4.2 VLOOKUP, és adja vissza az utolsó egyező értéket

Ha vlookup-ot szeretne adni, és visszaadja az utolsó egyező értéket, az alábbi képernyőképen látható, akkor ezt VLOOKUP és az utolsó egyező érték visszaadása Az oktatóprogram segíthet az utolsó egyező érték részleteinek meghatározásában.


2.5 VLOOKUP két megadott érték vagy dátum közötti egyező értékek

Néha érdemes lehet két érték vagy dátum között értékeket keresni, és visszaadni a megfelelő eredményeket az alábbi képernyőképen látható módon. Ebben az esetben a KERESÉS funkciót használhatja a VLOOKUP függvény helyett egy rendezett táblázat esetén.

 2.5.1 VLOOKUP két megadott érték vagy dátum közötti egyező értékek képlettel

1. lépés: Rendezze el az adatokat, és alkalmazza a következő képletet

Az eredeti táblázatnak rendezett adattartománynak kell lennie. Ezután másolja vagy írja be a következő képletet egy üres cellába. Ezután húzza a kitöltő fogantyút a képlet más szükséges cellákba való kitöltéséhez.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Eredmény:

És most megkapja az összes egyező rekordot az adott érték alapján, lásd a képernyőképet:

Megjegyzések:

  • A fenti képletben:
    • A2: A6 a kisebb értékek tartománya;
    • B2: B6 a nagyobb számok tartománya;
    • E2 az a keresési érték, amelynek megfelelő értékét meg szeretné kapni;
    • C2: C6 az az oszlop, amelyből egy megfelelő értéket szeretne visszaadni.
  • Ez a képlet használható két dátum közötti egyező értékek kinyerésére is, amint az alábbi képernyőképen látható:
 2.5.2 VLOOKUP értékek egyeztetése két megadott érték vagy dátum között egy praktikus funkcióval

Ha nehéznek találja megjegyezni és megérteni a fenti képletet, itt bemutatok egy egyszerű eszközt – Kutools az Excel számára, Annak KERESÉS két érték között funkcióval könnyedén visszaadhatja a megfelelő tételt a két érték vagy dátum közötti konkrét érték vagy dátum alapján.

  1. Kattints Kutools > Szuper keresés > KERESÉS két érték között a funkció engedélyezéséhez.
  2. Ezután adja meg a műveleteket a párbeszédpanelen az adatok alapján.
Megjegyzések: A funkció használatához le kell töltenie Kutools for Excel 30 napos ingyenes próbaverzióval először.


2.6 Helyettesítő karakterek használata részleges egyezésekhez a VLOOKUP funkcióban

Az Excelben a helyettesítő karakterek a VLOOKUP függvényen belül használhatók, amely lehetővé teszi a keresési érték részleges egyezését. Használhatja például a VLOOKUP funkciót, hogy a keresési érték egy része alapján egyező értéket adjon vissza egy táblázatból.

Tételezzük fel, hogy van egy sor adatom, amint az az alábbi képernyőképen látható. Most a keresztnév (nem a teljes név) alapján szeretném kivonni a pontszámot. Hogyan oldhatná meg ezt a feladatot az Excel programban?

1. lépés: Alkalmazza és töltse ki a képletet más cellákra

Kérjük, másolja vagy írja be a következő képletet egy üres cellába, majd húzza a kitöltő fogantyút a képlet más szükséges cellákba való kitöltéséhez:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Eredmény:

És az összes egyező pontszámot visszaküldtük az alábbi képernyőképen látható módon:

Jegyzet: A fenti képletben:

  • E2 & ”*” a részleges matematika kritériuma. Ez azt jelenti, hogy bármilyen értéket keres, amely az E2 cellában lévő értékkel kezdődik. (A helyettesítő karakter*” bármely karaktert vagy karaktereket jelöl)
  • A2: C11 az az adattartomány, ahol az egyező értéket keresni kívánja;
  • 3 azt jelenti, hogy visszaadja a megfelelő értéket az adattartomány 3. oszlopából;
  • Hamis a pontos matematikát jelzi. (Ha helyettesítő karaktereket használ, a függvény utolsó argumentumát FALSE-ra vagy 0-ra kell állítania, hogy engedélyezze a pontos egyezési módot a VLOOKUP függvényben.)
tippek:
  • Egy adott értékre végződő egyező értékek megkereséséhez és visszaadásához tegye az érték elé a „*” helyettesítő karaktert. Kérjük, alkalmazza ezt a képletet:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

  • Az egyező érték megkereséséhez és visszaadásához a szöveges karakterlánc egy része alapján, függetlenül attól, hogy a megadott szöveg a szövegsor elején, végén vagy közepén található, csak a cellahivatkozást vagy a szöveget két csillaggal (*) kell körülírnia. mindkét oldalon. Kérem, tegye ezt a képletet
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


2.7 VLOOKUP értékek egy másik munkalapról

Általában előfordulhat, hogy egynél több munkalappal kell dolgoznia, a VLOOKUP funkcióval egy másik munkalapról is kikereshetünk adatokat, ugyanúgy, mint egy munkalapon.

Például két munkalapja van, az alábbi képernyőképen: a megadott adatok megkereséséhez és visszaadásához tegye a következőket:

1. lépés: Alkalmazza és töltse ki a képletet más cellákra

Kérjük, írja be vagy másolja be az alábbi képletet egy üres cellába, ahová az egyező elemeket szeretné lekérni. Ezután húzza le a kitöltő fogantyút azokhoz a cellákhoz, amelyekben alkalmazni szeretné ezt a képletet.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Eredmény:

Szükség szerint megkapja a megfelelő eredményeket, lásd a képernyőképet:

Jegyzet: A fenti képletben:

  • A2 a keresési értéket képviseli;
  • 'Adatlap'!A2:C15 jelzi, hogy az Adatlap nevű munkalapon az A2:C15 tartományból kell keresni az értékeket; (Ha a munkalap neve szóközt vagy írásjelet tartalmaz, akkor a munkalap nevét idézőjelbe kell tenni, ellenkező esetben közvetlenül használhatja a munkalap nevét, pl. =VLOOKUP(A2,Adatlap!$A$2:$C$15,3,0) ).
  • 3 az oszlop száma, amely egyező adatokat tartalmaz, amelyekből vissza kíván térni;
  • 0 pontos egyezés végrehajtását jelenti.

2.8 VLOOKUP értékek egy másik munkafüzetből

Ez a szakasz a VLOOKUP függvény segítségével egy másik munkafüzetből való kikeresésről és az egyező értékek visszaadásáról szól.

Tegyük fel például, hogy van két munkafüzete. Az első munkafüzet tartalmazza a termékek listáját és a hozzájuk tartozó költségeket. A második munkafüzetben ki szeretné bontani a megfelelő költséget minden egyes termékhez az alábbi képernyőkép szerint.

1. lépés: Alkalmazza és töltse ki a képletet

Nyissa meg mindkét használni kívánt munkafüzetet, majd alkalmazza a következő képletet egy cellába, ahová az eredményt a második munkafüzetbe szeretné helyezni. Ezután húzza át és másolja a képletet a többi szükséges cellába

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Eredmény:

Megjegyzések:

  • A fenti képletben:
    • B2 a keresési értéket képviseli;
    • '[Terméklista.xlsx]1. lap'!A2:B6 azt jelzi, hogy az A2:B6 tartományból kell keresni a munkafüzet Terméklista Lap1 nevű lapján; (A munkafüzetre való hivatkozás szögletes zárójelben, a teljes munkafüzet + lap pedig szimpla idézőjelben van.)
    • 2 az oszlop száma, amely egyező adatokat tartalmaz, amelyekből vissza kíván térni;
    • 0 pontos egyezés visszaadását jelzi.
  • Ha a keresőmunkafüzet be van zárva, a keresőmunkafüzet teljes fájlútvonala megjelenik a képletben, az alábbi képernyőképen látható módon:

2.9 Üres vagy meghatározott szöveget ad vissza 0 vagy #N/A hiba helyett

Általában, ha a VLOOKUP függvényt használja egy megfelelő érték visszaadására, ha az egyező cella üres, akkor 0-t ad vissza. Ha pedig nem található a megfelelő érték, akkor #N/A hibaértéket kap, amint az a képernyőkép lent. Ha egy üres cellát vagy egy adott értéket szeretne megjeleníteni a 0 vagy a #N/A helyett, akkor ezt VLOOKUP 0 vagy N/A helyett üres vagy konkrét érték visszaadásához az oktatóanyag tehet egy szívességet.


Haladó VLOOKUP példák

3.1 Kétirányú keresés (VLOOKUP sorban és oszlopban)

Néha előfordulhat, hogy kétdimenziós keresést kell végrehajtania, ami azt jelenti, hogy egyszerre kell keresni egy értéket a sorban és az oszlopban. Például, ha a következő adattartománnyal rendelkezik, és előfordulhat, hogy egy adott termék értékét egy adott negyedévben kell lekérnie. Ez a rész egy képletet mutat be ennek a feladatnak az Excelben való kezeléséhez.

Az Excelben a VLOOKUP és a MATCH függvények kombinációjával végezhet kétirányú keresést.

Alkalmazza a következő képletet egy üres cellába, majd nyomja meg a gombot belép kulcs az eredmény eléréséhez.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

Jegyzet: A fenti képletben:

  • G2 az a keresési érték abban az oszlopban, amely alapján a megfelelő értéket szeretné megkapni;
  • A2: E7 az adattábla, amelyből nézni fog;
  • H1 a keresési érték abban a sorban, amely alapján a megfelelő értéket szeretné megkapni;
  • A2: E2 az oszlopfejlécek cellái;
  • HAMIS jelzi, hogy pontos egyezést kapjunk.

3.2 VLOOKUP egyező érték két vagy több feltétel alapján

Könnyen megkeresheti az egyező értéket egy kritérium alapján, de ha kettő vagy több feltétele van, mit tehet?

 3.2.1 VLOOKUP egyező érték két vagy több feltétel alapján képletekkel

Ebben az esetben az Excel KERESÉSE vagy MATCH és INDEX funkciói segíthetnek a feladat gyors és egyszerű megoldásában.

Például az alábbi adattáblázat áll rendelkezésemre, hogy az adott termék és méret alapján kiegyenlített ár visszatéréséhez a következő képletek segíthetnek.

1. lépés: Alkalmazza bármelyik képletet

1 képlet: A képlet beillesztése után nyomja meg a gombot belép kulcs.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

2 képlet: A képlet beillesztése után nyomja meg a gombot Ctrl + Shift + Enter kulcsok.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Eredmény:

Megjegyzések:

  • A fenti képletekben:
    • A2: A12 = G1 azt jelenti, hogy megkeresi a G1 kritériumait az A2:A12 tartományban;
    • B2: B12 = G2 azt jelenti, hogy megkeresi a G2 kritériumait a B2:B12 tartományban;
    • D2: D12 is azt a tartományt, amelyből a megfelelő értéket vissza szeretné adni.
  • Ha kettőnél több feltétele van, csak össze kell kapcsolnia a többi feltételt a képletbe, például:
    =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
    =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
 3.2.2 VLOOKUP egyező érték két vagy több kritérium alapján egy intelligens funkcióval

Nehéz lehet megjegyezni a fenti összetett képleteket, amelyeket többször kell alkalmazni, ami lelassíthatja a munka hatékonyságát. Azonban, Kutools az Excel számára kínál Többfeltételes keresés funkció, amely lehetővé teszi, hogy néhány kattintással visszaadja a megfelelő eredményt egy vagy több feltétel alapján.

  1. Kattints Kutools > Szuper keresés > Többfeltételes keresés a funkció engedélyezéséhez.
  2. Ezután adja meg a műveleteket a párbeszédpanelen az adatok alapján.
Megjegyzések: A funkció használatához le kell töltenie Kutools for Excel 30 napos ingyenes próbaverzióval először.


3.3 VLOOKUP több érték visszaadásához egy vagy több feltétellel

Az Excelben a VLOOKUP függvény értéket keres, és csak akkor adja vissza az első egyező értéket, ha több megfelelő érték is található. Néha előfordulhat, hogy vissza szeretné adni az összes megfelelő értéket egy sorban, egy oszlopban vagy egyetlen cellában. Ez a szakasz arról szól, hogyan lehet több egyező értéket visszaadni egy vagy több feltétellel egy munkafüzetben.

 3.3.1 Az összes egyező érték megkeresése egy vagy több feltétel alapján vízszintesen

Feltéve, hogy van egy adattáblázata, amely országot, várost és neveket tartalmaz az A1:C14 tartományban, és most vízszintesen szeretne visszaadni minden olyan nevet, amely az "USA"-ból származik, ahogy az alábbi képernyőképen látható. A feladat megoldásához kérem kattintson ide, hogy lépésről lépésre megkapja az eredményt.

 3.3.2 VLOOKUP minden egyező érték egy vagy több feltétel alapján függőlegesen

Ha meg kell keresnie, és az összes egyező értéket függőlegesen kell visszaadnia meghatározott kritériumok alapján, az alábbi képernyőképen látható módon, Kérjük, kattintson ide a megoldás részleteinek megtekintéséhez.

 3.3.3 Az összes egyező érték egy vagy több feltétel alapján történő KERESÉSE egyetlen cellába

Ha több egyező értéket szeretne keresni, és egyetlen cellába szeretne visszaadni meghatározott elválasztóval, a TEXTJOIN új funkciója segíthet gyorsan és egyszerűen megoldani ezt a feladatot.

Megjegyzések:


3.4 VLOOKUP az egyező cella teljes sorának visszaadásához

Ebben a részben arról fogok beszélni, hogyan lehet lekérni egy egyező érték teljes sorát a VLOOKUP függvény használatával.

1. lépés: Alkalmazza és töltse ki a következő képletet

Kérjük, másolja vagy gépelje be az alábbi képletet egy üres cellába, ahol ki szeretné adni az eredményt, majd nyomja meg a gombot belép gombot, hogy megkapja az első értéket. Ezután húzza jobbra a képletcellát, amíg a teljes sor adatai meg nem jelennek.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Eredmény:

Most láthatja, hogy a teljes soradat visszakerült. Lásd a képernyőképet:
doc vlookup függvény 50 1

Jegyzet: a fenti képletben:

  • F2 az a keresési érték, amely alapján a teljes sort vissza kívánja adni;
  • A1: D12 az az adattartomány, amelyből a keresési értéket keresni kívánja;
  • A1 az adattartományon belüli első oszlopszámot jelzi;
  • HAMIS pontos keresést jelez.

Tipp:

  • Ha az egyező érték alapján több sor is található, az összes megfelelő sor visszaadásához használja az alábbi képletet, majd nyomja meg a Ctrl + Shift + Enter a kulcsokat össze, hogy elérjük az első eredményt. Ezután húzza jobbra a kitöltő fogantyút. Ezután húzza le a kitöltő fogantyút a cellák között, hogy megkapja az összes egyező sort. Tekintse meg az alábbi bemutatót:
    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
    doc vlookup függvény 51 2

3.5 Beágyazott VLOOKUP Excelben

Néha előfordulhat, hogy olyan értékeket kell megkeresnie, amelyek több táblán keresztül kapcsolódnak egymáshoz. Ebben az esetben több VLOOKUP függvényt egymásba ágyazhat, hogy megkapja a végső értéket.

Például van egy munkalapom, amely két külön táblázatot tartalmaz. Az első táblázat felsorolja az összes terméknevet a megfelelő értékesítővel együtt. A második táblázat felsorolja az egyes értékesítők összesített eladásait. Most, ha meg szeretné keresni az egyes termékek eladásait, amint az a következő képernyőképen látható, beágyazhatja a VLOOKUP funkciót a feladat végrehajtásához.
doc vlookup függvény 53 1

A beágyazott VLOOKUP függvény általános képlete a következő:

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

Jegyzet:

  • keresési_érték az az érték, amelyet keres;
  • Táblázat_tömb1, Táblázat_tömb2 azok a táblák, amelyekben a keresési érték és a visszatérési érték létezik;
  • col_index_num1 az első táblázat oszlopszámát jelzi a köztes közös adatok megtalálásához;
  • col_index_num2 jelzi a második táblázat oszlopának számát, amelyhez a megfelelő értéket kívánja visszaadni;
  • 0 pontos egyezésre szolgál.

1. lépés: Alkalmazza és töltse ki a következő képletet

Alkalmazza a következő képletet egy üres cellába, majd húzza le a kitöltő fogantyút azokhoz a cellákhoz, amelyekhez ezt a képletet alkalmazni kívánja.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Eredmény:

Most az alábbi képernyőképen látható eredményt kapja:

Jegyzet: a fenti képletben:

  • G3 tartalmazza a keresett értéket;
  • A3: B7, D3: E7 azok a táblatartományok, amelyekben a keresési érték és a visszatérési érték létezik;
  • 2 az oszlop száma abban a tartományban, amelyből a megfelelő értéket vissza kell adni.
  • 0 a VLOOKUP pontos matematikát jelzi.

3.6 Ellenőrizze, hogy létezik-e érték egy másik oszlopban lévő listaadatok alapján

A VLOOKUP függvény abban is segíthet, hogy ellenőrizze, léteznek-e értékek egy másik oszlop adatlistája alapján. Például, ha meg szeretné keresni a neveket a C oszlopban, és csak az Igen vagy a Nem értéket szeretné visszaadni, ha a név megtalálható vagy sem az A oszlopban, ahogy az alábbi képernyőképen látható.
doc vlookup függvény 56 1

1. lépés: Alkalmazza és töltse ki a következő képletet

Alkalmazza a következő képletet egy üres cellába, majd húzza le a kitöltő fogantyút azokhoz a cellákhoz, amelyekbe ki szeretné tölteni ezt a képletet.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Eredmény:

És megkapja a szükséges eredményt, lásd a képernyőképet:

Jegyzet: a fenti képletben:

  • C2 az ellenőrizni kívánt keresési érték;
  • A2: A10 a tartomány listája, ahonnan ellenőrizhető, hogy a keresési értékek megtalálhatók-e vagy sem;
  • HAMIS jelzi, hogy pontos egyezést kapjunk.

3.7 VLOOKUP, és összegezze az összes egyező értéket a sorokban vagy oszlopokban

Amikor numerikus adatokkal dolgozik, előfordulhat, hogy egy táblázatból kell kivonnia az egyező értékeket, és össze kell adnia a számokat több oszlopban vagy sorban. Ez a rész bemutat néhány képletet, amelyek segíthetnek ennek a feladatnak a végrehajtásában.

 3.7.1 VLOOKUP és összegezze az összes egyező értéket egy sorban vagy több sorban

Tegyük fel, hogy van egy terméklistája több hónapig tartó eladásokkal, amint az a következő képernyőképen látható. Most össze kell adnia az összes rendelést minden hónapban az adott termékek alapján.

1. lépés: Alkalmazza és töltse ki a következő képletet

Kérjük, másolja vagy írja be a következő képletet egy üres cellába, majd nyomja meg a gombot Ctrl + Shift + Enter a kulcsokat össze, hogy elérjük az első eredményt. Ezután húzza le a kitöltő fogantyút a képlet más szükséges cellákba másolásához.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Eredmény:

Az első egyező érték sorában lévő összes értéket összeadtuk, lásd a képernyőképet:

Jegyzet: a fenti képletben:

  • H2 a keresett értéket tartalmazó cella;
  • A2: F9 az adattartomány (oszlopfejlécek nélkül), amely tartalmazza a keresési értéket és az egyező értékeket;
  • 2,3,4,5,6 {} a tartomány teljes összegének kiszámításához használt oszlopszámok;
  • HAMIS pontos egyezést jelez.

Tipp: Ha az összes egyezést több sorban szeretné összegezni, használja a következő képletet:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 VLOOKUP és összegezze az összes egyező értéket egy oszlopban vagy több oszlopban

Ha az alábbi képernyőképen látható módon szeretné összeadni az adott hónapok összértékét. A normál VLOOKUP függvény nem biztos, hogy segít, itt a SUM, INDEX és MATCH függvényeket együtt kell alkalmazni egy képlet létrehozásához.

1. lépés: Alkalmazza a következő képletet

Alkalmazza az alábbi képletet egy üres cellába, majd húzza le a kitöltő fogantyút a képlet más cellákba másolásához.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Eredmény:

Most egy oszlopban az adott hónapon alapuló első egyező értékeket összegeztük, lásd a képernyőképet:

Jegyzet: a fenti képletben:

  • H2 a keresett értéket tartalmazó cella;
  • B1: F1 a keresési értéket tartalmazó oszlopfejlécek;
  • B2: F9 az az adattartomány, amely az összegezni kívánt számértékeket tartalmazza.

Tipp: A VLOOKUP és az összes egyező érték több oszlopban történő összegzéséhez használja a következő képletet:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 VLOOKUP és összegezze az első illesztett vagy az összes illesztett értéket egy hatékony szolgáltatással

Lehet, hogy a fenti képleteket nehéz megjegyezni, ebben az esetben ajánlok egy hatékony funkciót - Keresés és összeg of Kutools az Excel számára, ezzel a funkcióval a lehető legegyszerűbben megkeresheti és összeadhatja az első egyező vagy az összes egyező értéket a sorokban vagy oszlopokban.

  1. Kattints Kutools > Szuper keresés > KERESÉS és Összeg a funkció engedélyezéséhez.
  2. Ezután adja meg a műveleteket a párbeszédpanelen az igényeinek megfelelően.
Megjegyzések: A funkció használatához le kell töltenie Kutools for Excel 30 napos ingyenes próbaverzióval először.
 3.7.4 VLOOKUP és az összes egyező érték összegzése a sorokban és az oszlopokban egyaránt

Ha összegezni szeretné az értékeket, amikor meg kell egyeznie az oszlopnak és a sornak is, például megkapja a pulóver termék teljes értékét a március hónapban, az alábbi képernyőképen.

Itt a SUMPRODCT függvényt használhatja a feladat végrehajtásához.

Alkalmazza a következő képletet egy cellába, majd nyomja meg a gombot belép kulcs az eredmény eléréséhez, lásd a képernyőképet:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Jegyzet: A fenti képletben:

  • B2: F9 az az adattartomány, amely az összegezni kívánt számértékeket tartalmazza;
  • B1: F1 az az oszlopfejléc, amely tartalmazza az összegezni kívánt keresési értéket;
  • I2 a keresett érték a keresett oszlopfejléceken belül;
  • A2: A9 az a sorfejléc, amely tartalmazza az összegezni kívánt keresési értéket;
  • H2 a keresési érték a keresett sorfejlécekben.

3.8 VLOOKUP két tábla egyesítéséhez kulcsoszlopok alapján

Napi munkája során az adatok elemzése során előfordulhat, hogy minden szükséges információt egyetlen táblázatba kell gyűjtenie egy vagy több kulcsoszlop alapján. Ennek a feladatnak a végrehajtásához használhatja az INDEX és a MATCH függvényeket a VLOOKUP függvény helyett.

 3.8.1 VLOOKUP két tábla egyesítéséhez egy kulcsoszlop alapján

Például van két táblája, az első tábla a termékek és nevek adatait tartalmazza, a második tábla pedig a termékek és rendelések adatait. Most ezt a két táblát szeretné egyesíteni úgy, hogy a közös termékoszlopot egy táblázatba párosítja.

1. lépés: Alkalmazza és töltse ki a következő képletet

Alkalmazza a következő képletet egy üres cellába. Ezután húzza le a kitöltő fogantyút azokhoz a cellákhoz, amelyekben alkalmazni szeretné ezt a képletet

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Eredmény:

Most kap egy összevont táblát, amelyben a rendelési oszlop csatlakozik az első táblázathoz a kulcs oszlopadatok alapján.

Jegyzet: A fenti képletben:

  • A2 az a keresési érték, amelyet keres;
  • F2: F8 azon adatok tartománya, amelyeknél vissza kívánja adni az egyező értékeket;
  • E2: E8 a keresési tartomány, amely tartalmazza a keresési értéket.
 3.8.2 VLOOKUP két tábla egyesítéséhez több kulcsoszlop alapján

Ha az egyesíteni kívánt két táblázat több kulcsoszlopot tartalmaz, akkor a táblázatok e közös oszlopok alapján történő egyesítéséhez kövesse az alábbi lépéseket.

Az általános képlet:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

Jegyzet:

  • keresési táblázat az adattartomány tartalmazza-e a keresési adatokat és az egyező rekordokat;
  • keresési_érték1 az első kritérium, amelyet keres;
  • keresési_tartomány1 az adatlista tartalmazza-e az első kritériumot;
  • keresési_érték2 a második kritérium, amelyet keres;
  • keresési_tartomány2 az adatlista tartalmazza-e a második kritériumot;
  • visszatérési_oszlop_szám azt az oszlopszámot jelzi a keresési_táblázatban, amelyhez az egyező értéket kívánja visszaadni.

1. lépés: Alkalmazza a következő képletet

Alkalmazza az alábbi képletet egy üres cellába, ahová az eredményt el szeretné helyezni, majd nyomja meg a gombot Ctrl + Shift + Enter billentyűk együtt kapják meg az első egyező értéket, lásd a képernyőképet:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

2. lépés: Töltse ki a képletet más cellákkal

Ezután jelölje ki az első képletcellát, és húzza a kitöltő fogantyúját a képlet más cellákba történő másolásához, ha szüksége van rá:

tippek: Az Excel 2016 vagy újabb verzióiban használhatja a Power Query két vagy több tábla egyesítése a kulcsoszlopok alapján. Kérjük, kattintson a részletekről lépésről lépésre.

3.9 VLOOKUP egyező értékek több munkalapon

Szüksége volt valaha is VLOOKUP-ra több munkalapon az Excelben? Ha például három munkalapja van adattartományokkal, és bizonyos értékeket szeretne lekérni ezekről a lapokról a kritériumok alapján, kövesse a lépésről lépésre bemutatott útmutatót. VLOOKUP Értékek több munkalapon ezt a feladatot.


A VLOOKUP egyeztetett értékek megőrzik a cellák formázását

Az egyező értékek megkeresésekor az eredeti cellaformázás, például a betűszín, a háttérszín, az adatformátum stb. nem marad meg. A cella- vagy adatformátum megtartása érdekében ez a rész néhány trükköt mutat be a feladatok megoldásához.

4.1 VLOOKUP egyező érték, és megtartja a cella színét, a betűtípus formázását

Mint mindannyian tudjuk, a normál VLOOKUP függvény csak egy másik adattartományból tudja lekérni a megfelelő értéket. Előfordulhatnak azonban olyan esetek, amikor a megfelelő értéket szeretné megkapni a cellaformázással, például a kitöltési színnel, a betűtípus színével és a betűstílussal együtt. Ebben a részben azt tárgyaljuk, hogyan lehet lekérni az egyező értékeket, miközben megőrzi a forrásformázást az Excelben.

A következő lépésekkel keresse meg és adja vissza a megfelelő értéket a cellák formázásával együtt:

1. lépés: Másolja be az 1-es kódot a Sheet Code Module-ba

  1. A munkalapon a VLOOKUP kívánt adatokat tartalmazza, kattintson jobb gombbal a lapfülre, és válassza ki Kód megtekintése a helyi menüből. Lásd a képernyőképet:
  2. A nyitottban Microsoft Visual Basic for Applications ablakba, kérjük, másolja az alábbi VBA kódot a Kód ablakba.
  3. VBA-kód 1: VLOOKUP, hogy megkapja a cella formázását a keresési értékkel együtt
  4. Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice
        Dim I As Long
        Dim xKeys As Long
        Dim xDicStr As String
        On Error Resume Next
        Application.ScreenUpdating = False
        xKeys = UBound(xDic.Keys)
        If xKeys >= 0 Then
            For I = 0 To UBound(xDic.Keys)
                xDicStr = xDic.Items(I)
                If xDicStr <> "" Then
                    Range(xDic.Keys(I)).Interior.Color = _
                    Range(xDic.Items(I)).Interior.Color
                    Range(xDic.Keys(I)).Font.FontStyle = _
                    Range(xDic.Items(I)).Font.FontStyle
                    Range(xDic.Keys(I)).Font.Size = _
                    Range(xDic.Items(I)).Font.Size
                    Range(xDic.Keys(I)).Font.Color = _
                    Range(xDic.Items(I)).Font.Color
                    Range(xDic.Keys(I)).Font.Name = _
                    Range(xDic.Items(I)).Font.Name
                    Range(xDic.Keys(I)).Font.Underline = _
                    Range(xDic.Items(I)).Font.Underline
                Else
                    Range(xDic.Keys(I)).Interior.Color = xlNone
                End If
            Next
            Set xDic = Nothing
        End If
        Application.ScreenUpdating = True
    End Sub
    

2. lépés: Másolja be a 2-es kódot a Modul ablakba

  1. Még a Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modulok, majd másolja az alábbi VBA kódot a Modul ablakba.
  2. VBA-kód 2: VLOOKUP, hogy megkapja a cella formázását a keresési értékkel együtt
  3. Public xDic As New Dictionary
    Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
        Dim xFindCell As Range
        On Error Resume Next
        Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
        If xFindCell Is Nothing Then
            LookupKeepFormat = ""
            xDic.Add Application.Caller.Address, ""
        Else
            LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
            xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
        End If
    End Function
    

3. lépés: Válassza ki a VBAproject opciót

  1. A fenti kódok beillesztése után kattintson a gombra Eszközök > Referenciák a Microsoft Visual Basic for Applications ablak. Ezután ellenőrizze a Microsoft Scripting Runtime jelölőnégyzet a Referenciák - VBAProject párbeszédablak. Képernyőképek megtekintése:
  2. Ezután kattintson a gombra OK a párbeszédpanel bezárásához, majd mentse és zárja be a kód ablakot.

4. lépés: Írja be az eredmény eléréséhez szükséges képletet

  1. Most menjen vissza a munkalaphoz, és alkalmazza a következő képletet. Ezután húzza le a kitöltő fogantyút, hogy megkapja az összes eredményt a formázással együtt. Lásd a képernyőképet:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

Jegyzet: a fenti képletben:

  • E2 az az érték, amelyet meg fog keresni;
  • A1: C10 az asztal tartománya;
  • 3 annak a táblának az oszlopszáma, amelyből az egyező értéket le kívánja kérni.

4.2 Tartsa meg a dátumformátumot a VLOOKUP által visszaadott értékből

Ha a VLOOKUP függvényt dátumformátumú érték megkeresésére és visszaadására használja, a visszaadott eredmény számként jelenhet meg. A dátumformátum megtartásához a visszaadott eredményben a VLOOKUP függvényt a SZÖVEG függvénybe kell zárni.

1. lépés: Alkalmazza és töltse ki a következő képletet

Alkalmazza az alábbi képletet egy üres cellába. Ezután húzza a kitöltő fogantyút a képlet más cellákba másolásához.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Eredmény:

Az összes egyező dátumot visszaküldtük az alábbi képernyőképen látható módon:

Jegyzet: A fenti képletben:

  • E2 a keresési érték;
  • A2: C9 a keresési tartomány;
  • 3 az az oszlopszám, amelynek értéket vissza kell adni;
  • HAMIS jelzi a pontos egyezés elérését;
  • éééé / hh / nn a megtartani kívánt dátumformátum.

4.3 Cella megjegyzésének visszaadása a VLOOKUP-ból

Szüksége volt valaha az egyező cellaadatok és a hozzájuk tartozó megjegyzések lekérésére az Excel VLOOKUP használatával, amint az a következő képernyőképen látható? Ha igen, az alább megadott, felhasználó által definiált függvény segíthet a feladat végrehajtásában.

1. lépés: Másolja a kódot egy modulba

  1. Tartsa lenyomva a ALT + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.
  2. Kattints betétlap > Modulok, majd másolja és illessze be a következő kódot a Modul ablakba.
    VBA kód: Vlookup és egyező érték visszaadása cellamegjegyzéssel:
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    'Updateby Extendoffice
        Application.Volatile
        Dim xRet As Variant 'could be an error
        Dim xCell As Range
        xRet = Application.Match(LookVal, FTable.Columns(1), FType)
        If IsError(xRet) Then
            VlookupComment = "Not Found"
        Else
            Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
            VlookupComment = xCell.Value
            With Application.Caller
                If Not .Comment Is Nothing Then
                    .Comment.Delete
                End If
                If Not xCell.Comment Is Nothing Then
                    .AddComment xCell.Comment.Text
                End If
            End With
        End If
    End Function
  3. Ezután mentse el és zárja be a kód ablakot.

2. lépés: Írja be a képletet az eredmény eléréséhez

  1. Most írja be a következő képletet, és húzza a kitöltő fogantyút a képlet más cellákba másolásához. Egyszerre adja vissza az egyező értékeket és a megjegyzéseket, lásd a képernyőképet:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

Jegyzet: A fenti képletben:

  • D2 az a keresési érték, amelynek megfelelő értékét vissza kívánja adni;
  • A2: B9 a használni kívánt adattábla;
  • 2 az oszlop száma, amely tartalmazza a visszaadni kívánt egyező értéket;
  • HAMIS jelzi, hogy pontos egyezést kapjunk.

4.4 VLOOKUP számok szövegként tárolva

Például van egy adattartományom, ahol az eredeti táblázatban szereplő azonosító szám számformátumban van, és a keresési cellákban lévő azonosítószám szövegként van tárolva, #N/A hibával találkozhat a normál VLOOKUP funkció használatakor. Ebben az esetben a helyes információ lekéréséhez a SZÖVEG és az ÉRTÉK függvényeket becsomagolhatja a VLOOKUP függvénybe. Az alábbi képlet ennek eléréséhez:

1. lépés: Alkalmazza és töltse ki a következő képletet

Alkalmazza a következő képletet egy üres cellába, majd húzza le a kitöltő fogantyút a képlet másolásához.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Eredmény:

Most a megfelelő eredményeket kapja az alábbi képernyőképen látható módon:

Megjegyzések:

  • A fenti képletben:
    • D2 az a keresési érték, amelynek megfelelő értékét vissza kívánja adni;
    • A2: B8 a használni kívánt adattábla;
    • 2 az oszlop száma, amely tartalmazza a visszaadni kívánt egyező értéket;
    • 0 jelzi, hogy pontos egyezést kapjunk.
  • Ez a képlet akkor is jól működik, ha nem biztos abban, hogy hol vannak számok és hol szövegek.

Tartalomjegyzék