Bakit mas mahusay ang INDEX at MATCH kaysa sa VLOOKUP sa Excel

Nauna naming ipinaliwanag sa mga nagsisimula kung paano gamitin ang mga pangunahing pag-andar ng VLOOKUP (Ingles na VLOOKUP, ang abbreviation ay nangangahulugang "vertical lookup function"). At ang mga nakaranasang user ay ipinakita ng ilang mas kumplikadong mga formula.

At sa artikulong ito susubukan naming magbigay ng impormasyon tungkol sa isa pang paraan ng pagtatrabaho sa patayong paghahanap.

Maaaring nagtataka ka: "Bakit kailangan ito?". At ito ay kinakailangan upang maipakita ang lahat ng posibleng paraan ng paghahanap. Bilang karagdagan, maraming mga paghihigpit sa VLOOKUP ang kadalasang pumipigil sa pagkuha ng ninanais na resulta. Kaugnay nito, ang INDEX( ) MATCH( ) ay higit na gumagana at magkakaibang, at mayroon din silang mas kaunting mga paghihigpit.

Pangunahing INDEX MATCH

Dahil ang layunin ng gabay na ito ay ipakita kung gaano kahusay ang feature na ito, kami Tingnan natin ang pangunahing impormasyon tungkol sa mga prinsipyo ng pagpapatakbo nito. At magpapakita kami ng mga halimbawa, at isaalang-alang din kung bakit, ito ay mas mahusay kaysa sa VLOOKUP ().

INDEX Function Syntax at Paggamit

Ang function na ito ay tumutulong sa paghahanap ng nais na halaga sa mga tinukoy na lugar ng paghahanap batay sa numero ng hanay o linya. Syntax:

=INDEX(array, row number, column number):

• array – ang lugar kung saan magaganap ang paghahanap;
• numero ng linya – ang bilang ng linyang hahanapin sa tinukoy na hanay. Kung ang numero ng hilera ay hindi kilala, ang numero ng hanay ay dapat na tukuyin;
• numero ng column – ang numero ng column na makikita sa tinukoy na array. Kung hindi alam ang halaga, kailangan ng numero ng linya.

Isang halimbawa ng isang simpleng formula:

=INDEX(A1:S10,2,3)

Ang function ay maghahanap sa hanay mula A1 hanggang C10. Ipinapakita ng mga numero kung saang row (2) at column (3) ipapakita ang gustong halaga. Ang magiging resulta ay cell C2.

Medyo simple, tama? Ngunit kapag nagtatrabaho ka sa mga tunay na dokumento, malamang na hindi ka magkaroon ng impormasyon tungkol sa mga numero ng column o mga cell. Para sa iyon ang MATCH() function.

MATCH Function Syntax at Paggamit

Ang MATCH() function ay naghahanap para sa nais na halaga at ipinapakita ang tinatayang numero nito sa tinukoy na lugar ng paghahanap.

Ang searchpos() syntax ay ganito ang hitsura:

=MATCH(value to lookup, array to lookup, match type)

• halaga ng paghahanap – ang numero o tekstong makikita;
• searched array – ang lugar kung saan magaganap ang paghahanap;
• uri ng pagtutugma – tumutukoy kung hahanapin ang eksaktong halaga o ang mga halaga na pinakamalapit dito:
  • 1 (o walang tinukoy na halaga) – ibinabalik ang pinakamalaking halaga na katumbas o mas mababa sa halaga na tinukoy;
  • 0 – nagpapakita ng eksaktong tugma sa hinanap na halaga. Sa kumbinasyong INDEX() MATCH() ay halos palaging kailangan mo ng eksaktong tugma, kaya nagsusulat kami ng 0;
  • -1 – Ipinapakita ang pinakamaliit na value na mas malaki kaysa o katumbas ng value na tinukoy sa formula. Ang pag-uuri ay isinasagawa sa pababang pagkakasunud-sunod.

Halimbawa, sa hanay na B1:B3 New York, Paris, London ay nakarehistro. Ipapakita ng formula sa ibaba ang numero 3 dahil pangatlo ang London sa listahan:

=EXPOSE(London,B1:B3,0)

