Shaqada VLOOKUP ee ku jirta Microsoft Excel macneheedu waxa weeye  baadhis toos ah^{(vertical lookup)} . Waa shaqo raadin weydiinta qiyamka unugga tiirka. Shaqadani waxay raadisaa xogta la xiriirta gelinta tiirka koowaad ee bidixda.

Raadinta xogta tooska ah ayaa aad muhiim u ah marka la macaamilayo miisaska tiirarka iyo safafka badan. Halkii aad ka dhex wareegi lahayd oo aad falanqayn lahayd boqolaal unug, shaqada VLOOKUP ee Excel waxay kaa caawinaysaa inaad hesho xogta aad raadinayso adiga oo kor ilaa hoos ka eegaya qiyamka.

Abuur^(Create) , dhis oo isticmaal shaqada VLOOKUP ee Excel^(VLOOKUP)

Tusaalahayaga, waxaanu la shaqayn doonaa hawl VLOOKUP ah oo raadisa macluumaadka ku saabsan todobada mushaharka shaqaalaha. Qaybtani waxay ku tusinaysaa sida loo isticmaalo shaqada VLOOKUP ee hababka soo socda:

1. Qor shaqada Excel VLOOKUP.
2. Ku dhis shaqada VLOOKUP gudaha Excel.

Anagoon hadal dheerayn, aan gaadhno. Habka ugu horeeya, waxaan u abuuri doonaa shaqada gacanta. Marka xigta, waxaanu ka isticmaali doonaa saaxir doodaha shaqada^{(Functions Arguments)} ee Excel ee la dhisay  .

1] Qor shaqada Excel VLOOKUP

Bilaw Microsoft Excel oo samee tiir qiyamka u shaqeeya sida aqoonsiyo gaar ah. Tan waxaan ugu yeeri doonaa  tiirka tixraaca^{(reference column)} .

Ku dar tiirar kale oo dhinaca midigta ah oo ka mid ah kii ugu horreeyay ee aad ku abuurtay tallaabadii hore oo geli qiimayaasha unugyada tiirarkan.

Guji^(Click) unug madhan oo xaashida faafin ah oo ku qor aqoonsiga shaqaalaha^{(Employee ID)} ee tiirka tixraaca ee shaqaalaha kaas oo aad rabto inaad u raadiso xogta.

Ka dooro^(Select) unug kale oo faaruq ah xaashida faafinta kaas oo Excel uu ku kaydin doono qaacidooyinka oo markaas muujin doono qiimaha la soo celiyay. Halkan, geli qaacidada soo socota:

=VLOOKUP()

Markaad gelayso qaacidada sare, Excel wuxuu soo jeedinayaa ereyga VLOOKUP :

=VLOOKUP(vlookup_value,table_array,col_index_num,range_lookup)

Doodaha ama xuduudaha

Waa kuwan waxa ay doodaha sare ku qeexayaan ereyga:

• Lookup_value:  unugga leh aqoonsiga alaabta ee tiirka tixraaca.
• table_array: xogtu waxay u dhaxaysaa ilaa wax raadin. Waa inay ka kooban tahay tiirka tixraaca iyo tiirka ka kooban qiimaha aad eegayso. Xaaladaha intooda badan, waxaad isticmaali kartaa dhammaan xaashida shaqada. Waxaad ku jiidi kartaa jiirkaaga qiyamka miiska si aad u dooratid baaxadda xogta.
• col_index_num: tirada tiirka kaas oo laga eegayo qiime. Tan waxaad ka gelisaa bidix ilaa midig.
• range_lookup: RUN^(TRUE) oo ah ciyaar ku dhow, BEEN^{(FALSE )} ciyaar sax ah. Qiimuhu waa RUN^{(TRUE )} sida caadiga ah, laakiin guud ahaan waxaad isticmaashaa FALSE.

Macluumaadkan, waxaan hadda ku bedeli doonaa halbeegyada ku jira khaanadaha xogta aan rabno inaan kor u qaadno. Tusaale ahaan, si aad u soo celiso mushaharka Wayne Creed , geli qaacidada soo socota:

=VLOOKUP(C14,B5:E11,6,FALSE)

