Kedu ihe kpatara INDEX na MATCH ji dị mma karịa VLOOKUP na Excel

Anyị kọwaburu ụzọ nye ndị mbido ka esi eji ọrụ bụ isi nke VLOOKUP (Bekee VLOOKUP, ndebiri na-anọchite anya “ọrụ nyocha kwụ ọtọ”). E gosikwara ndị ọrụ nwere ahụmahụ ọtụtụ usoro mgbagwoju anya.

Na n'isiokwu a, anyị ga-agbalị inye ozi gbasara usoro ọzọ nke na-arụ ọrụ na vetikal search.

Ị nwere ike ịnọ na-eche: "Gịnị kpatara nke a ji dị mkpa?". Na nke a dị mkpa iji gosi ụzọ ọchụchọ niile enwere ike. Na mgbakwunye, ọtụtụ mmachi VLOOKUP na-egbochikarị inweta nsonaazụ achọrọ. N'akụkụ a, INDEX () MATCH () na-arụ ọrụ na iche iche, na ha nwekwara obere mgbochi.

Ihe ndabere INDEX MACH

Ebe ọ bụ na ebumnuche nke ntuziaka a bụ igosi etu njirimara a si dị mma, anyị Ka anyị leba anya na ozi bụ isi gbasara ụkpụrụ nke ọrụ ya. Anyị ga-egosikwa ihe atụ, ma tụlee ihe kpatara ya, ọ dị mma karịa VLOOKUP ().

Njikọ ọrụ INDEX na ojiji

Ọrụ a na-enyere aka ịchọta uru achọrọ n'etiti mpaghara ọchụchọ akọwapụtara dabere na kọlụm ma ọ bụ nọmba ahịrị. Ndekọ:

=INDEX(usoro, nọmba ahịrị, nọmba kọlụm):

  • n'usoro - mpaghara ebe a ga-achọ ya;
  • akara akara - ọnụọgụ nke ahịrị a ga-achọ n'usoro akọwapụtara. Ọ bụrụ na amaghi nọmba ahịrị, a ga-akọwarịrị nọmba kọlụm;
  • nọmba kọlụm - ọnụ ọgụgụ nke kọlụm a ga-ahụ n'usoro akọwapụtara. Ọ bụrụ na amaghi uru ọ bara, achọrọ nọmba ahịrị.

Ihe atụ nke usoro dị mfe:

=INDEX (A1:S10,2,3)

Ọrụ a ga-achọ na nso A1 ruo C10. Ọnụọgụ ndị ahụ na-egosi ahịrị (2) na kọlụm (3) iji gosi uru achọrọ si. Nsonaazụ ga-abụ cell C2.

Mara mma dị mfe, nri? Mana mgbe iji ezigbo akwụkwọ na-arụ ọrụ, o yighị ka ị ga-enwe ozi gbasara nọmba kọlụm ma ọ bụ sel. Nke ahụ bụ ihe ọrụ MATCH() bụ maka.

Njikọ ọrụ MATCH na ojiji

Ọrụ MATCH() na-achọ uru achọrọ wee gosi ọnụọgụ ya na mpaghara ọchụchọ akọwapụtara.

Okwu searchpos() dị ka nke a:

=MATCH (uru iji lelee, nhazi ihe nleba anya, ụdị egwuregwu)

  • uru ọchụchọ - ọnụọgụ ma ọ bụ ederede a ga-achọta;
  • usoro nyocha - mpaghara ebe a ga-eme nchọpụta;
  • ụdị egwuregwu - na-akọwapụta ma ọ ga-achọ uru ọ bara ma ọ bụ ụkpụrụ kacha nso ya:
    • 1 (ma ọ bụ enweghị uru akọwapụtara) - weghachi uru kachasị ukwuu nke hà nhata ma ọ bụ na-erughị uru nke akọwapụtara;
    • 0 - na-egosi ezigbo egwuregwu yana uru achọrọ. Na nchikota INDEX () MATCH () ị ga-achọkarị otu egwuregwu, yabụ anyị na-ede 0;
    • -1 - Na-egosiputa uru kacha nta karịa ma ọ bụ nhata na uru akọwapụtara na usoro. A na-eme nhazi usoro n'usoro mgbada.

Dịka ọmụmaatụ, na nso B1:B3 New York, Paris, London ka edebanye aha. Usoro dị n'okpuru ga-egosi nọmba 3 n'ihi na London bụ nke atọ na ndepụta:

= EXPOSE(London,B1:B3,0)

Otu esi arụ ọrụ na ọrụ INDEX MATCH 