Paano gamitin ang function na INDEX MATCH 

Marahil ay nagsimula ka nang maunawaan ang prinsipyo kung saan binuo ang magkasanib na gawain ng mga function na ito. Sa madaling salita, kung gayon Ang INDEX() ay naghahanap ng gustong halaga sa mga tinukoy na row at column. At ang MATCH() ay nagpapakita ng mga numero ng mga halagang ito:

=INDEX(column kung saan ibinalik ang value, MATCH(value to search, column to search in, 0))

Nahihirapan pa rin bang unawain kung paano ito gumagana? Marahil ang isang halimbawa ay magpapaliwanag nang mas mahusay. Ipagpalagay na mayroon kang isang listahan ng mga kabisera ng mundo at ang kanilang populasyon:

Upang malaman ang laki ng populasyon ng isang tiyak na kabisera, halimbawa, ang kabisera ng Japan, ginagamit namin ang sumusunod na formula:

=INDEX(C2:C10, MATCH(Japan, A2:A10,0))

paliwanag:

• Hinahanap ng MATCH() function ang value – “Japan” sa array A2:A10 at ibinabalik ang numero 3, dahil ang Japan ang pangatlong value sa listahan. 
• Ang figure na ito ay papunta sanumero ng linya” sa INDEX() formula at nagsasabi sa function na mag-print ng value mula sa row na ito.

Kaya ang formula sa itaas ay nagiging karaniwang formula INDEX(C2:C10,3). Ang formula ay naghahanap mula sa mga cell C2 hanggang C10 at nagbabalik ng data mula sa ikatlong cell sa hanay na ito, iyon ay, C4, dahil ang countdown ay nagsisimula sa pangalawang row.

Hindi nais na magreseta ng pangalan ng lungsod sa formula? Pagkatapos ay isulat ito sa anumang cell, sabihin ang F1, at gamitin ito bilang reference sa MATCH() formula. At magtatapos ka sa isang dynamic na formula sa paghahanap:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Mahalaga! Bilang ng mga linya sa ayos Ang INDEX() ay dapat na kapareho ng bilang ng mga row sa loob itinuturing na array sa MATCH(), kung hindi ay makakakuha ka ng maling resulta.

Sandali lang, bakit hindi na lang gamitin ang VLOOKUP() formula?

=VLOOKUP(F1, A2:C10, 3, Mali)

 Ano ang silbi ng pag-aaksaya ng oras sa pagsisikap na alamin ang lahat ng mga kumplikadong ito ng INDEX MATCH?

Sa kasong ito, hindi mahalaga kung aling function ang gagamitin. Ito ay isang halimbawa lamang upang maunawaan kung paano gumagana ang INDEX() at MATCH() na mga function. Ipapakita ng iba pang mga halimbawa kung ano ang kaya ng mga function na ito sa mga sitwasyon kung saan walang kapangyarihan ang VLOOKUP. 

INDEX MATCH o VLOOKUP

Kapag nagpapasya kung aling formula sa paghahanap ang gagamitin, marami ang sumasang-ayon na ang INDEX() at MATCH() ay higit na nakahihigit sa VLOOKUP. Gayunpaman, maraming tao pa rin ang gumagamit ng VLOOKUP(). Una, ang VLOOKUP() ay mas simple, at pangalawa, hindi lubos na nauunawaan ng mga user ang lahat ng mga pakinabang ng pagtatrabaho sa INDEX() at MATCH(). Kung wala ang kaalamang ito, walang sinuman ang sasang-ayon na gugulin ang kanilang oras sa pag-aaral ng isang komplikadong sistema.

Narito ang mga pangunahing bentahe ng INDEX() at MATCH() sa VLOOKUP():

• Maghanap mula kanan hanggang kaliwa. Ang VLOOKUP() ay hindi makakapaghanap mula kanan pakaliwa, kaya ang mga value na iyong hinahanap ay dapat palaging nasa pinakakaliwang column ng talahanayan. Ngunit ang INDEX() at MATCH() ay kayang hawakan ito nang walang problema. Sasabihin sa iyo ng artikulong ito kung ano ang hitsura nito sa pagsasanay: kung paano hanapin ang nais na halaga sa kaliwang bahagi.

