Waxaad haysataa buug- shaqeedka Excel^(Excel) oo leh kumanaan tirooyin iyo erayo ah. Waxaa hubaal ah inay jiraan tiro isku mid ah ama kelmad isku mid ah oo halkaas ku jira. Waxaa laga yaabaa inaad u baahato inaad hesho. Markaa waxaanu eegi doonaa dhawr siyaabood oo aad ku heli karto qiimayaal iswaafaqaya gudaha Excel 365 .

Waxaynu dabooli doonaa helida erayo ama tirooyin isku mid ah oo ku jira laba waraaqood oo shaqo oo kala duwan iyo laba tiir oo kala duwan. Waxaan eegi doonaa isticmaalka EXACT , MATCH , iyo VLOOKUP hawlaha. Qaar ka mid ah hababka aan adeegsan doono ayaa laga yaabaa inaysan ku shaqeyn nooca shabakadda ee Microsoft Excel , laakiin dhammaantood waxay ku shaqeyn doonaan nooca desktop-ka. 

Waa maxay Shaqada Excel?^{(What’s An Excel Function?)}

Haddii aad hore u isticmaashay hawlaha, horay u dhaaf. 

Shaqada Excel waa sida app mini. Waxay khusaysaa tillaabooyin taxane ah si loo qabto hawl keliya. Hawlaha ugu badan ee la isticmaalo ee Excel waxaa laga heli karaa tab Formulas . Halkan waxaan ku aragnaa iyaga oo loo kala saaray dabeecadda shaqada -

• AutoSum
• Dhawaan La Isticmaalay
• Dhaqaale
• macquul ah
• Qoraal
• Taariikhda & Waqtiga
• Raadinta & Tixraaca
• Xisaab & Trig
• Shaqooyin badan

Qaybta Hawlaha Badan^{(More Functions )} waxay ka kooban tahay qaybaha Tirakoobka, Injineerinka, Cube, Macluumaadka, Waafaqid, iyo Shabakad^{(Statistical, Engineering, Cube, Information, Compatibility, and Web)} .

Shaqada saxda ah^{(The Exact Function)}

Hawsha saxda^(Exact) ah ee shaqada waa in la maro safafka laba tiir oo laga helo qiyamka isku midka ah ee unugyada Excel . Sax^(Exact) macnaheedu waa sax. Keligiis , shaqada saxda^(Exact) ah waa kiis xasaasi ah. Uma arki doonto New York iyo new york inay yihiin ciyaar.

Tusaalaha hoose, waxaa jira laba tiir oo qoraal ah - Tigidhada^{(– Tickets)} iyo Rasiidhada^(Receipts) . Kaliya 10 qaybood oo qoraal ah, waxaan ku barbardhigi karnaa iyaga markaan eegno. Bal qiyaas haddii ay jireen 1,000 saf ama ka badan in kastoo. Taasi waa marka aad isticmaali lahayd shaqada saxda^(Exact) ah.

Geli calaamadeeyaha unugga C2. Bar caanaha, geli caanaha 

=EXACT(E2:E10,F2:F10)

E2:E10 waxa ay tilmaamaysaa tiirka kowaad ee qiyamka F2:F10 waxa ay tilmaamaysaa tiirka ku xiga. Marka aan taabno Gelida^(Enter) , Excel wuxuu isbarbar dhigayaa labada qiyam ee saf kasta wuxuuna noo sheegi doonaa haddii ay isku mid yihiin ( Run^(True) ) iyo in kale ( Been^(False) ). Maadaama aan isticmaalnay safafka halkii aan ka isticmaali lahayn laba unug oo keliya, qaacidadu waxay ku daadan doontaa unugyada ka hooseeya waxayna qiimeyn doontaa dhammaan safafka kale.

Habkani wuu xadidan yahay in kastoo. Waxa kaliya oo ay is barbar dhigi doontaa laba unug oo isku saf ah. Ma barbar dhigi doono waxa ku jira A2 iyo B3 tusaale ahaan. Sideen u samaynaa taas? MATCH ayaa ku caawin kara

Shaqada MATCH^{(The MATCH Function)}

MATCH waxa loo isticmaali karaa in lagu sheego halka u dhigma qiimo gaar ah uu kaga jiro unugyo kala duwan.

Aynu nidhaahno waxaan rabnaa inaan ogaano safka SKU gaar ah ( Unugga^{(Stock Keeping Unit)} Kaydka Kaydka ) ee ku jira, tusaalaha hoose.

Haddii aan rabno inaan helno safka AA003 ku jira, waxaan isticmaaleynaa qaacidada:

=MATCH(J1,E2:E9,0)

J1 waxaa loola jeedaa unugga leh qiimaha aan rabno inaan ku dhigno. E2:E9 waxa ay tilmaamaysaa kala duwanaanshaha qiyamka aynu ka raadinayno. Eber ( 0 ) ee dhamaadka qaacidada ayaa u sheegaysa Excel inuu raadiyo ciyaar sax ah. Haddii aan isbarbar dhigno nambarada, waxaan isticmaali karnaa 1 si aan u helno wax ka yar weydiinteena ama 2 si aan u helno wax ka weyn weydiinteena.