Eleghị anya ị malitela ịghọta ụkpụrụ nke eji arụkọ ọrụ ọnụ nke ọrụ ndị a. Na nkenke, mgbe ahụ INDEX() na-achọ uru achọrọ n'etiti ahịrị na kọlụm akọwapụtara. Na MATCH() na-egosi ọnụọgụ nke ụkpụrụ ndị a:

=INDEX(kọlụm nke esi na ya weghachi uru, MATCH(uru ịchọ, kọlụm iji chọọ na, 0))

Ọ ka na-esiri gị ike ịghọta ka ọ na-arụ ọrụ? Ma eleghị anya, ihe atụ ga-akọwa nke ọma. Were ya na ị nwere ndepụta isi obodo ụwa na ọnụ ọgụgụ ha:

Iji chọpụta ọnụ ọgụgụ ndị bi n'otu isi obodo, dịka ọmụmaatụ, isi obodo Japan, anyị na-eji usoro a:

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

Nkọwa:

  • Ọrụ MATCH () na-achọ uru - "Japan" na nhazi A2: A10 wee weghachite nọmba 3, n'ihi na Japan bụ uru nke atọ na ndepụta ahụ. 
  • Ọnụọgụ a na-aganọmba akara” na usoro INDEX() wee gwa ọrụ ka ibipụta uru site na ahịrị a.

Ya mere usoro a dị n'elu na-aghọ usoro ọkọlọtọ INDEX (C2:C10,3). Usoro a na-achọ site na mkpụrụ ndụ C2 ruo C10 wee weghachi data sitere na cell nke atọ dị na nso a, ya bụ, C4, n'ihi na ngụkọ na-amalite site n'ahịrị nke abụọ.

Ịchọghị idepụta aha obodo na usoro? Wee dee ya na cell ọ bụla, kwuo F1, wee jiri ya dịka ntụnye aka na usoro MATCH(). Ị mechaa jiri usoro ọchụchọ dị ike:

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

Kedu ihe kpatara INDEX na MATCH ji dị mma karịa VLOOKUP na Excel

Ihe dị mkpa! Ọnụọgụ ahịrị dị na ụdị INDEX() ga-abụrịrị otu na ọnụọgụ ahịrị dị na atụle n'usoro na MATCH (), ma ọ bụghị ya, ị ga-enweta nsonaazụ na-ezighi ezi.

Chere otu nkeji, gịnị kpatara na ị gaghị eji naanị usoro VLOOKUP()?

= VLOOKUP (F1, A2:C10, 3, Ụgha)

 Kedu ihe bụ isi n'iyi oge na-agbalị ịchọta mgbagwoju anya ndị a niile nke INDEX MATCH?

N'okwu a, ọ baghị uru ọrụ ị ga-eji. Nke a bụ naanị ọmụmaatụ iji ghọta ka ọrụ INDEX() na MATCH() si arụkọ ọrụ ọnụ. Ihe atụ ndị ọzọ ga-egosi ihe ọrụ ndị a nwere ike na ọnọdụ ebe VLOOKUP enweghị ike. 

INDEX MATCH ma ọ bụ VLOOKUP

Mgbe ị na-ekpebi usoro ọchụchọ ị ga-eji, ọtụtụ ndị kwenyere na INDEX() na MATCH() dị elu karịa VLOOKUP. Agbanyeghị, ọtụtụ mmadụ ka na-eji VLOOKUP(). Nke mbụ, VLOOKUP () dị mfe, na nke abụọ, ndị ọrụ anaghị aghọtacha uru niile dị na INDEX () na MATCH (). Enweghị ihe ọmụma a, ọ dịghị onye ga-ekweta iji oge ha na-amụ usoro mgbagwoju anya.

Nke a bụ uru bụ isi nke INDEX() na MATCH() karịa VLOOKUP():

 

  • Chọọ site n'aka nri gaa n'aka ekpe. VLOOKUP() enweghị ike ịchọ site n'aka nri gaa n'aka ekpe, yabụ ụkpụrụ ị na-achọ ga-adịrịrị na kọlụm aka ekpe nke tebụl. Mana INDEX () na MATCH () nwere ike ijikwa nke a n'enweghị nsogbu. Isiokwu a ga-agwa gị ihe ọ dị na omume: otu esi achọta uru achọrọ n'akụkụ aka ekpe.

 

  1. Nchekwa mgbakwunye ma ọ bụ mwepụ nke ogidi. Usoro VLOOKUP() na-egosi nsonaazụ na-ezighi ezi mgbe ị na-ewepụ ma ọ bụ na-agbakwunye kọlụm n'ihi na VLOOKUP() chọrọ nọmba kọlụm ahụ ka ọ nwee ihe ịga nke ọma. Dị ka o kwesịrị ịdị, mgbe agbakwunyere ma ọ bụ wepụ ogidi, ọnụọgụ ha na-agbanwekwa. 