1. Ligtas na pagdaragdag o pag-alis ng mga column. Ang VLOOKUP() formula ay nagpapakita ng mga maling resulta kapag nag-aalis o nagdaragdag ng mga column dahil kailangan ng VLOOKUP() ang eksaktong column number para maging matagumpay. Naturally, kapag idinagdag o inalis ang mga column, nagbabago rin ang kanilang mga numero. 

At sa mga formula ng INDEX() at MATCH(), isang hanay ng mga column ang tinukoy, hindi mga indibidwal na column. Bilang resulta, maaari mong ligtas na magdagdag at mag-alis ng mga column nang hindi kinakailangang i-update ang formula sa bawat pagkakataon.

1. Walang limitasyon sa dami ng paghahanap. Kapag gumagamit ng VLOOKUP(), ang kabuuang bilang ng mga pamantayan sa paghahanap ay hindi dapat lumampas sa 255 character o makakakuha ka ng #VALUE! Kaya kung ang iyong data ay naglalaman ng isang malaking bilang ng mga character, INDEX() at MATCH() ay ang pinakamahusay na pagpipilian.
2. Mataas na bilis ng pagproseso. Kung ang iyong mga talahanayan ay medyo maliit, malamang na hindi mo mapansin ang anumang pagkakaiba. Ngunit, kung ang talahanayan ay naglalaman ng daan-daan o libu-libong mga hilera, at, nang naaayon, mayroong daan-daan at libu-libong mga formula, ang INDEX () at MATCH () ay haharapin nang mas mabilis kaysa sa VLOOKUP (). Ang katotohanan ay ang Excel ay magpoproseso lamang ng mga haligi na tinukoy sa formula, sa halip na iproseso ang buong talahanayan. 

Ang epekto sa pagganap ng VLOOKUP() ay lalong kapansin-pansin kung ang iyong worksheet ay naglalaman ng malaking bilang ng mga formula tulad ng VLOOKUP() at SUM(). Ang mga hiwalay na pagsusuri ng mga function ng VLOOKUP() ay kinakailangan upang mai-parse ang bawat halaga sa isang array. Kaya kailangang iproseso ng Excel ang isang malaking halaga ng impormasyon, at ito ay makabuluhang nagpapabagal sa trabaho.

Mga Halimbawa ng Formula 

Nalaman na natin ang pagiging kapaki-pakinabang ng mga pag-andar na ito, kaya maaari tayong magpatuloy sa pinaka-kagiliw-giliw na bahagi: ang aplikasyon ng kaalaman sa pagsasanay.

Formula upang maghanap mula kanan hanggang kaliwa

Gaya ng nabanggit na, hindi maisagawa ng VLOOKUP ang ganitong paraan ng paghahanap. Kaya, kung ang mga nais na halaga ay wala sa pinakakaliwang hanay, ang VLOOKUP() ay hindi gagawa ng resulta. Ang INDEX() at MATCH() function ay mas maraming nalalaman, at ang lokasyon ng mga value ay hindi gumaganap ng malaking papel para gumana ang mga ito.

Halimbawa, magdaragdag kami ng hanay ng ranggo sa kaliwang bahagi ng aming talahanayan at susubukan naming alamin kung anong ranggo sa mga tuntunin ng populasyon na sinasakop ng kabisera ng Ating Bansa.

Sa cell G1, isinusulat namin ang halagang makikita, at pagkatapos ay gamitin ang sumusunod na formula upang maghanap sa hanay na C1:C10 at ibalik ang katumbas na halaga mula sa A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Prompt. Kung plano mong gamitin ang formula na ito para sa maraming mga cell, tiyaking ayusin mo ang mga saklaw gamit ang ganap na pagtugon (halimbawa, $A$2: $A$10 at $C$2: 4C$10).

INDEX MORE EXPOSED MORE EXPOSED  upang maghanap sa mga column at row

Sa mga halimbawa sa itaas, ginamit namin ang mga function na ito bilang isang kapalit para sa VLOOKUP() upang ibalik ang mga halaga mula sa isang paunang natukoy na hanay ng mga hilera. Ngunit paano kung kailangan mong gumawa ng isang matrix o dalawang panig na paghahanap?