Markaad ka guurto unugga qaacidada VLOOKUP , waxay soo celinaysaa qiimihii aad waydiisay. Haddii aad hesho qalad  #N/A ah, akhri hagaha Microsoft si aad u barato sida loo saxo^{(Microsoft guide to learn how to correct it)} .

2] Ku dhis^(Build) shaqada VLOOKUP gudaha Excel

Qaybta hore waxay ku tusinaysaa sida loo sameeyo shaqada VLOOKUP gacanta. Haddii aad u malaysay in habka kor ku xusan uu fudud yahay, sug ilaa inta aad tan akhrinayso. Halkan, waxaad ku baran doontaa sida loo dhiso shaqada VLOOKUP si deg deg ah adigoo isticmaalaya saaxir doodaha Functions Functions  .^{(Functions Arguments )}

Fur Microsoft Excel marka hore, oo samee tiir tixraaceed oo ka koobnaan doona aqoonsiyo gaar ah.

Marka xigta, samee tiirar badan oo ku yaal dhinaca midig ee tiirka tixraaca. Halkan, waxaanu gelin doonaa qiimaha la xidhiidha shayada tiirka tixraaca.

Ka dooro^(Select) unug madhan oo ku qor qiimaha unugga tixraaca. Tani waa qiimaha aan ku eegi doono hantidooda.

Guji^(Click) unug kale oo madhan. Iyada oo la doortay, dhagsii  tab Formulas .

Ka dooro  Qalabka Raadinta & Tixraaca ^{(Lookup & Reference )} ee Maktabada Hawlaha^{(Functions Library)} oo ka dooro VLOOKUP liiska hoos u dhaca. Tani waxay fureysaa saaxirkii doodaha Functions^{(Functions Arguments)} .

Ku buuxi Lookup_value , Table_array , Col_index_num , iyo Range_lookup beeraha ku jira saaxir doodaha Functions^{(Functions Arguments)} ee ku qeexan habka kowaad.

Ku dhufo  badhanka OK marka aad dhammayso, shaqada VLOOKUP waxay soo celin doontaa natiijooyinka doodaha aad gelisay.

Hagahan ayaa ku caawin doona haddii habka Excel uu ku guuldareysto inuu si toos ah u cusboonaysiiyo .

Labada habba waxay si guul leh u waydiin doonaan xogta aad u baahan tahay iyagoo tixraacaya tiirka kowaad. Saaxir Argument Formulas^{(Formulas Argument)} wuxuu fududeeyaa in la geliyo doorsoomayaasha si shaqada VLOOKUP ay u shaqeyso.

Si kastaba ha noqotee, shaqada VLOOKUP waxay sidoo kale ka shaqeysaa nooca shabakadda ee Excel . Waxa kale oo aad isticmaali kartaa saaxir doodeedka Functions^{(Functions Argument)} ama waxaad ku abuuri kartaa shaqada VLOOKUP gacanta nooca shabakadda.

Aynu eegno shaqada HLOOKUP ee Excel^{(HLOOKUP function in Excel)} hadda.

How to write, build, and use VLOOKUP function in Excel

The VLOOKUP function in Microsoft Excel literally means vertical lookup. It’s a search function for querying values in the cell of a column. This function searches for the data relative to the entries in the first column from the left.

A vertical data search is most vital when dealing with tables with numerous columns and rows. Instead of scrolling through and analyzing hundreds of cells, Excel’s VLOOKUP function helps you find the data you’re looking for by looking up the values from top to bottom.

Create, build & use Excel’s VLOOKUP function

In our example, we’ll work with a VLOOKUP function that searches for information about seven employees’ salaries. This section shows you how to use the VLOOKUP function in the following ways:

1. Write the Excel VLOOKUP function.
2. Build a VLOOKUP function in Excel.

Without further ado, let’s get to it. In the first method, we’ll create the function manually. Next, we’ll use it from Excel’s inbuilt Functions Arguments wizard.

1] Write the Excel VLOOKUP function

Launch Microsoft Excel and make a column for the values that act as unique identifiers. We’ll call this the reference column.

Add some more columns to the right-hand side of the first one you created in the first step and insert values for the cells in these columns.

Click on an empty cell in the spreadsheet and type in an Employee ID from the reference column of an employee for whom you wish to search for data.