Na na usoro INDEX() na MATCH(), a na-akọwapụta ọtụtụ kọlụm, ọ bụghị kọlụm nke ọ bụla. N'ihi ya, ị nwere ike tinye na wepu ogidi n'enweghị nsogbu na-enweghị imelite usoro ahụ oge ọ bụla.

  1. Enweghị oke na mpịakọta ọchụchọ. Mgbe ị na-eji VLOOKUP(), ngụkọta ọnụọgụ ọchụchọ agaghị agafe mkpụrụedemede 255 ma ọ bụ ị ga-enweta #VALUE! Yabụ ọ bụrụ na data gị nwere ọnụọgụ mkpụrụedemede dị ukwuu, INDEX() na MATCH() bụ nhọrọ kacha mma.
  2. Ọsọ nhazi dị elu. Ọ bụrụ na tebụl gị dị ntakịrị, mgbe ahụ ị gaghị achọpụta ọdịiche ọ bụla. Ma, ọ bụrụ na tebụl nwere ọtụtụ narị ahịrị ma ọ bụ puku kwuru puku ahịrị, na, ya mere, e nwere ọtụtụ narị na ọtụtụ puku usoro, INDEX () na MATCH () ga-anagide ngwa ngwa karịa VLOOKUP (). Nke bụ eziokwu bụ na Excel ga-ahazi naanị ogidi ndị akọwapụtara na usoro ahụ, kama ịhazi tebụl dum. 

Mmetụta arụmọrụ nke VLOOKUP() ga-apụta ìhè karịsịa ma ọ bụrụ na akwụkwọ ọrụ gị nwere ọnụ ọgụgụ buru ibu nke usoro dịka VLOOKUP() na SUM(). A chọrọ nlele iche iche nke ọrụ VLOOKUP() iji tụba uru ọ bụla n'usoro. Yabụ na Excel ga-ahazi nnukwu ozi, nke a na-ebelata ọrụ ahụ nke ukwuu.

Ihe Nlereanya Formula 

Anyị achọpụtalarị uru nke ọrụ ndị a, n'ihi ya, anyị nwere ike ịga n'ihu na akụkụ kachasị mmasị: itinye ihe ọmụma na omume.

Formula iji chọọ site n'aka nri gaa n'aka ekpe

Dịka e kwurula, VLOOKUP enweghị ike ịme ụdị ọchụchọ a. Yabụ, ọ bụrụ na ụkpụrụ achọrọ adịghị na kọlụm aka ekpe, VLOOKUP () agaghị arụpụta nsonaazụ. Ọrụ INDEX () na MATCH () na-agbanwe agbanwe, na ọnọdụ nke ụkpụrụ anaghị arụ ọrụ dị ukwuu ka ha rụọ ọrụ.

Dịka ọmụmaatụ, anyị ga-agbakwunye kọlụm ọkwa n'akụkụ aka ekpe nke tebụl anyị ma gbalịa chọpụta ọkwa ọkwa n'ihe gbasara ọnụ ọgụgụ ndị isi obodo nke obodo anyị nwere.

Na cell G1, anyị na-ede uru a ga-achọta, wee jiri usoro na-esonụ iji chọọ na nso C1: C10 wee weghachi uru kwekọrọ na A2: A10:

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

Kedu ihe kpatara INDEX na MATCH ji dị mma karịa VLOOKUP na Excel

Ngwa ngwa. Ọ bụrụ na ị na-eme atụmatụ iji usoro a maka ọtụtụ sel, hụ na ị na-edozi ọnụọgụ ahụ site na iji adreesị zuru oke (dịka ọmụmaatụ, $2: A$10 na $C$2: 4C$10).

INDEX ỌZỌ KWESỊRỊ ỌZỌ KWESỊRỊ  ka ịchọọ na kọlụm na ahịrị

N'ihe atụ ndị a dị n'elu, anyị ejirila ọrụ ndị a dị ka nnọchi maka VLOOKUP() iji weghachi ụkpụrụ site na ahịrị ndị eburula ụzọ kọwaa. Mana gịnị ma ọ bụrụ na ịchọrọ ịme matriks ma ọ bụ ọchụchọ ihu abụọ?

