Volt már olyan nagy táblázata, amely adatokat tartalmaz Excelben^(Excel) , és szüksége van egy egyszerű módra konkrét információk szűrésére és kinyerésére? Ha megtanulja, hogyan kell használni a VLOOKUP programot az Excelben^(Excel) , akkor ezt egyetlen hatékony Excel - függvénnyel is megteheti.

Az Excel VLOOKUP függvénye sok embert megijeszt, mert sok paramétere van, és többféleképpen is használható. Ebből a cikkből megtudhatja, hogy miként használhatja a VLOOKUP alkalmazást az Excelben^(Excel) , és megtudhatja, miért olyan erős a funkció.

VLOOKUP paraméterek Excelben^{(VLOOKUP Parameters In Excel)}

Amikor elkezdi beírni a =VLOOKUP(Excel bármely cellájába , megjelenik egy felugró ablak, amely az összes elérhető függvényparamétert mutatja.

Vizsgáljuk meg ezeket a paramétereket és azok jelentését.

• lookup_value : A táblázatból keresett érték
• table_array : A munkalap celláinak tartománya, amelyben keresni kíván
• col_index_num : Az az oszlop, ahonnan az eredményt le szeretné venni
• [range_lookup] : Egyezési mód ( TRUE = hozzávetőleges, FALSE = pontos)

Ezzel a négy paraméterrel sok különböző, hasznos adatkeresést végezhet nagyon nagy adatkészleteken belül.

Egy egyszerű VLOOKUP Excel példa^{(A Simple VLOOKUP Excel Example)}

A VLOOKUP nem tartozik azon alapvető Excel-funkciók^{(the basic Excel functions)} közé, amelyeket esetleg megtanult, ezért nézzünk meg egy egyszerű példát a kezdéshez.

A következő példában egy nagy táblázatot használunk az egyesült ^(United)államokbeli^(States) iskolák SAT-pontszámairól . Ez a táblázat több mint 450 iskolát tartalmaz, valamint az olvasás, a matematika és az írás  egyéni SAT -pontszámait. ^(SAT)Nyugodtan^(Feel) töltse le a követéshez. Van egy külső kapcsolat, ami lekéri az adatokat, így a fájl megnyitásakor figyelmeztetést kapsz, de ez biztonságos.

Nagyon időigényes lenne ilyen nagy adathalmazban keresni, hogy megtalálja az Önt érdeklő iskolát.

Ehelyett létrehozhat egy egyszerű űrlapot a táblázat oldalán lévő üres cellákban. A keresés végrehajtásához csak hozzon létre egy mezőt az Iskola^(School) számára , és három további mezőt az olvasási, matematikai és írási pontszámok számára.

Ezután a VLOOKUP^(VLOOKUP) függvényt kell használnia az Excelben^(Excel) , hogy ez a három mező működjön. Az Olvasás^(Reading) mezőben hozza létre a VLOOKUP függvényt az alábbiak szerint:

1. Írja be a =VLOOKUP(
2. Válassza ki az Iskola^(School) mezőt, amely ebben a példában I2 . Írjon be egy vesszőt.
3. Jelölje ki a keresni kívánt adatokat tartalmazó cellák teljes tartományát. Írjon be egy vesszőt.

Amikor kiválasztja a tartományt, kiindulhat a kereséshez használt oszlopból (jelen esetben az iskolanév oszlopból), majd kijelölheti az összes többi adatot tartalmazó oszlopot és sort.

Megjegyzés^(Note) : Az Excel VLOOKUP funkciója csak a keresőoszloptól jobbra lévő cellák között tud keresni. Ebben a példában az iskolanév oszlopnak a keresett adatoktól balra kell lennie.

1. Ezután az olvasási^(Reading) pontszám lekéréséhez ki kell választania a 3. oszlopot a bal szélső kijelölt oszlopból. Tehát írjon be egy 3 -ast, majd írjon be egy másik vesszőt.
2. Végül írja be a FALSE -t a pontos egyezéshez, és zárja be a függvényt a ) gombbal .

A végső VLOOKUP függvénynek így kell kinéznie:

=VLOOKUP(I2,B2:G461,3,FALSE)

Amikor először megnyomja az Enter billentyűt , és befejezi a funkciót, észre fogja venni, hogy az Olvasás^(Reading) mezőben #N/A lesz látható .

Ennek az az oka, hogy az Iskola^(School) mező üres, és a VLOOKUP függvény nem talál semmit. Ha azonban megadja bármely középiskola nevét, amelyet meg szeretne keresni, akkor az olvasási^(Reading) pontszám megfelelő eredményeit láthatja az adott sorban.

Hogyan kezeljük a VLOOKUP-ot a kis- és nagybetűk megkülönböztetésével^{(How To Deal With VLOOKUP Being Case- Sensitive)}

Észreveheti, hogy ha nem ugyanabban a kis- és nagybetűben írja be az iskola nevét, mint ahogyan az az adatkészletben szerepel, akkor nem fog látni semmilyen eredményt.

Ennek az az oka, hogy a VLOOKUP függvény megkülönbözteti a kis- és nagybetűket. Ez bosszantó lehet, különösen egy nagyon nagy adatkészlet esetén, ahol a keresett oszlop nincs összhangban a dolgok nagybetűs írásmódjával.

Ennek megkerüléséhez kényszerítheti, hogy a keresett kifejezés kisbetűre váltson, mielőtt megnézné az eredményeket. Ehhez hozzon létre egy új oszlopot a keresett oszlop mellett. Írja be a függvényt:

=TRIM(LOWER(B2))

Ez kisbetűsre csökkenti az iskola nevét, és eltávolítja a felesleges karaktereket (szóközöket), amelyek a név bal vagy jobb oldalán találhatók.

Tartsa lenyomva a Shift billentyűt, és vigye az egérmutatót az első cella jobb alsó sarka fölé, amíg az két vízszintes vonalra nem változik. Kattintson duplán^(Double) az egérrel a teljes oszlop automatikus kitöltéséhez.

Végül, mivel a VLOOKUP megpróbálja a képletet használni, nem pedig a szöveget ezekben a cellákban, mindegyiket csak értékké kell konvertálnia. Ehhez másolja ki a teljes oszlopot, kattintson jobb gombbal az első cellára, és illessze be csak az értékeket.

Most, hogy az összes adatot megtisztította ebben az új oszlopban, kissé módosítsa a VLOOKUP függvényt az Excelben^(Excel) , hogy ezt az új oszlopot használja az előző helyett, és a keresési tartományt^{(the lookup range)} a B2 helyett C2-ről kezdi.

=VLOOKUP(I2,C2:G461,3,FALSE)

Most észre fogja venni, hogy ha mindig kisbetűvel írja be a keresést, mindig jó keresési eredményt kap.

Ez egy praktikus Excel-tipp^{(handy Excel tip)} , amellyel kiküszöbölheti azt a tényt, hogy a VLOOKUP megkülönbözteti a kis- és nagybetűket.

VLOOKUP Hozzávetőleges egyezés

Míg a cikk első részében leírt, pontos egyezésű LOOKUP példa meglehetősen egyszerű, a hozzávetőleges egyezés kissé összetettebb.

A hozzávetőleges egyezés a legjobb a számtartományok közötti kereséshez. Ennek megfelelő végrehajtásához a keresési tartományt megfelelően rendezni kell. A legjobb példa erre a VLOOKUP függvény, amely egy számjegynek megfelelő betűosztályzatot keres.

Ha egy tanárnak hosszú listája van a tanulók házi feladatainak osztályzatairól az év során, utolsó átlagolt oszloppal, jó lenne, ha az adott osztályzatnak megfelelő betűjegy automatikusan megjelenne.

Ez a VLOOKUP funkcióval lehetséges. Mindössze egy keresési táblázatra van szükség a jobb oldalon, amely tartalmazza az egyes numerikus pontszámok tartományának megfelelő betűosztályzatát.

Most a VLOOKUP funkció és egy hozzávetőleges egyezés segítségével megtalálhatja a megfelelő számtartománynak megfelelő betű fokozatot.

Ebben a VLOOKUP függvényben:

• lookup_value : F2, a végső átlagolt érdemjegy
• table_array : I2:J8, A betűosztályzat keresési tartománya
• index_column : 2, a keresőtábla második oszlopa
• [range_lookup] : IGAZ, hozzávetőleges egyezés

Miután befejezte a VLOOKUP funkciót a G2-ben, és megnyomta az Entert^(Enter) , kitöltheti a többi cellát az utolsó részben leírt módszerrel. Látni fogja az összes betűosztályzatot megfelelően kitöltve.

Vegye figyelembe, hogy az Excel VLOOKUP funkciója az osztályzati tartomány alsó végétől a hozzárendelt betűpontszámmal a következő betű pontszámának tartományának tetejéig keres.

Tehát a „C”-nek az alsó tartományhoz (75) rendelt betűnek kell lennie, a B-nek pedig a saját betűtartományának alsó (minimális) betűje. A VLOOKUP^(VLOOKUP) „megtalálja” a 60 (D) eredményét a legközelebbi hozzávetőleges értékként 60 és 75 között.

A VLOOKUP^(VLOOKUP) az Excelben^(Excel) egy nagyon hatékony funkció, amely már régóta elérhető. Akkor is hasznos, ha egy Excel-munkafüzetben bárhol keres egyező értékeket^{(finding matching values anywhere in an Excel workbook)} .

Ne feledje azonban, hogy a havi Office 365 -előfizetéssel rendelkező ^{(Office 365)}Microsoft - felhasználók mostantól hozzáférhetnek egy újabb XLOOKUP funkcióhoz. Ez a funkció több paraméterrel és további rugalmassággal rendelkezik. A féléves előfizetéssel rendelkező felhasználóknak meg kell várniuk a frissítés 2020 júliusában^{(July 2020)} történő megjelenését .

How to Use VLOOKUP in Excel

Have you ever had a large sprеadsheet with data in Excel and need an easy way to filter and extract specific information from it? If yоu learn how to use VLOOKUP in Excel, you can do this lookup with just a single, powerful Excel function.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• Adjon hozzá egy lineáris regressziós trendvonalat egy Excel szóródiagramhoz

• Hogyan készítsünk hisztogramot Excelben

• Hogyan lehet biztonságosan jelszóval védeni egy Excel fájlt

• Folyamatábra létrehozása Wordben és Excelben

• Címkék létrehozása a Wordben Excel-táblázatból

• Formázza a cellákat az Excel feltételes formázásával

• Hogyan lehet megérteni a „Mi lenne, ha” elemzést a Microsoft Excelben

• Használja az Excelt, hogy kiszámoljon egy effektív kamatlábat a névleges kamatlábból

• Két Excel-fájl összehasonlítása és a különbségek kiemelése

• Szúrjon be egy Excel-munkalapot egy Word-dokumentumba

• A háttér megváltoztatása a Microsoft Teamsben

• Gantt-diagramok létrehozása a Microsoft Excelben

• Hogyan készítsünk legördülő listát az Excelben

• Az Excel figyelőablak segítségével figyelheti a munkafüzet fontos celláit

• Használja az Excel Mobile új „Adatok beszúrása képből” funkcióját

• Hogyan válthatunk a munkalapok között az Excelben

• Hogyan készítsünk üdvözlőkártyát MS Word segítségével

• Használja az Összefoglaló függvényeket az adatok összegzéséhez az Excelben

• Útmutató az összes Excel fájlkiterjesztéshez és azok jelentéséhez

• Használja az Excelt az adatok webről történő másolására szolgáló eszközként