Noloshu^(Life) waa khariban sow maaha? Waxyaabaha ay ka midka yihiin la socodka dhaqaalaha iyo maaraynta wakhtiga waa khariban oo waqti badan qaata. Hase yeeshee, kuwani waa waxyaabo, haddii la hagaajiyo, ay hagaajin lahaayeen noloshaada. Xaashiyaha faafinta ayaa kaa caawin kara maalin^{(Spreadsheets can help every day)} kasta hawlahan oo kale.

Way adag tahay in laga helo macluumaadka xaashiyaha, si kastaba ha ahaatee. Taasi waa sababta aan ku tusin doono sida loo isticmaalo shaqada VLOOKUP ee Google Sheets si aan uga dhigno wax ka helidda xaashida xaashida mid aad u fudud.

VLOOKUP waa shaqo xaashida^{(Sheets function)} lagu helo wax ku jira tiirka kowaad ee xaashida. V-gu waa mid toosan sababtoo ah, sida tiirarka dhismaha, tiirarka xaashida ayaa toosan. Markaa marka VLOOKUP ay hesho waxa muhiimka ah ee aanu raadinayno, waxay noo sheegi doontaa qiimaha unug gaar ah oo safkaas ku jira.

Shaqada VLOOKUP waa la Sharaxay^{(The VLOOKUP Function Explained)}

Sawirka hoose waxa ku jira habraaca shaqada VLOOKUP . Sidan ayaa shaqada loo dejiyay, iyadoon loo eegin meesha laga isticmaalo.

Shaqadu waa qaybta =VLOOKUP( ) . Shaqadu gudaheed waa:

• Furaha Raadinta^{(Search Key )} - Waxay u sheegtaa VLOOKUP^{(Tells VLOOKUP)} waxay u baahan tahay si loo helo.
• Range - Wuxuu u sheegaa VLOOKUP^{(Tells VLOOKUP)} meesha laga raadiyo. VLOOKUP waxay had iyo jeer ka eegi doontaa tiirka bidix ee xadka.
• Tusmada^(Index) – Waxay u sheegtaa VLOOKUP^{(Tells VLOOKUP)} inta tiir ee dhanka midig ee tiirarka bidix ee ugu badan ee kala duwan si loo raadiyo qiime haddii ay hesho mid ku habboon furaha raadinta. Tiirka bidix ee ugu badan had iyo jeer waa 1, kan ku xiga ee midig waa 2, iyo wixii la mid ah.
• Ma la kala soocay? ^{(Is sorted?)}- Waxay u sheegaysaa VLOOKUP^{(Tells VLOOKUP)} haddii tiirka kowaad la kala soocay. Tani waxay u dhigantaa RUN^(TRUE) , taas oo macnaheedu yahay VLOOKUP waxay heli doontaa ciyaarta ugu dhow ee furaha raadinta. Tani waxay keeni kartaa natiijooyin sax ah oo yar. BEEN ABUUR^(FALSE) waxay u sheegaysaa VLOOKUP inay tahay inay noqoto mid sax ah, markaa isticmaal BEEN ABUUR^(FALSE) .

Shaqada VLOOKUP ee kore waxay isticmaali doontaa wax kasta oo qiime ah oo ku jira unugga E1 sida furaha raadinta. Marka ay hesho ciyaar ku jirta tiirka A ee kala duwanaanshaha unugyada A1 ilaa C5 , waxay eegi doontaa tiirka saddexaad ee isla safka sida ay u heshay ciyaarta oo soo celisaa wax kasta oo qiimo ah oo ku jira. Sawirka hoose wuxuu muujinayaa natiijooyinka gelitaanka 4 ee unugga E1 . Marka xigta, aan eegno dhowr siyaabood oo loo isticmaalo shaqada VLOOKUP ee Google Sheets .

Tusaale 1: Isticmaalka VLOOKUP ee Dabagalka Shaqooyinka^{(Example 1: Using VLOOKUP For Tracking Jobs)}

Aynu nidhaahno waxaad leedahay ganacsi adeeg oo aad rabto inaad ogaato marka uu bilaabmayo amarka shaqada. Waxaad haysataa hal xaashi shaqo, hoos ugu dhaadhac nambarka dalabka shaqada ka bacdina eeg safka oo dhan si aad u ogaato goorta uu bilaabmayo. Taasi waxay noqon kartaa mid caajis ah oo u nugul qaladka. 

Ama waxaad isticmaali kartaa VLOOKUP . 

1. Geli ciwaanada Amarka^{(Work Order )} Shaqada iyo Taariikhda Shaqada^{(Work Date )} meel xaashida shaqada ah.