Laakiin maxaa dhacaya haddii aan rabno inaan helno qiimaha AA003 ?

Shaqada VLOOKUP^{(The VLOOKUP Function)}

V ee ku jira VLOOKUP waxay u taagan tahay toosan. Macnaha waxay ka raadin kartaa qiimaha la bixiyay ee tiirka. Waxa ay sidoo kale samayn karto waa soo celinta qiimaha isku midka ah ee qiimaha la helay. 

Haddii aad ku heshay rukunka Xafiiska 365 kanaalka ^{(Office 365)}bilaha^(Monthly) ah, waxaad isticmaali kartaa XLOOKUP -ka cusub . Haddi aad haysato rukunka nus-sanadlaha ah waxa ay diyaar kuu noqon doontaa Julaay 2020^{(July 2020)} .

Aynu isticmaalno xogta alaabada isku midka ah oo aan isku dayno inaan helno qiimaha shay.

Meeshii aan saf ka raadineynay ka hor, geli qaacidada:

=VLOOKUP(J1,E2:G9,3,FALSE)

J1 waxa loola jeedaa unuga leh qiimaha aanu isbarbar dhigno. E2:G9 waa tirada qiyamka ee aanu ku shaqaynayno. Laakiin VLOOKUP waxay kaliya eegi doontaa tiirka kowaad ee xadkaas ciyaar. 3 - da waxa ay tilmaamaysaa tiirka 3aad ee ka soo wareegay bilawga tirada.  

Markaa marka aan ku qorno SKU gudaha J1, VLOOKUP waxay heli doontaa ciyaarta oo waxay ka soo qaadan doontaa qiimaha unugga 3 tiir ee ka sarreeya. FALSE waxay u sheegtaa Excel nooca kulan ee aan raadineyno. BEENTU^(FALSE) waxay ka dhigan tahay inay tahay ciyaar sax ah halka RUNTU^(TRUE) u sheegi doonto inay tahay mid isku dhow.

Sideen Ku Heli Karaa Qiimaha Isbarbarya Laba Xaashi oo Kala Duwan?^{(How Do I Find Matching Values in Two Different Sheets?)}

Mid kasta oo ka mid ah hawlaha kor ku xusan waxay ka shaqayn karaan laba xaashi oo kala duwan si loo helo qiimayaal iswaafaqaya gudaha Excel . Waxaan isticmaali doonaa shaqada EXACT si aan ku tuso sida. Tan waxaa lagu samayn karaa ku dhawaad ​​shaqo kasta. Kaliya maaha kuwa aan halkan ku soo koobnay. Waxa kale oo jira habab kale oo lagu xidho unugyo u dhexeeya xaashiyo kala duwan iyo buugaag shaqo^{(ways to link cells between different sheets and workbooks)} .

Ka shaqaynta xaashida Holders -ka, waxaanu galnaa qaacidada 

=EXACT(D2:D10,Tickets!E2:E10)

D2:D10 waa tirada aan ku dooranay xaashida Holders . Marka aan dhigno comma ka dib, waxaan gujin karnaa xaashida Tikidhada^(Tickets) oo jiidi oo dooran karnaa kala duwanaanshaha labaad. 

Eeg sida ay u tixraacayso xaashida iyo tirada sida Tickets!E2:E10 ? Xaaladdan oo kale saf kastaa waa u dhigma, markaa natiijadu waa wada run^(True) .

Sidee kale ayaan u isticmaali karaa hawlahan?^{(How Else Can I Use These Functions?)}

Markaad u barato hawlahan is-barbardhigga iyo raadinta walxaha, waxaad bilaabi kartaa inaad waxyaabo badan oo kala duwan ku sameyso. Sidoo kale u fiirso isticmaalka INDEX iyo MATCH si^{(using the INDEX and MATCH functions)} wada jir ah si aad u sameyso wax la mid ah VLOOKUP .

Ma haysaa qaar ka mid ah talooyin wanaagsan oo ku saabsan isticmaalka hawlaha Excel si aad u hesho qiimayaal isku mid ah gudaha Excel ? Waxaa laga yaabaa in su'aal ku saabsan sida loo sameeyo wax badan? Noogu^(Drop) soo qor faallooyinka hoose.

How to Find Matching Values in Excel

You’ve got an Excel workbook with thousands of numbers and words. There аre bound to bе multiрles of the same number or word in there. You might need to find them. So we’re going to look аt several ways you can find matching values in Excel 365.

We’re going to cover finding the same words or numbers in two different worksheets and in two different columns. We’ll look at using the EXACT, MATCH, and VLOOKUP functions. Some of the methods we’ll use may not work in the web version of Microsoft Excel, but they will all work in the desktop version. 