Ọ na-ada mgbagwoju anya, mana usoro maka mgbako ndị dị otú ahụ yiri ọkọlọtọ INDEX() MATCH(), nwere naanị otu ọdịiche: a ga-eji usoro MATCH() mee ihe ugboro abụọ. Oge mbụ iji nweta nọmba ahịrị, yana oge nke abụọ iji nweta nọmba kọlụm:

=INDEX(n'usoro, MATCH(uru ọchụchọ kwụ ọtọ, kọlụm ọchụchọ, 0), MATCH(uru ọchụchọ kwụ ọtọ, ahịrị ọchụchọ, 0))

Ka anyị leba anya na tebụl dị n'okpuru ma gbalịa ịme usoro INDEX() EXPRESS() EXPRESS() iji gosi ọnụọgụgụ igwe mmadụ n'otu obodo maka otu afọ ahọpụtara.

Mba e lekwasịrị anya dị na cell G1 (nyocha kwụ ọtọ) na afọ ebumnuche dị na cell G2 (nyocha kwụ ọtọ). Usoro a ga-adị ka nke a:

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

Kedu ihe kpatara INDEX na MATCH ji dị mma karịa VLOOKUP na Excel

Ka usoro a si arụ ọrụ

Dị ka ọ dị na usoro mgbagwoju anya ndị ọzọ, ha na-adị mfe nghọta site n'imebi ha n'otu n'otu n'otu n'otu. Ma mgbe ahụ ị nwere ike ịghọta ihe ọrụ onye ọ bụla na-eme:

  • MKPA (G1, A2: A11,0) - na-achọ uru (G1) na nso A2: A11 ma gosipụta ọnụ ọgụgụ nke uru a, n'ọnọdụ anyị ọ bụ 2;
  • SEARCH (G2,B1:D1,0) – na-achọ uru (G2) na oke B1:D1. N'okwu a, ihe si na ya pụta bụ 3.

A na-eziga nọmba ahịrị na kọlụm a chọtara na uru kwekọrọ na usoro INDEX():

=INDEX(B2:D11,2,3)

N'ihi ya, anyị nwere uru dị na cell na nkwụsị nke ahịrị abụọ na ogidi 2 na nso B3:D2. Na usoro na-egosi uru achọrọ, nke dị na cell D11.

Jiri INDEX na MATCH chọọ ọtụtụ ọnọdụ

Ọ bụrụ na ị gụọla ntuziaka anyị na VLOOKUP(), ikekwe ị nwala ọtụtụ usoro ọchụchọ. Ma usoro ọchụchọ a nwere otu njedebe dị ịrịba ama - mkpa ịgbakwunye kọlụm inyeaka.

Ma ozi ọma ahụ bụ Na INDEX() na MATCH() ị nwere ike ịchọ ọtụtụ ọnọdụ na-enweghị idezi ma ọ bụ gbanwee akwụkwọ ọrụ gị.

Nke a bụ usoro ọchụchọ ọtụtụ ọnọdụ maka INDEX() MATCH():

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

Ihe ndetu: a ga-ejirịrị usoro a yana ụzọ mkpirisi ahụigodo CTRL+ SHIFT+ ENTER.

Were ya na ịchọrọ ịchọta uru ị na-achọ dabere na ọnọdụ 2: asịwo и Ngwaahịa.

Nke a chọrọ usoro a:

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

N'ime usoro a, C2: C10 bụ oke ebe a ga-achọ ya, F1 - ọnọdụ a, A2: A10 — bụ oke iji tụnyere ọnọdụ ahụ, F2 - ọnọdụ 2, V2:V10 - oke maka ntụnyere ọnọdụ 2.

Echefula ịpị nchikota na njedebe nke ọrụ na usoro CTRL + SHIFT + Tinye - Excel ga-eji braces mechie usoro ahụ ozugbo, dị ka egosiri na ihe atụ:

Kedu ihe kpatara INDEX na MATCH ji dị mma karịa VLOOKUP na Excel

Ọ bụrụ na ịchọghị iji usoro nhazi maka ọrụ gị, tinyezie INDEX() ọzọ na usoro wee pịa ENTER, ọ ga-adị ka ihe atụ:

Kedu ihe kpatara INDEX na MATCH ji dị mma karịa VLOOKUP na Excel

Ka usoro ndị a si arụ ọrụ

Usoro a na-arụ ọrụ n'otu ụzọ ahụ dị ka ọkọlọtọ INDEX() MATCH(). Ka ịchọọ ọtụtụ ọnọdụ, naanị ị na-emepụta ọtụtụ ọnọdụ ụgha na eziokwu na-anọchi anya ọnọdụ onye ọ bụla ziri ezi na ezighi ezi. Mgbe ahụ, ọnọdụ ndị a na-emetụta ihe niile kwekọrọ n'usoro. Usoro ahụ na-atụgharị arụmụka Ụgha na Ezi na 0 na 1, n'otu n'otu, wee wepụta usoro ebe 1 bụ ụkpụrụ dabara adaba nke achọtara na eriri. MATCH() ga-achọta uru mbụ dabara na 1 wee nyefee ya na usoro INDEX(). Na ya, n'aka nke ya, ga-eweghachite uru a chọrọ ugbua na ahịrị a kapịrị ọnụ site na kọlụm achọrọ.

Usoro na-enweghị nhazi na-adabere na ike nke INDEX() ijikwa ha n'onwe ya. INDEX nke abụọ () na usoro a dabara adaba (0), yabụ ọ na-eji ụkpụrụ ndị ahụ gafere n'usoro niile na usoro MATCH (). 

Nke a bụ nkọwa dị ogologo nke ukwuu maka mgbagha dị n'azụ usoro a. Maka ozi ndị ọzọ gụọ akụkọ "INDEX MATCH nwere ọtụtụ ọnọdụ".

Ọkara, MAX na MIN na INDEX na MATCH

Excel nwere ọrụ pụrụ iche nke ya maka ịchọta nkezi, kacha na kacha nta. Mana gịnị ma ọ bụrụ na ịchọrọ ịnweta data sitere na cell metụtara ụkpụrụ ndị ahụ? N'okwu a AVERAGE, MAX na MIN ga-ejikọ na INDEX na MATCH.

INDEX MATCH na MAX

Ka ịchọta uru kachasị na kọlụm D wee gosipụta ya na kọlụm C, jiri usoro a: 

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

INDEX MATCH na MIN

Ka ịchọta uru kacha nta na kọlụm D wee gosipụta ya na kọlụm C, jiri usoro a:

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

SEARCH INDEX na agwọ

Ka ịchọta nkezi uru na kọlụm D wee gosipụta uru a na C:

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

Dabere na otu esi ede data gị, arụmụka nke atọ maka MATCH() bụ 1, 0, ma ọ bụ -1:

  • ọ bụrụ na a na-ahazi ogidi ndị ahụ n'usoro na-arịgo, setịpụ 1 (mgbe ahụ, usoro ahụ ga-agbakọọ ọnụ ahịa kachasị elu, nke na-erughị ma ọ bụ hà nhata na nkezi uru);
  • ọ bụrụ na ụdị ahụ na-agbada, mgbe ahụ -1 (usoro a ga-emepụta uru kacha nta nke dị ukwuu karịa ma ọ bụ hà nhata);
  • Ọ bụrụ n'usoro nchọgharị ahụ nwere uru dabara na nkezi, tinyezie ya na 0. 

 N'ihe atụ anyị, a na-ahazi ọnụ ọgụgụ ndị mmadụ n'usoro na-agbadata, ya mere anyị na-etinye -1. Ihe si na ya pụta bụ Tokyo, ebe ọ bụ na ọnụ ahịa ndị bi na ya (13,189) kacha nso na nkezi uru (000).

Kedu ihe kpatara INDEX na MATCH ji dị mma karịa VLOOKUP na Excel

VLOOKUP() nwekwara ike ịrụ ụdị mgbako a, mana ọ bụ naanị dịka usoro nhazi: VLOOKUP na AVERAGE, MIN na MAX.

INDEX MACH na ESND/IFERROR

Eleghị anya ị chọpụtala na ọ bụrụ na usoro ahụ enweghị ike ịchọta uru achọrọ, ọ na-atụfu njehie # N / E. Ị nwere ike iji ihe na-enye nkọwa karịa dochie ozi njehie ọkọlọtọ. Dịka ọmụmaatụ, tọọ arụmụka na usoro N'ime afọ iri na isii:

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

Site na usoro a, ọ bụrụ na itinye data na-adịghị na tebụl, ụdị ahụ ga-enye gị ozi akọwapụtara.

Kedu ihe kpatara INDEX na MATCH ji dị mma karịa VLOOKUP na Excel

Ọ bụrụ na ịchọrọ ijide njehie niile, ewezuga maka N'ime afọ iri na isii nwere ike iji IFEROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0))), "Onwere ihe na-agahie!")

Mana cheta na ikpuchi njehie n'ụzọ dị otú a abụghị ezigbo echiche, n'ihi na njehie ọkọlọtọ na-akọ mmebi na usoro.

Anyị na-atụ anya na ịchọtara ntuziaka anyị iji ọrụ INDEX MATCH() nyere aka.

Nkume a-aza