Weligaa ma haysataa xaashi ballaadhan oo xog ku jirta Excel oo aad u baahan tahay hab fudud oo aad ku shaandhayso oo aad macluumaad gaar ah uga soo saarto? Haddii aad barato sida loogu isticmaalo VLOOKUP gudaha Excel , waxaad ku samayn kartaa raadintan hal-abuur oo awood leh oo Excel ah.

Shaqada VLOOKUP ee Excel waxay cabsi gelisaa dad badan sababtoo ah waxay leedahay cabbirro badan waxaana jira siyaabo badan oo loo isticmaalo. Maqaalkan waxaad ku baran doontaa dhammaan siyaabaha aad ugu isticmaali karto VLOOKUP gudaha Excel iyo sababta ay shaqadu u awood badan tahay.

Qiyaasta VLOOKUP ee Excel^{(VLOOKUP Parameters In Excel)}

Markaad bilowdo inaad ku =VLOOKUP( unug kasta oo Excel ah , waxaad arki doontaa pop-up muujinaya dhammaan cabbirrada shaqada ee jira.

Aynu eegno mid kasta oo ka mid ah halbeegyadan iyo waxa ay ka dhigan yihiin.

• Lookup_value : Qiimaha aad ka raadinayso xaashida faafinta
• table_array : Kala duwanaanshaha unugyada xaashida aad rabto inaad ka dhex raadiso
• col_index_num : Tiirka meesha aad rabto inaad ka soo jiidato natiijadaada
• [range_lookup] : Qaabka ciyaarta ( TRUE = qiyaas, FALSE = sax)

Afartan qaybood waxay kuu oggolaanayaan inaad sameyso wax badan oo kala duwan, raadinta faa'iido leh ee xogta gudaha kaydka xogta aadka u weyn.

Tusaale fudud oo VLOOKUP Excel ah^{(A Simple VLOOKUP Excel Example)}

VLOOKUP maaha mid ka mid ah shaqooyinka aasaasiga ah ee Excel ee^{(the basic Excel functions)} laga yaabo inaad baratay, markaa aan eegno tusaale fudud si aan u bilowno.

Tusaalaha soo socda, waxaanu u isticmaali doonaa buundooyinka SAT ee^(States) dugsiyada Maraykanka^(United) . Xaashidan xaashida ah waxay ka kooban tahay in ka badan 450 dugsi oo ay la socdaan buundooyinka SAT ee shakhsi ahaaneed ee akhriska, xisaabta, iyo qoraalka. Dareen^(Feel) xor inaad soo dejiso si aad ula socoto. Waxaa jira xiriir dibadeed oo jiidaya xogta, markaa waxaad heli doontaa digniin markaad furto faylka, laakiin waa badbaado.

Waxay noqon doontaa wakhti aad u badan inaad ka dhex raadiso xog-ururin intaa le'eg si aad u hesho dugsiga aad xiisaynayso.

Taa baddalkeeda, waxaad samayn kartaa foom fudud unugyada bannaan ee dhinaca miiska. Si aad u sameyso raadintan, kaliya samee hal goob oo loogu talagalay Dugsiga^(School) , iyo seddex goobood oo dheeri ah oo loogu talagalay buundooyinka akhriska, xisaabta, iyo qorista.

Marka xigta, waxaad u baahan doontaa inaad isticmaasho shaqada VLOOKUP ee Excel si aad uga dhigto saddexdan goobood inay shaqeeyaan. Goobta wax- akhriska^(Reading) , u samee shaqada VLOOKUP sida soo socota:

1. Nooca =VLOOKUP(
2. Dooro goobta dugsiga^(School) , kaas oo tusaalahan ku jira I2 . Kooma ku qor
3. Dooro dhammaan unugyada kala duwan ee ka kooban xogta aad rabto inaad kor u eegto. Kooma ku qor

Markaad doorato kala duwanaanshaha, waxaad ka bilaabi kartaa tiirka aad isticmaalayso si aad u eegto (haddii ay sidaas tahay tiirarka magaca dugsiga), ka dibna dooro dhammaan tiirarka iyo safafka kale ee xogta ku jira.

Fiiro gaar^(Note) ah : Shaqada VLOOKUP ee Excel waxay kaliya ka raadin kartaa unugyada dhanka midig ee tiirka raadinta. Tusaalahan, tiirka magaca dugsigu wuxuu u baahan yahay inuu ahaado bidixda xogta aad kor u eegayso.

1. Marka xigta, si aad u soo ceshato dhibcaha Akhriska^(Reading) , waxaad u baahan doontaa inaad ka doorato tiirka 3aad tiirka bidix ee la doortay. Markaa, ku qor 3 ka dibna hakad kale ku qor.
2. Ugu dambeyntii, ku qor BEENTA^(FALSE) ciyaar sax ah, oo ku xir shaqada a ) .

Hawshaada ugu dambeysa ee VLOOKUP waa inay u ekaataa sidan:

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

Markaad marka hore taabato Gelida^(Enter) oo aad dhammayso hawsha, waxaad ogaan doontaa in goobta wax- akhriska^(Reading) ay ka koobnaan doonto #N/A .

Tani waa sababta oo ah goobta dugsigu^(School) waa bannaan oo ma jiraan wax loogu talagalay hawsha VLOOKUP si loo helo. Si kastaba ha ahaatee, haddii aad geliso magaca dugsi kasta oo aad rabto in aad kor u eegto, waxaad arki doontaa natiijada saxda ah ee safkaas dhibcaha Akhriska .^(Reading)

Sida loola tacaalo VLOOKUP inuu yahay kiis- xasaasi ah^{(How To Deal With VLOOKUP Being Case- Sensitive)}

Waxa laga yaabaa inaad dareento in haddii aanad ku qorin magaca dugsiga isla kiis la mid ah sida uu ugu qoran yahay xogta, ma arki doontid wax natiijo ah.

Tani waa sababta oo ah shaqada VLOOKUP waa kiis xasaasi ah. Tani waxay noqon kartaa wax laga xumaado, gaar ahaan xog-ururinta aadka u weyn oo tiirka aad ka dhex raadinayso aanu ku habboonayn sida wax loo weyneeyo.

Si aad arrintan uga gudubto, waxaad ku qasbi kartaa waxa aad raadinayso inaad u beddesho far-yar ka hor inta aanad natiijada fiirin. Si tan loo sameeyo, samee tiir cusub oo ku xigta tiirka aad raadinayso. Ku qor shaqada:

=TRIM(LOWER(B2))

Tani waxay hoos u dhigi doontaa magaca dugsiga waxayna meesha ka saari doontaa xarfo kasta oo ka baxsan (meel bannaan) oo laga yaabo inay ku yaalliin dhinaca bidix ama midig ee magaca.

Qabo furaha Shift oo dhig curcurka jiirka dhanka midig ee hoose ee unugga kowaad ilaa uu ka noqdo laba xariiq oo toosan. Laba jeer^(Double) guji jiirka si aad si otomaatig ah ugu buuxiso tiirka oo dhan.

Ugu dambeyntii, maadaama VLOOKUP ay isku dayi doonto inay isticmaasho qaacidada halkii aad ka isticmaali lahayd qoraalka unugyadan, waxaad u baahan tahay inaad dhamaantood u beddesho qiimaha kaliya. Si aad tan u samayso, koobiyayso tiirka oo dhan, midigta ku dhufo unugga kowaad, oo ku dheji qiyamka oo keliya.

Hadda oo dhammaan xogtaada lagu nadiifiyo tiirkan cusub, wax yar wax ka beddel shaqadaada VLOOKUP ee Excel si aad u isticmaasho tiirarkan cusub halkii aad ka isticmaali lahayd tii hore adoo ka bilaabaya kala-fiirinta^{(the lookup range)} C2 halkii aad ka isticmaali lahayd B2.

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

Hadda waxaad ogaan doontaa haddii aad had iyo jeer ku qorto raadintaada kiis yar, waxaad mar walba heli doontaa natiijo raadin wanaagsan.

Tani waa tilmaame Excel ah oo anfacaya^{(handy Excel tip)} si looga gudbo xaqiiqda ah in VLOOKUP uu yahay kiis xasaasi ah.

VLOOKUP Qiyaasta Ciyaarta

In kasta oo tusaalaha LOOKUP ee saxda ah ee lagu tilmaamay qaybta koowaad ee maqaalkan uu yahay mid toos ah, ciyaarta qiyaastuna waa ka sii adag tahay.

Qiyaasta ciyaarta ayaa sida ugu fiican loo isticmaalaa in lagu baadho tirada kala duwan. Si tan si sax ah loo sameeyo baaxadda goobiddu waxay u baahan tahay in si habboon loo habeeyo. Tusaalaha ugu fiican ee tan waa shaqada VLOOKUP si loo raadiyo darajada xarafka u dhigma darajada lambar.

Haddii macalinku leeyahay liis dheer oo ah buundooyinka shaqada guriga ee sannadka oo dhan oo leh tiir celceliska ugu dambeeya, way fiicnaan lahayd in xarafka u dhigma fasalka u dambeeya si toos ah u soo baxo.

Tani waxay suurtogal u tahay shaqada VLOOKUP . Waxa kaliya ee loo baahan yahay waa miis raadin oo dhanka midig ah oo ka kooban darajada xarafka ku habboon ee kala duwanaanshaha dhibcaha tiro kasta.

Hadda, addoo isticmaalaya shaqada VLOOKUP iyo ciyaar qiyaas ah, waxaad heli kartaa darajada xarafka saxda ah ee u dhiganta tirada saxda ah.

Shaqadan VLOOKUP:

• Lookup_value : F2, celceliska fasalka u dambeeya
• table_array : I2:J8, Kala duwanaanshaha qiimaynta heerka xarafka
• index_column : 2, tiirka labaad ee miiska raadinta
• [range_lookup] : RUN, ciyaar qiyaas ah

Markaad dhammayso shaqada VLOOKUP ee G2 oo taabo Gelida^(Enter) , waxaad buuxin kartaa unugyada intiisa kale adigoo isticmaalaya hab la mid ah sida lagu sheegay qaybta ugu dambeysa. Waxaad arki doontaa dhammaan darajooyinka xarafka oo si habboon loo buuxiyay.

Ogsoonow in shaqada VLOOKUP ee Excel ay ka raadinayso dhamaadka hoose ee kala duwanaanshaha fasalka oo leh dhibcaha xarafka loo qoondeeyay ilaa sare ee kala duwanaanshaha dhibcaha xarafka xiga.

Markaa, “C” waxa ay u baahan tahay in ay noqoto xarafka loo qoondeeyay tirada hoose ee (75), B waxa loo qoondeeyay xagga hoose (ugu yar) ee xadka xarafkiisa. VLOOKUP waxay "heli doontaa" natiijada 60 (D) sida qiimaha ugu dhow ee wax kasta oo u dhexeeya 60 ilaa 75.

VLOOKUP ee Excel waa shaqo aad u awood badan oo la heli karo muddo dheer. Waxa kale oo ay faa'iido u leedahay in la helo qiyam isku mid ah meel kasta oo ka mid ah buugga shaqada ee Excel^{(finding matching values anywhere in an Excel workbook)} .

Maskaxda ku hay, si kastaba ha ahaatee, isticmaalayaasha Microsoft ee haysta rukhsad bille ah ^(Microsoft)Office 365 hadda waxay heli karaan hawl cusub oo XLOOKUP ah. Shaqadani waxay leedahay xaddi badan iyo dabacsanaan dheeraad ah. Isticmaalayaasha leh is-diiwaangelinta nus-sanadlaha ah waxay u baahnaan doonaan inay sugaan cusbooneysiinta si ay u soo baxdo Julaay 2020^{(July 2020)} .

How to Use VLOOKUP in Excel

Have you еver had a large spreadѕheet with data in Excel and need an easy way to filter and extraсt specific information from it? If you learn how to use VLОOKUP 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

• Kudar Khadka Dib-u-Celinta Toosan ee Qorshaha Kala firdhisan ee Excel

• Sida loo sameeyo Histogram gudaha Excel

• Sida loo xafido erayga sirta ah ee Excel File

• Sida Loo Sameeyo Jadwalka socodka gudaha Word iyo Excel

• Sida Loo Sameeyo Calaamadaha Erayga laga bilaabo xaashida faafinta ee Excel

• Sida loo xisaabiyo kala duwanaanshaha Excel

• Hagaha Dhammaan Kordhinta Faylka Excel & Waxa ay ula jeedaan

• Sida Loo Sameeyo Liiska hubinta gudaha Excel

• Jadwalka xogtaada Excel

• Balacaha Saarka AutoFit iyo Dhismayaasha Saf ee Excel

• Sida loo sameeyo garaafka fudud ama jaantuska gudaha Excel

• 13 Tabaha OneNote & Tabaha loogu Abaabulayo Qoraaladaada si ka Wanagsan

• Sida Taariikhaha Looga Jaro Excel

• Sida Loo Sameeyo Liiska Hoos-u-dhaca ee Excel

• Sida loo kala saaro Magacyada Hore iyo Dambe ee Excel

• Sida loo shaandheeyo xogta gudaha Excel

• Sida Loogu Beddelayo Inta u dhaxaysa Waraaqaha Shaqada ee Excel

• Sida loo Furo Tusaalooyin Badan oo Excel ah

• Isku xidh Unugyada u dhexeeya Xaashiyaha iyo Buugaagta Shaqada ee Excel

• Casharrada Aasaasiga ah ee Microsoft Excel - Barashada Sida loo Isticmaalo Excel