1. Dooro unugga dhanka midig ee Taariikhda Shaqada^{(Work Date )} oo billow geli qaacidada =VLOOKUP . Sanduuqa caawinta ayaa soo bixi doona marka aanu wax qorayno, kaas oo ina tusi doona hawlaha xaashida Google ee diyaarsan ee^{(Google Sheet functions)} ku habboon waxa aanu qorayno. Marka ay muujiso VLOOKUP , taabo Gelida,^{(Enter, )} oo ay dhammayn doonto qorista.

1. Si aad u dejiso halka VLOOKUP ay ka heli doonto furaha raadinta^{(Search Key)} , dhagsii unugga midigta ka sarreeya kan.

1. Si aad u dooratid Range of data in la baadho, guji oo hay madaxa tiirarka A oo jiid si aad u dooratid wax walba, oo ay ku jiraan tiirka H . 

1. Si aad u dooratid tusmada^(Index) , ama tiirka, ee aan rabno inaan xogta ka soo saarno, tiri A ilaa H . H waa tiirka 7aad ee geli 7 qaacidada.

1. Hadda waxaan sheegaynaa sida aan rabno in tiirka koowaad ee tirada loo raadiyo. Waxaan u baahanahay ciyaar sax ah markaa gal BEEN^(FALSE) . 

Ogaysii^(Notice) in ay doonayso in ay dhigto dabada qaloocan ee furan ka dib BEEN^(FALSE) . Riix^(Press) gadaasha dambe si aad taas meesha uga saarto. 

Kadibna geli garabka xidhitaanka qaloocan ) , oo taabo Gelida^{(Enter )} si aad u dhamaystirto qaacidada.

Waxaan arki doonaa fariin qalad ah Taasi waa ok; si sax ah ayaanu wax u samaynay. Arrintu waxay tahay in aanan weli haysan qiimaha muhiimka ah ee raadinta.

Si aad u tijaabiso qaaciddada VLOOKUP , geli lambarka shaqada ee ugu horreeya unugga ka sarreeya caanaha oo tabo Gelida^(Enter) . Taariikhda la soo celiyay waxay la mid tahay taariikhda ku jirta tiirka Taariikhda Shaqada ee amarka shaqada A00100^{(WorkDate )} .

Si aad u aragto sida ay tani u fududaynayso nolosha, geli lambarka shaqada ee aan ka muuqanayn shaashadda, sida A00231 .

Is barbar dhig taariikhda la soo celiyay iyo taariikhda safka A00231 , oo waa inay iswaafaqaan. Haddii ay sameeyaan, qaacidada ayaa wanaagsan.

Tusaalaha 2: Isticmaalka VLOOKUP si loo Xisaabiyo Kalooriyada Maalinlaha ah^{(Example 2: Using VLOOKUP to Calculate Daily Calories)}

Tusaalaha Amarka^(Order) Shaqada waa mid wanaagsan laakiin fudud. Aan ku aragno awooda dhabta ah ee VLOOKUP ee Google Sheets anagoo samaynayna xisaabiyaha kalooriga maalinlaha ah. Waxaan xogta gelin doonaa hal xaashi shaqo waxaanan ka dhigi doonaa xisaabiyaha kalooriga mid kale.

1. Dooro^(Select) dhammaan xogta ku jirta liiska cuntada iyo kaloriyada. 

1. Dooro Xog^{(Data )} > Heerarka Magacaaban^{(Named Ranges)} .

1. Magaca xadka Range Cunnada^(FoodRange) . Heerarka la magacaabay^{(Named ranges)} ayaa ka fudud in la xasuusto Sheet2!A1:B:29 , taasoo ah qeexida dhabta ah ee kala duwanaanshaha.

1. Ku laabo xaashida shaqada halka cuntada lagala socdo. Unugga ugu horreeya ee aan rabno in kalooriyadu muujiyaan, waxaan geli karnaa qaacidada =VLOOKUP(A3,FoodRange,2,False) .

Way shaqayn lahayd, laakiin sababtoo ah waxba kuma jiraan A3 , waxaa jiri doona qalad #REF ah oo fool xun . Xisaabiyahan waxaa laga yaabaa in unugyo badan oo Cunno^(Food) ah ay bannaan yihiin mana rabno inaan aragno # REF oo dhan.

1. Aan gelino qaacidada VLOOKUP gudaha shaqada IFERROR . IFERROR wuxuu u sheegaa Xaashiyaha in haddii ay wax ka khaldan yihiin qaacidada, soo celi meel bannaan.

