Kuwa idinka mid ah oo si fiican u yaqaan Excel , waxaad u badan tahay inaad aad u yaqaaniin shaqada VLOOKUP . Shaqada VLOOKUP waxaa loo isticmaalaa in lagu helo qiime unug kale oo ku saleysan qoraal isku mid ah oo isla saf ah.

Haddii aad wali ku cusub tahay shaqada VLOOKUP , waxaad ka eegi kartaa qoraaladeydii hore sida loo isticmaalo VLOOKUP gudaha Excel^{(how to use VLOOKUP in Excel)} .

Si kasta oo ay u awood badan tahay, VLOOKUP waxay leedahay xaddid ku saabsan sida miiska tixraaca ee ku habboon uu u baahan yahay in loo habeeyo si qaaciddadu u shaqeyso.

Maqaalkani wuxuu ku tusi doonaa xaddidaadda meesha VLOOKUP aan la isticmaali karin waxayna soo bandhigi doontaa shaqo kale oo Excel ah oo loo yaqaan INDEX-MATCH oo xallin karta arrinta.

INDEX MATCH Excel Tusaale

Isticmaalka tusaalaha soo socda ee xaashida xaashida ee Excel , waxaanu haynaa liiska magaca mulkiilayaasha baabuurta iyo magaca baabuurka. Tusaalahan, waxaanu isku dayi doonaa inaanu soo qabano  aqoonsiga gaadhiga^{(Car ID)} iyadoo lagu salaynayo Qaabka Baabuurka ee^{(Car Model)} ku hoos taxan mulkiilayaal badan sida hoos ku cad:

Warqad gaar ah oo la yiraahdo CarType , waxaan ku haynaa keydka xogta baabuurta ee fudud oo leh  aqoonsiga^(ID) , Qaabka Gaariga^{(Car Model)} iyo Midabka^(Color) .

Shaxdan dejinta,  shaqada VLOOKUP waxay shaqayn kartaa oo keliya haddii xogta aan rabno inaan soo saarno ay ku taal tiirka dhinaca midig ee waxa aan isku dayeyno inaan ku dhigno ( Garoonka Model Baabuurka^{(Car Model )} ).

Si kale haddii loo dhigo, qaabkan shaxda, maadaama aan isku dayeyno inaan ku dhigno iyada oo ku saleysan Habka Baabuurka^{(Car Model)} , macluumaadka kaliya ee aan heli karno waa Midabka^{(Color )} (Ma aha aqoonsiga^(ID) sida tiirka aqoonsiga^(ID)  uu ku yaal dhinaca bidix ee  tiirka Model Baabuurka^{(Car Model )} .)

Tani waa sababta oo ah VLOOKUP , qiimaha raadinta waa inuu ka soo baxaa tiirarka koowaad iyo tiirarka raadinta waa inay ahaadaan midig. Shuruudahaas midkoodna laguma daboolin tusaaleheenna.

Akhbaarta wanaagsan ayaa ah, INDEX-MATCH  waxay awood u yeelan doontaa inay naga caawiso gaaritaanka tan. Ficil ahaan, tani dhab ahaantii waxay isku daraysaa laba hawlood oo Excel ah oo si gaar ah u shaqayn kara: function INDEX iyo function MATCH .

Si kastaba ha ahaatee, ujeedada maqaalkan, waxaan ka hadli doonaa oo kaliya isku darka labadaba iyada oo ujeedadu tahay in la soo koobo shaqada VLOOKUP .

Habkani wuxuu u ekaan karaa inuu xoogaa dheer yahay oo cabsi gelin marka hore. Si kastaba ha ahaatee, marka aad isticmaasho dhowr jeer, waxaad baran doontaa syntax by wadnaha.

Tani waa qaacidada buuxda ee tusaalaheena:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Waa kan kala-goynta qayb kasta