Select another empty cell on the spreadsheet in which Excel will store the formula and hence display the returned value. Here, enter the following formula:

=VLOOKUP()

On entering the above formula, Excel suggests the VLOOKUP syntax:

=VLOOKUP(vlookup_value,table_array,col_index_num,range_lookup)

Arguments or parameters

Here are what the above arguments define in the syntax:

• lookup_value: the cell with the product identifier from the reference column.
• table_array: the data range from with to search. It must contain the reference column and the column containing the value you’re looking up. In most cases, you can use the entire worksheet. You can drag your mouse over the values of the table to select a data range.
• col_index_num: the number of the column from which to look up a value. You put this in from left to right.
• range_lookup: TRUE for an approximate match, FALSE for an exact match. The value is TRUE by default, but you generally use FALSE.

With this information, we’ll now replace the parameters in the parenthesis with the information we wish to look up. For example, to return Wayne Creed‘s salary, enter the following formula:

=VLOOKUP(C14,B5:E11,6,FALSE)

On navigating away from the cell with the VLOOKUP formula, it returns the value for which you queried. If you get a #N/A error, read this Microsoft guide to learn how to correct it.

2] Build a VLOOKUP function in Excel

The first part showed you how to create a VLOOKUP function manually. If you thought the above method was easy, wait till you read this. Here, you’ll learn how to build a VLOOKUP function quickly using the user-friendly Functions Arguments wizard.

Open Microsoft Excel first, and create a reference column that will contain unique identifiers.

Next, create some more columns on the right-hand side of the reference column. Here, we’ll insert the relevant values for the items on the reference column.

Select an empty cell and type in a value from the reference cell. This is the value whose properties we’ll lookup.

Click on another empty cell. With that selected, click on the Formulas tab.

Select the Lookup & Reference tool from the Functions Library and choose VLOOKUP from the dropdown menu. This opens the Functions Arguments wizard.

Fill in the Lookup_value, Table_array, Col_index_num, and Range_lookup fields in the Functions Arguments wizard specified in the first method.

Hit the OK button when you’re done, and the VLOOKUP function will return the results from the arguments you entered.

This guide will help you if the Excel formula fails to update automatically.

Both methods will successfully query the data you need in reference to the first column. The Formulas Argument wizard makes it easy to input the variables to make the VLOOKUP function work.

However, the VLOOKUP function also works on the web version of Excel. You also get to use the Functions Argument wizard or create the VLOOKUP function manually on the web version.

Let us take a look at the HLOOKUP function in Excel now.

Related posts

• Sida loo isticmaalo VLOOKUP gudaha Excel

• Sida loo Hagaajiyo #N/A Khaladaadka gudaha Foomamka Excel sida VLOOKUP

• Goorta la Isticmaalayo Tusaha-Match Bedelkii VLOOKUP ee Excel

• Sida loo isticmaalo Percentile.Exc function gudaha Excel

• Sida loo isticmaalo shaqada NETWORKDAYS gudaha Excel

• Sida loo isticmaalo sifada Nooca Xogta tooska ah ee Excel

• Excel, Word ama PowerPoint ma bilaabi karaan markii ugu dambeysay

• Sida loo abuuro oo loo isticmaalo Koontaroolada Foomka gudaha Excel

• Sida loo isticmaalo Rept Function gudaha Excel

• Sida loo isticmaalo EDATE iyo EOMONTH Functions gudaha Excel

• Sida loo abuuro Bracket tartanka gudaha Windows 11/10

• Microsoft Excel waxay sababtaa isticmaalka CPU sare marka uu socdo Windows 10

• Sida loo bilaabo Word, Excel, PowerPoint, Outlook in Mode Safe

• Sida loo joojiyo ama loo damiyo Wadaagista Buuga Shaqada ee Excel

• Xisaabi leexashada caadiga ah iyo khaladka caadiga ah ee macnaha Excel

• Sida loo isticmaalo shaqada HLOOKUP ee Microsoft Excel

• Sida loo isticmaalo Find and FindB functional in Excel

• Sida loogu isticmaalo DEC2Bin gudaha Excel si loogu beddelo nambarka jajab tobanle ee binary

• Sida loo tiriyo lambarka Haa ama Maya ee Excel

• Sida loo abuuro Jaantuska Ururka gudaha Excel