Mukhang kumplikado, ngunit ang formula para sa mga naturang kalkulasyon ay katulad ng karaniwang INDEX() MATCH() formula, na may isang pagkakaiba lamang: ang MATCH() formula ay dapat gamitin nang dalawang beses. Ang unang pagkakataon upang makuha ang numero ng row, at ang pangalawang pagkakataon upang makuha ang numero ng column:

=INDEX(array, MATCH(vertical search value, search column, 0), MATCH(horizontal search value, search row, 0))

Tingnan natin ang talahanayan sa ibaba at subukang gumawa ng formula INDEX() EXPRESS() EXPRESS() upang maipakita ang mga demograpiko sa isang partikular na bansa para sa isang napiling taon.

Ang target na bansa ay nasa cell G1 (vertical lookup) at ang target na taon ay nasa cell G2 (horizontal lookup). Ang formula ay magiging ganito:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Paano gumagana ang formula na ito

Tulad ng anumang iba pang kumplikadong mga formula, mas madaling maunawaan ang mga ito sa pamamagitan ng paghahati-hati sa mga ito sa mga indibidwal na equation. At pagkatapos ay mauunawaan mo kung ano ang ginagawa ng bawat indibidwal na function:

• MATCH(G1,A2:A11,0) – naghahanap ng halaga (G1) sa hanay na A2:A11 at ipinapakita ang bilang ng halagang ito, sa aming kaso ito ay 2;
• PAGHAHANAP(G2,B1:D1,0) – naghahanap ng value (G2) sa hanay na B1:D1. Sa kasong ito, ang resulta ay 3.

Ang nahanap na mga numero ng row at column ay ipinapadala sa katumbas na halaga sa INDEX() formula:

=INDEX(B2:D11,2,3)

Bilang resulta, mayroon kaming value na nasa isang cell sa intersection ng 2 row at 3 column sa range B2:D11. At ipinapakita ng formula ang nais na halaga, na nasa cell D3.

Maghanap sa pamamagitan ng maraming kundisyon na may INDEX at MATCH

Kung nabasa mo na ang aming gabay sa VLOOKUP(), malamang na sinubukan mo ang maramihang mga formula sa paghahanap. Ngunit ang paraan ng paghahanap na ito ay may isang makabuluhang limitasyon - ang pangangailangan na magdagdag ng isang pantulong na hanay.

Ngunit ang mabuting balita ay iyon Sa INDEX() at MATCH() maaari kang maghanap ng maraming kundisyon nang hindi kinakailangang i-edit o baguhin ang iyong worksheet.

Narito ang pangkalahatang formula sa paghahanap ng maraming kundisyon para sa INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Ang tala: dapat gamitin ang formula na ito kasama ng keyboard shortcut CTRL+SHIFT+ENTER.

Ipagpalagay na kailangan mong hanapin ang halaga na iyong hinahanap batay sa 2 kundisyon: Suki и Produkto.

Nangangailangan ito ng sumusunod na formula:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Sa formula na ito, ang C2:C10 ay ang hanay kung saan magaganap ang paghahanap, F1 - ang kondisyong ito, A2:A10 — ay ang hanay upang ihambing ang kundisyon, F2 – kondisyon 2, V2: V10 – saklaw para sa paghahambing ng kondisyon 2.

Huwag kalimutang pindutin ang kumbinasyon sa dulo ng trabaho kasama ang formula CTRL+SHIFT+ENTER – Awtomatikong isasara ng Excel ang formula gamit ang mga kulot na braces, tulad ng ipinapakita sa halimbawa:

Kung ayaw mong gumamit ng array formula para sa iyong trabaho, pagkatapos ay magdagdag ng isa pang INDEX() sa formula at pindutin ang ENTER, magiging ganito ang hitsura sa halimbawa:

Paano gumagana ang mga formula na ito