1. Si aad u koobiyaysid qaacidada hoos tiirka, dooro gacanta ku taal geeska hoose ee midig ee unugga oo u jiid hoos unugyo badan oo loo baahdo.

Haddii aad u malaynayso in qaaciddada A3 u adeegsan doonto furaha tiirka, ha welwelin. Xaashiyaha ayaa habayn doona qaacidada si ay u isticmaalaan furaha safka ay qaacidadu ku jirto. Tusaale ahaan, sawirka hoose, waxa aad arki kartaa in furuhu isu beddelay A4 markii loo raray safka 4aad. Foomamka^(Formulas) ayaa si otomaatig ah u beddeli doona tixraacyada unugga^{(change cell references)} sidan oo kale marka laga guuro tiir loona guuro tiir, sidoo kale.

1. Si aad isugu geyso dhammaan kalooriyada maalintii, isticmaal =SUM function unugga bannaan ee ku xiga wadarta guud^(Total) , oo dooro dhammaan safafka kalooriyada ee ka sarreeya.

Hadda waxaan arki karnaa inta kalooriyo ee aan haysanay maanta.

1. Ka dooro tiirka kalooriyada Isniinta^{(Monday )} oo ku dheji^{(Calories )} tiirarka kalooriyada Talaadada^(Tuesday) , Arbacada^(Wednesday) , iyo wixii la mid ah. 

Si la mid ah u samee wadarta^{(Total )} unugga ka hooseeya Isniinta^(Monday) . Markaa hadda waxaan haynaa xisaabiye kalori todobaadle ah.

Soo koobida VLOOKUP^{(Summing Up VLOOKUP)}

Haddii tani ay tahay markii ugu horreysay ee aad u quusato Google Sheets iyo hawlaha, waxaad arki kartaa sida faa'iido iyo awood leh hawlaha sida VLOOKUP ay u noqon karaan. Isku darka hawlaha kale sida IFERROR , ama kuwo kale oo badan, ayaa kaa caawin doona inaad sameyso wax kasta oo aad u baahan tahay. Haddii aad tan ku raaxaysatay, waxaad xitaa ka fikiri kartaa ka beddelashada Excel una beddelo Google Sheets^{(converting from Excel to Google Sheets)} .

How to Use VLOOKUP in Google Sheets

Life is messy, isn’t it? Things like tracking finances and managing time are messy and time-consuming. Yet, these are things that, if put in order, would improve your life. Spreadsheets can help every day with these sorts of tasks.

It can be challenging to find information in spreadsheets, however. That’s why we’re going to show you how to use the VLOOKUP function in Google Sheets to make finding something in a spreadsheet a lot easier.

VLOOKUP is a Sheets function to find something in the first column of a spreadsheet. The V is for vertical because, like columns on a building, spreadsheet columns are vertical. So when VLOOKUP finds the key thing we’re looking for, it will tell us the value of a specific cell in that row.

The VLOOKUP Function Explained

In the image below is the VLOOKUP function’s syntax. This is how the function is laid out, regardless of where it’s being used.

The function is the =VLOOKUP( ) part. Inside the function are:

• Search Key – Tells VLOOKUP what it needs to find.
• Range – Tells VLOOKUP where to look for it. VLOOKUP will always look in the leftmost column of the range.
• Index – Tells VLOOKUP how many columns to the right of the left-most column in the range to look for a value if it finds a match of the search key. The left-most column is always 1, the next to its right is 2, and so on.
• Is sorted? – Tells VLOOKUP if the first column is sorted. This defaults to TRUE, which means VLOOKUP will find the nearest match to the search key. This can lead to less accurate results. FALSE tells VLOOKUP that it must be an exact match, so use FALSE.

The VLOOKUP function above will use whatever value is in cell E1 as its search key. When it finds a match in column A of the range of cells from A1 to C5, it will look in the third column of the same row as it found the match and return whatever value is in it. The image below shows the results of entering 4 in cell E1. Next, let’s look at a couple of ways to use the VLOOKUP function in Google Sheets.

Example 1: Using VLOOKUP For Tracking Jobs

Let’s say you have a service business and you want to find out when a work order starts. You could have a single worksheet, scroll down to the work order number and then look across the row to find out when it starts. That can become tedious and prone to error. 

Or you could use VLOOKUP. 

1. Enter the headings Work Order and Work Date somewhere on the worksheet.

1. Select the cell to the right of Work Date and start entering the formula =VLOOKUP. A help box will pop up as we type, showing us available Google Sheet functions that match what we’re typing. When it shows VLOOKUP, press Enter, and it will complete the typing.

1. To set where VLOOKUP will find the Search Key, click on the cell right above this.