=INDEX( - “=” waxay tilmaamaysaa bilawga caanaha unugga iyo INDEX waa qaybta koowaad ee shaqada Excel ee aan isticmaaleyno.

CarType!$A$2:$A$5 - tiirarka xaashida CarType  halkaasoo xogta aanu rabno inaan soo saarno ay ku jirto. Tusaalahan, aqoonsiga^(ID)  nooc kasta oo baabuur.^{(Car Model.)}

MATCH ( - Qaybta labaad ee shaqada Excel ee aan isticmaaleyno.

B4 - Unugga ka kooban qoraalka raadinta ee aan isticmaaleyno ( Qaabka Gaariga^{(Car Model)} ) .

CarType!$B$2:$B$5 - Tiirarka xaashida CarType   oo ay wataan xogta aan u isticmaali doono si aan u dhigno qoraalka raadinta.

0)) - Si loo muujiyo in qoraalka raadinta uu si sax ah ula mid yahay qoraalka ku jira tiirka u dhigma (ie CarType!$B$2:$B$5 ). Haddii u dhigma saxda ah aan la helin, qaacidada ayaa soo noqoneysa #N/A .

Fiiro gaar ah: xasuuso xidhitaanka labanlaabka ah ee dhamaadka shaqadan "))" iyo hakadka u dhexeeya doodaha.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Shakhsi ahaan waan ka guuray VLOOKUP oo hadda waxaan isticmaalaa INDEX-MATCH maadaama ay awood u leedahay inay qabato wax ka badan VLOOKUP .

Hawlaha INDEX-MATCH waxay kaloo leeyihiin faa'iidooyin kale marka la barbar dhigo VLOOKUP :

1. Xisaabinta Degdega ah^{(Faster Calculations)}

Marka aan ku shaqeyneyno xog-ururin ballaaran oo xisaabinta lafteedu ay qaadan karto waqti dheer sababtoo ah hawlo badan oo VLOOKUP ah, waxaad ogaan doontaa marka aad ku beddelato dhammaan hababkaas INDEX-MATCH^{(INDEX-MATCH)} , xisaabinta guud ayaa si dhakhso ah loo xisaabin doonaa.

1. Looma baahna in la tiriyo tirooyinka Qaraabada^{(No Need to Count Relative Columns)}

Haddii miiskayaga tixraacu uu haysto qoraalka muhiimka ah ee aan rabno inaan ka raadino tiirka C iyo xogta aan u baahanahay inaan helno waxay ku taal tiirka AQ , waxaan u baahan doonaa inaan ogaano / tirino inta tiir ee u dhexeeya tiirka C iyo tiirka AQ marka la isticmaalayo VLOOKUP .

Iyadoo la adeegsanayo  INDEX-MATCH , waxaan si toos ah u dooran karnaa tiirka tusaha (ie column AQ) halkaas oo aan u baahanahay inaan helno xogta oo aan doorano tiirka la isku dhejinayo (ie tiirka C).

1. Waxay u egtahay mid aad u adag^{(It Looks More Complicated)}

VLOOKUP waa wax caadi ah maalmahan, laakiin dad badan ma oga isticmaalka hawlaha INDEX-MATCH.

Xadhiga dheer ee INDEX-MATCH wuxuu kaa caawinayaa inuu kaa dhigo sidii khabiir ku saabsan maaraynta hawlaha Excel ee adag iyo kuwa horumarsan . Ku raaxayso!

When to Use Index-Match Instead of VLOOKUP in Excel

For thoѕe of you who are well-versed in Excel, you are most likely very familiar wіth the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

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

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Hal-Column iyo Xog-urur badan oo Aasaaska ah oo ku kala soocida xaashida faafinta ee Excel

• Waa maxay VBA Array ee Excel iyo Sida loo Barnaamijyo Mid

• Sida loo Furo Word iyo Excel si Badbaado ah

• Sida loo raad raaco Ku-tiirsanaanta gudaha Excel

• Sida Dhakhso Loo Geliyo Safaf Badan gudaha Excel

• Sida looga saaro Gridlines gudaha Excel

• Si degdeg ah ugu qor nambarada Word iyo Excel

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Sida loo xarfo xarfaha Excel

• 10 Talooyin iyo Tabaha Excel ee 2019

• 3 Siyood Oo Unugyada Excel Loogu Kala Qaybiyo

• Sida loo xisaabiyo Z-Score gudaha Excel

• Xarunta Xogta Xaashida Shaqada ee Excel si loo daabaco

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• Sida loo sameeyo shaxda Pie ee Excel