What’s An Excel Function?

If you’ve used functions before, skip ahead. 

An Excel function is like a mini app. It applies a series of steps to perform a single task. The most commonly used Excel functions can be found in the Formulas tab. Here we see them categorized by the nature of the function –

• AutoSum
• Recently Used
• Financial
• Logical
• Text
• Date & Time
• Lookup & Reference
• Math & Trig
• More Functions.

The More Functions category contains the categories Statistical, Engineering, Cube, Information, Compatibility, and Web.

The Exact Function

The Exact function’s task is to go through the rows of two columns and find matching values in the Excel cells. Exact means exact. On its own, the Exact function is case sensitive. It won’t see New York and new york as being a match.

In the example below, there are two columns of text – Tickets and Receipts. For only 10 sets of text, we could compare them by looking at them. Imagine if there were 1,000 rows or more though. That’s when you would use the Exact function.

Place the cursor in cell C2. In the formula bar, enter the formula 

=EXACT(E2:E10,F2:F10)

E2:E10 refers to the first column of values and F2:F10 refers to the column right next to it. Once we press Enter, Excel will compare the two values in each row and tell us if it’s a match (True) or not (False). Since we used ranges instead of just two cells, the formula will spill over into the cells below it and evaluate all the other rows.

This method is limited though. It will only compare two cells that are on the same row. It won’t compare what’s in A2 with B3 for example. How do we do that? MATCH can help.

The MATCH Function

MATCH can be used to tell us where a match for a specific value is in a range of cells.

Let’s say we want to find out what row a specific SKU (Stock Keeping Unit) is in, in the example below.

If we want to find what row AA003 is in, we would use the formula:

=MATCH(J1,E2:E9,0)

J1 refers to the cell with the value we want to match. E2:E9 refers to the range of values we’re searching through. The zero (0) at the end of the formula tells Excel to look for an exact match. If we were matching numbers, we could use 1 to find something less than our query or 2 to find something greater than our query.

But what if we wanted to find the price of AA003?

The VLOOKUP Function

The V in VLOOKUP stands for vertical. Meaning it can search for a given value in a column. What it can also do is return a value on the same row as the found value. 

If you’ve got an Office 365 subscription in the Monthly channel, you can use the newer XLOOKUP. If you only have the semi-annual subscription it will be available to you in July 2020.

Let’s use the same inventory data and try to find the price of something.

Where we were looking for a row before, enter the formula:

=VLOOKUP(J1,E2:G9,3,FALSE)

J1 refers to the cell with the value we’re matching. E2:G9 is the range of values we’re working with. But VLOOKUP will only look in the first column of that range for a match. The 3 refers to the 3rd column over from the start of the range.  

So when we type a SKU in J1, VLOOKUP will find the match and grab the value from the cell 3 columns over from it. FALSE tells Excel what kind of match we’re looking for. FALSE means it must be an exact match where TRUE would tell it that it has to be a close match.

How Do I Find Matching Values in Two Different Sheets?

Each of the functions above can work across two different sheets to find matching values in Excel. We’re going to use the EXACT function to show you how. This can be done with almost any function. Not just the ones we covered here. There are also other ways to link cells between different sheets and workbooks.

Working on the Holders sheet, we enter the formula 

=EXACT(D2:D10,Tickets!E2:E10)

D2:D10 is the range we’ve selected on the Holders sheet. Once we put a comma after that, we can click on the Tickets sheet and drag and select the second range. 

See how it references the sheet and range as Tickets!E2:E10? In this case each row matches, so the results are all True.

How Else Can I Use These Functions?

Once you master these functions for matching and finding things, you can start doing a lot of different things with them. Also take a look at using the INDEX and MATCH functions together to do something similar to VLOOKUP.

Have some cool tips on using Excel functions to find matching values in Excel? Maybe a question about how to do more? Drop us a note in the comments below.

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

• Adeegso Excel si aad u ogaato dulsaarka waxtarka leh ee ka imanaya heerka dulsaarka magaca ah

• Sida loo sameeyo garaafka fudud ama jaantuska gudaha Excel

• Sida loo isticmaalo Sparklines gudaha Excel

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

• Hagaha Dhammaan Kordhinta Faylka Excel & Waxa ay ula jeedaan

• Sida loo barbar dhigo labada faylal ee Excel oo loo muujiyo faraqa u dhexeeya

• Sida loo isticmaalo VLOOKUP gudaha Excel

• Sida loo shaandheeyo xogta gudaha Excel

• Sida loogu daro faallooyinka unugga Xaashida shaqada ee Excel

• Sida loo Helo loona Xisaabiyo Range gudaha Excel

• Sida Loo Bedelo Taariikhda Kooxda Microsoft

• Geli xaashida shaqada ee Excel Word Doc

• 40 ka ugu wanaagsan ee Keyboard-ka Microsoft Excel

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

• Sida Loo Fahmo Maxaa-Haddii Falanqaynta Microsoft Excel