1. To select the Range of data to search in, click and hold on the A column header and drag to select everything over to, including column H. 

1. To select the Index, or column, that we want to pull data from, count from A to H. H is the 7th column so enter 7 in the formula.

1. Now we state how we want the first column of the range to be searched. We need an exact match so enter FALSE. 

Notice that it wants to put an opening curved bracket after FALSE. Press backspace to remove that. 

Then enter a curved closing bracket ), and press Enter to finish the formula.

We’ll see an error message. That’s ok; we did things correctly. The issue is that we don’t have a search key value yet.

To test the VLOOKUP formula, enter the first work order number in the cell above the formula and press Enter. The date returned matches the date in the WorkDate column for work order A00100.

To see how this makes life easier, enter a work order number that isn’t visible on the screen, like A00231.

Compare the date returned and the date in the row for A00231, and they should match. If they do, the formula is good.

Example 2: Using VLOOKUP to Calculate Daily Calories

The Work Order example is good but simple. Let’s see the actual power of VLOOKUP in Google Sheets by creating a daily calorie calculator. We’ll put the data in one worksheet and make the calorie calculator in another.

1. Select all the data on the food and calorie list. 

1. Select Data > Named Ranges.

1. Name the range FoodRange. Named ranges are easier to remember than Sheet2!A1:B:29, which is the actual definition of the range.

1. Go back to the worksheet where food is tracked. In the first cell in which we want calories to show, we could enter the formula =VLOOKUP(A3,FoodRange,2,False).

It would work, but because there’s nothing in A3, there will be an ugly #REF error. This calculator might have many Food cells left blank and we don’t want to see #REF all over it.

1. Let’s put the VLOOKUP formula inside an IFERROR function. IFERROR tells Sheets that if anything goes wrong with the formula, return a blank.

1. To copy the formula down the column, select the handle at the bottom-right corner of the cell and drag it down over as many cells as needed.

If you think that the formula will use A3 as the key down the column, don’t worry. Sheets will adjust the formula to use the key in the row that the formula is in. For example, in the image below, you can see that the key changed to A4 when moved to the 4th row. Formulas will automatically change cell references like this when moved from column to column, too.

1. To add up all the calories in a day, use the =SUM function in the blank cell next to Total, and select all the rows of calories above it.

Now we can see how many calories we had today.

1. Select the column of calories from Monday and paste it to the Calories column for Tuesday, Wednesday, and so on. 

Do the same for the Total cell below Monday. So now we have a weekly calorie counter.

Summing Up VLOOKUP

If this is your first dive into Google Sheets and functions, you can see how useful and powerful functions like VLOOKUP can be. Combining it with other functions like IFERROR, or so many others, will help you do whatever you need. If you enjoyed this, you might even consider converting from Excel to Google Sheets.

Related posts

• Sida loo Geliyo Emoji gudaha Word, Google Docs iyo Outlook

• Sida loo Hagaajiyo 403 Cilada Reebay Google Chrome

• Waxa la sameeyo marka Google uu yidhaahdo waxaa laga yaabaa in kombayutarkaagu soo dirayo su'aalo toos ah

• Sida loo Dejiyo Aagga Daabacaadda ee Xaashiyaha Google-ka

• Sida loo Beddelo Kaabta Google Drive iyo Sync Goobta Faylka

• Sida loo hagaajiyo qaladka Disney Plus Code 83

• Sida Ganacsi loogu daro Khariidadaha Google iyo Khariidadaha Apple

• Sida looga takhaluso raadinta Yahoo gudaha Chrome

• Sida Loo Helo Maalmaha Dhalashada Facebook-ga

• Sida Loo Sameeyo Liiska Ilaalinta saamiyada ee Google Finance

• Sida Microsoft U Qabato Is-dhexgalka Kalandarka Google

• 7 Siyaabood Oo Sawirka Looga Badbaadiyo Google Docs

• 3 Siyaabood oo Sawir ama Fiidyow Looga Qaado Buugga Chrome-ka

• Sida maqaal loogu dhejiyo Linkedin (iyo waqtiyada ugu fiican ee la dhejiyo)

• Sida loo soo dejiyo oo loogu rakibo Peacock on Firestick

• Sida Slack loola xidhiidhiyo Google Calendar

• Ma bedeli kartaa Magacaaga Twitch? Haa, Laakin ka digtoonow

• Sida Looga Qaado Sawir-qaadista Steam

• Waa maxay Qiimaynta Uber Rakaabka iyo sida loo hubiyo

• Aaway Sawirradayda Google-ka? Sida Loo Helo