Jeste li ikada imali veliku proračunsku tablicu s podacima u Excelu^(Excel) i trebate jednostavan način za filtriranje i izdvajanje određenih informacija iz nje? Ako naučite koristiti VLOOKUP u Excelu^(Excel) , ovo traženje možete obaviti samo jednom, moćnom funkcijom programa Excel^(Excel) .

Funkcija VLOOKUP u Excelu^(Excel) plaši mnoge ljude jer ima puno parametara i postoji više načina za korištenje. U ovom ćete članku naučiti sve načine na koje možete koristiti VLOOKUP u Excelu^(Excel) i zašto je ova funkcija tako moćna.

Parametri VLOOKUP-a u Excelu^{(VLOOKUP Parameters In Excel)}

Kada počnete upisivati =VLOOKUP( u bilo koju ćeliju u Excelu^(Excel) , vidjet ćete skočni prozor koji prikazuje sve dostupne parametre funkcije.

Pogledajmo svaki od ovih parametara i što oni znače.

• lookup_value : vrijednost koju tražite iz proračunske tablice
• table_array : Raspon ćelija na listu po kojem želite pretraživati
• col_index_num : Stupac iz kojeg želite izvući svoj rezultat
• [range_lookup] : Način podudaranja ( TRUE = približno, FALSE = točno)

Ova četiri parametra omogućuju vam puno različitih, korisnih pretraživanja podataka unutar vrlo velikih skupova podataka.

Jednostavan VLOOKUP Excel primjer^{(A Simple VLOOKUP Excel Example)}

VLOOKUP nije jedna od osnovnih funkcija programa Excel^{(the basic Excel functions)} koje ste možda naučili, pa pogledajmo jednostavan primjer za početak.

Za sljedeći primjer koristit ćemo veliku proračunsku tablicu SAT rezultata za škole u Sjedinjenim ^(United) Državama^(States) . Ova proračunska tablica sadrži preko 450 škola zajedno s pojedinačnim SAT rezultatima za čitanje, matematiku i pisanje. Slobodno^(Feel) preuzmite i pratite. Postoji vanjska veza koja povlači podatke pa ćete pri otvaranju datoteke dobiti upozorenje, ali je sigurna.

Bilo bi dugotrajno pretraživati ​​tako veliki skup podataka kako biste pronašli školu koja vas zanima.

Umjesto toga, možete stvoriti jednostavan obrazac u praznim ćelijama sa strane tablice. Da biste izvršili ovu pretragu, samo napravite jedno polje za školu^(School) i tri dodatna polja za rezultate čitanja, matematike i pisanja.

Zatim ćete morati koristiti funkciju VLOOKUP u Excelu^(Excel) kako bi ova tri polja radila. U polju Čitanje^(Reading) kreirajte funkciju VLOOKUP na sljedeći način:

1. Upišite =VLOOKUP(
2. Odaberite polje Škola^(School) , koje je u ovom primjeru I2 . Upišite zarez.
3. Odaberite cijeli raspon ćelija koje sadrže podatke koje želite potražiti. Upišite zarez.

Kada odaberete raspon, možete krenuti od stupca koji koristite za traženje (u ovom slučaju stupca naziva škole), a zatim odabrati sve ostale stupce i retke koji sadrže podatke.

Napomena^(Note) : funkcija VLOOKUP u Excelu^(Excel) može pretraživati ​​samo ćelije desno od stupca za pretraživanje. U ovom primjeru, stupac naziva škole mora biti lijevo od podataka koje tražite.

1. Zatim, da biste dohvatili ocjenu Readinga^(Reading) , morat ćete odabrati 3. stupac iz krajnje lijevog odabranog stupca. Dakle, upišite 3 , a zatim upišite još jedan zarez.
2. Na kraju upišite FALSE za točno podudaranje i zatvorite funkciju s ) .

Vaša konačna funkcija VLOOKUP trebala bi izgledati otprilike ovako:

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

Kada prvi put pritisnete Enter i završite funkciju, primijetit ćete da će polje za čitanje^(Reading) sadržavati #N/A .

To je zato što je polje Škola prazno i ​​funkcija ^(School)VLOOKUP ne može ništa pronaći. Međutim, ako unesete naziv bilo koje srednje škole koju želite potražiti, vidjet ćete točne rezultate iz tog retka za ocjenu Readinga .^(Reading)

Kako se nositi s time da VLOOKUP razlikuje velika i mala slova^{(How To Deal With VLOOKUP Being Case- Sensitive)}

Možda ćete primijetiti da ako naziv škole ne upišete u istom slovu kao što je navedeno u skupu podataka, nećete vidjeti nikakve rezultate.

To je zato što je funkcija VLOOKUP osjetljiva na velika i mala slova. To može biti neugodno, posebno za vrlo veliki skup podataka u kojem stupac koji pretražujete nije u skladu s načinom na koji su stvari napisane velikim slovima.

Da biste to zaobišli, možete natjerati ono što tražite da se prebaci na mala slova prije nego što potražite rezultate. Da biste to učinili, stvorite novi stupac pored stupca koji tražite. Upišite funkciju:

=TRIM(LOWER(B2))

Ovo će smanjiti naziv škole i ukloniti sve suvišne znakove (razmake) koji se mogu nalaziti s lijeve ili desne strane naziva.

Držite pritisnutu tipku Shift i postavite pokazivač miša iznad donjeg desnog kuta prve ćelije dok se ne promijeni u dvije vodoravne crte. Dvaput^(Double) kliknite mišem za automatsko popunjavanje cijelog stupca.

Konačno, budući da će VLOOKUP pokušati koristiti formulu, a ne tekst u tim ćelijama, morate ih sve pretvoriti samo u vrijednosti. Da biste to učinili, kopirajte cijeli stupac, desnom tipkom miša kliknite prvu ćeliju i zalijepite samo vrijednosti.

Sada kada su svi vaši podaci očišćeni u ovom novom stupcu, malo izmijenite svoju funkciju VLOOKUP u Excelu^(Excel) kako biste koristili ovaj novi stupac umjesto prethodnog tako što ćete započeti raspon pretraživanja^{(the lookup range)} na C2 umjesto na B2.

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

Sada ćete primijetiti da ćete uvijek dobiti dobar rezultat pretraživanja ako pretraživanje uvijek upisujete malim slovima.

Ovo je zgodan savjet^{(handy Excel tip)} za Excel za prevladavanje činjenice da je VLOOKUP osjetljiv na velika i mala slova.

VLOOKUP Približno podudaranje

Dok je primjer točnog podudaranja LOOKUP opisan u prvom odjeljku ovog članka prilično jednostavan, približno podudaranje je malo složenije.

Približno podudaranje najbolje je koristiti za pretraživanje raspona brojeva. Da biste to učinili ispravno, raspon pretraživanja mora biti pravilno sortiran. Najbolji primjer za to je funkcija VLOOKUP za traženje slovne ocjene koja odgovara brojčanoj ocjeni.

Ako učitelj ima dugačak popis ocjena učeničkih domaćih zadaća tijekom cijele godine s konačnim prosječnim stupcem, bilo bi lijepo da se slovna ocjena koja odgovara toj konačnoj ocjeni pojavi automatski.

To je moguće s funkcijom VLOOKUP . Sve što je potrebno je pregledna tablica s desne strane koja sadrži odgovarajuću slovnu ocjenu za svaki raspon brojčanih bodova.

Sada, koristeći funkciju VLOOKUP i približno podudaranje, možete pronaći odgovarajuću ocjenu slova koja odgovara ispravnom numeričkom rasponu.

U ovoj funkciji VLOOKUP:

• lookup_value : F2, konačna prosječna ocjena
• table_array : I2:J8, Raspon traženja ocjene slova
• index_column : 2, drugi stupac u tablici pretraživanja
• [range_lookup] : TRUE, približno podudaranje

Nakon što završite s funkcijom VLOOKUP u G2 i pritisnete Enter , možete ispuniti preostale ćelije koristeći isti pristup opisan u zadnjem odjeljku. Vidjet ćete sve slovne ocjene ispravno popunjene.

Imajte na umu da funkcija VLOOKUP u Excelu^(Excel) pretražuje od donjeg kraja raspona ocjena s dodijeljenim slovnim rezultatom do vrha raspona sljedećeg slovnog rezultata.

Dakle, "C" treba biti slovo dodijeljeno donjem rasponu (75), a B je dodijeljeno dnu (minimum) vlastitog raspona slova. VLOOKUP će "pronaći" rezultat za 60 (D) kao najbližu približnu vrijednost za bilo što između 60 i 75.

VLOOKUP u Excelu^(Excel) je vrlo moćna funkcija koja je već dugo dostupna. Također je korisno za pronalaženje odgovarajućih vrijednosti bilo gdje u radnoj knjizi programa Excel^{(finding matching values anywhere in an Excel workbook)} .

Međutim, imajte na umu da korisnici Microsofta^(Microsoft) koji imaju mjesečnu pretplatu na Office 365 sada imaju pristup novijoj funkciji XLOOKUP. Ova funkcija ima više parametara i dodatnu fleksibilnost. Korisnici s polugodišnjom pretplatom morat će pričekati da se ažuriranje uvede u srpnju 2020^{(July 2020)} .

How to Use VLOOKUP in Excel

Have you ever had a lаrge spreadѕheet with data in Excel and need an easy way to filter and extrаct specific information from it? If you learn how to use VLOOKUP in Excel, you can do this lookup with just a single, powerful Excel functiоn.

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

• Dodajte liniju trenda linearne regresije dijagramu raspršenja programa Excel

• Kako napraviti histogram u Excelu

• Kako sigurno lozinkom zaštititi Excel datoteku

• Kako napraviti dijagram toka u Wordu i Excelu

• Kako stvoriti naljepnice u Wordu iz Excel proračunske tablice

• Kako napraviti kontrolnu listu u Excelu

• Kako stvoriti Ganttov grafikon u Microsoft Excelu

• Oblikujte ćelije pomoću uvjetnog oblikovanja u Excelu

• Kako stvoriti popis distribucije u Outlooku

• Kako koristiti Excel AutoRecover i AutoBackup značajke

• Kako grupirati radne listove u Excelu

• Kako oduzeti datume u Excelu

• Kako pratiti promjene u Excelu

• Kako dodati trake grešaka u Excel

• Kako napraviti čestitku u MS Wordu

• 40 najboljih Microsoft Excel tipkovničkih prečaca

• Vodič za osnove Microsoft Excela – Učenje kako koristiti Excel

• Koristite prozor za praćenje programa Excel za praćenje važnih ćelija u radnoj knjizi

• Kako koristiti COUNTIFS, SUMIFS, AVERAGEIFS u Excelu

• Kako filtrirati podatke u Excelu