Gumagana ang formula na ito sa parehong paraan tulad ng karaniwang INDEX() MATCH() formula. Para maghanap ng maraming kundisyon, gagawa ka lang ng maramihang False at True na kundisyon na kumakatawan sa tama at maling indibidwal na kundisyon. At pagkatapos ay nalalapat ang mga kundisyong ito sa lahat ng kaukulang elemento ng array. Ang formula ay nagko-convert ng False at True argument sa 0 at 1, ayon sa pagkakabanggit, at naglalabas ng array kung saan ang 1 ay ang mga tumutugmang value na natagpuan sa string. Hahanapin ng MATCH() ang unang value na tumutugma sa 1 at ipapasa ito sa INDEX() formula. At ito, sa turn, ay magbabalik ng nais na halaga sa tinukoy na linya mula sa nais na hanay.

Ang isang formula na walang array ay nakasalalay sa kakayahan ng INDEX() na pangasiwaan ang mga ito nang mag-isa. Ang pangalawang INDEX() sa formula ay tumutugma sa falsy (0), kaya ipinapasa nito ang buong array na may mga value na iyon sa MATCH() formula. 

Ito ay isang medyo mahabang paliwanag ng lohika sa likod ng formula na ito. Para sa karagdagang impormasyon basahin ang artikulong "INDEX MATCH na may maraming kundisyon'.

AVERAGE, MAX at MIN sa INDEX at MATCH

Ang Excel ay may sariling mga espesyal na function para sa paghahanap ng mga average, maximum, at minimum. Ngunit paano kung gusto mong makakuha ng data mula sa cell na nauugnay sa mga halagang iyon? Sa kasong ito AVERAGE, MAX at MIN ay dapat gamitin kasabay ng INDEX at MATCH.

INDEX MATCH at MAX

Upang mahanap ang pinakamalaking halaga sa column D at ipakita ito sa column C, gamitin ang formula: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH at MIN

Upang mahanap ang pinakamaliit na halaga sa column D at ipakita ito sa column C, gamitin ang sumusunod na formula:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX at SERPENT

Upang mahanap ang average na halaga sa column D at ipakita ang halagang ito sa C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Depende sa kung paano isinulat ang iyong data, ang ikatlong argumento sa MATCH() ay alinman sa 1, 0, o -1:

• kung ang mga haligi ay pinagsunod-sunod sa pataas na pagkakasunud-sunod, itakda ang 1 (pagkatapos ay kakalkulahin ng formula ang maximum na halaga, na mas mababa sa o katumbas ng average na halaga);
• kung ang pag-uuri ay pababang, pagkatapos ay -1 (ang formula ay maglalabas ng pinakamababang halaga na mas malaki kaysa o katumbas ng average);
• kung ang lookup array ay naglalaman ng value na eksaktong katumbas ng average, pagkatapos ay itakda ito sa 0. 

 Sa aming halimbawa, ang populasyon ay pinagsunod-sunod sa pababang pagkakasunud-sunod, kaya inilalagay namin ang -1. At ang resulta ay Tokyo, dahil ang halaga ng populasyon (13,189) ay ang pinakamalapit sa average na halaga (000).

Ang VLOOKUP() ay maaari ding magsagawa ng mga naturang kalkulasyon, ngunit bilang isang array formula lamang: VLOOKUP na may AVERAGE, MIN at MAX.

INDEX MATCH at ESND/IFERROR

Marahil ay napansin mo na kung ang formula ay hindi mahanap ang nais na halaga, ito ay nagtatapon ng isang error # N / A. Maaari mong palitan ang karaniwang mensahe ng error ng isang bagay na mas nagbibigay-kaalaman. Halimbawa, itakda ang argumento sa formula Sa ika-XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Sa formula na ito, kung maglalagay ka ng data na wala sa talahanayan, ibibigay sa iyo ng form ang tinukoy na mensahe.

Kung nais mong mahuli ang lahat ng mga error, pagkatapos ay maliban sa Sa ika-XNUMX ay maaaring magamit IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "May nangyaring mali!")

Ngunit tandaan na ang pag-mask ng mga error sa ganitong paraan ay hindi magandang ideya, dahil ang mga karaniwang error ay nag-uulat ng mga paglabag sa formula.

Umaasa kaming nakatulong ang aming gabay sa paggamit ng INDEX MATCH() function.