No ke aha ʻoi aku ka maikaʻi o INDEX a me MATCH ma mua o VLOOKUP ma Excel

Ua wehewehe mua mākou i ka poʻe hoʻomaka i ka hoʻohana ʻana i nā hana kumu o VLOOKUP (English VLOOKUP, ʻo ka pōkole ʻo "vertical lookup function"). A ua hōʻike ʻia nā mea hoʻohana ʻike i kekahi mau ʻano paʻakikī.

A ma kēia ʻatikala e hoʻāʻo mākou e hāʻawi i ka ʻike e pili ana i kahi ʻano hana ʻē aʻe o ka hana ʻana me ka ʻimi kū pololei.

E noʻonoʻo paha ʻoe: "No ke aha e pono ai kēia?". A pono kēia i mea e hōʻike ai i nā ʻano hulina āpau. Eia hou, nui nā palena VLOOKUP e pale pinepine i ka loaʻa ʻana o ka hopena i makemake ʻia. Ma keia mea, INDEX() MATCH() 'oi aku ka nui o ka hana a me ka like ole, a he kakaikahi ka lakou mau kapu.

Nā kumu kumu INDEX MATCH

ʻOiai ke kumu o kēia alakaʻi e hōʻike i ka maikaʻi o kēia hiʻohiʻona, mākou E nānā kākou i ka ʻike kumu e pili ana i nā loina o kāna hana. A e hōʻike mākou i nā hiʻohiʻona, a noʻonoʻo pū i ke kumu, ʻoi aku ka maikaʻi ma mua o VLOOKUP ().

INDEX Function Syntax a me ka hoʻohana

Kōkua kēia hana i ka loaʻa ʻana o ka waiwai i makemake ʻia ma waena o nā wahi huli i kuhikuhi ʻia ma muli o ke kolamu a i ʻole ka helu laina. Hanana:

=INDEX(huahelu, helu lālani, helu kolamu):

• array - kahi e huli ai;
• helu laina - ka helu o ka laina e ʻimi ʻia ma ka papa kuhikuhi. Inā ʻaʻole ʻike ʻia ka helu lālani, pono e kuhikuhi ʻia ka helu kolamu;
• helu kolamu - ka helu o ke kolamu e ʻike ʻia ma ka laʻana i ʻōlelo ʻia. Inā ʻaʻole ʻike ʻia ka waiwai, pono ka helu laina.

He laʻana o kahi ʻano maʻalahi:

=INDEX(A1:S10,2,3)

E ʻimi ka hana ma ka laulā mai A1 a C10. Hōʻike nā helu i ka lālani (2) a me ke kolamu (3) e hōʻike i ka waiwai i makemake ʻia. ʻO ka hopena ka cell C2.

He mea maʻalahi, pololei? Akā ke hana ʻoe me nā palapala maoli, ʻaʻole paha ʻoe e loaʻa ka ʻike e pili ana i nā helu kolamu a i ʻole nā ​​​​pūnaewele. ʻO ia ke kumu o ka MATCH() hana.

MATCH Function Syntax a me ka hoʻohana

Huli ka hana MATCH() i ka waiwai i makemake ʻia a hōʻike i kona helu kokoke ma ka wahi huli i ʻōlelo ʻia.

Penei ke ʻano o ka syntax searchpos()

= MATCH(waiwai no ka nānā ʻana, ʻano hoʻohālikelike i ka nānā ʻana, ʻano hoʻohālikelike)

• waiwai huli - ka helu a i ʻole kikokikona e loaʻa;
• huli ʻia - kahi e huli ai;
• ʻano hoʻohālikelike - e ʻike inā e ʻimi i ka waiwai kūpono a i ʻole nā ​​waiwai kokoke loa iā ia:
  • 1 (a i ʻole ka waiwai i hōʻike ʻia) – hoʻihoʻi i ka waiwai nui i like a i ʻole ka liʻiliʻi ma mua o ka waiwai i kuhikuhi ʻia;
  • 0 - hōʻike i kahi kūlike pololei me ka waiwai i ʻimi ʻia. Ma ka hui INDEX() MATCH() e makemake mau ʻoe i kahi hoʻohālikelike pololei, no laila kākau mākou i ka 0;
  • -1 - Hōʻike i ka waiwai liʻiliʻi i ʻoi aku ka nui a i ʻole like me ka waiwai i kuhikuhi ʻia ma ke ʻano. Hana ʻia ka hoʻokaʻawale ʻana ma ke ʻano iho.

No ka laʻana, ma ka laulā B1:B3 New York, Paris, Lādana ua hoʻopaʻa inoa ʻia. E hōʻike ana ke kumu ma lalo nei i ka helu 3 no ka mea ʻo Lākana ke kolu ma ka papa inoa.

=EXPOSE(Lakana, B1:B3,0)

Pehea e hana ai me ka hana INDEX MATCH 

Ua hoʻomaka paha ʻoe e hoʻomaopopo i ke kumu i kūkulu ʻia ai ka hana hui o kēia mau hana. I ka pōkole, a laila ʻImi ʻo INDEX() i ka waiwai i makemake ʻia ma waena o nā lālani a me nā kolamu. A hōʻike ʻo MATCH() i nā helu o kēia mau waiwai:

=INDEX(kolume kahi i hoʻihoʻi ʻia ai ka waiwai, MATCH(waiwai e huli ai, kolamu e huli ai, 0))

Ke paʻakikī nei ka hoʻomaopopo ʻana i ka hana? Malia paha e wehewehe maikaʻi kekahi laʻana. Inā he papa inoa kāu o nā poʻokela honua a me ko lākou heluna kanaka:

I mea e ʻike ai i ka nui o ka heluna kanaka o kekahi kapikala, no ka laʻana, ke kapikala o Iapana, hoʻohana mākou i kēia ʻano:

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

Hōʻike:

• Ke nānā nei ka hana MATCH() i ka waiwai - "Iapana" i ka array A2:A10 a hoʻihoʻi i ka helu 3, no ka mea ʻo Iapana ke kolu o ka waiwai ma ka papa inoa. 
• Hele kēia helu ihelu laina” ma ka INDEX() formula a haʻi i ka hana e paʻi i kahi waiwai mai kēia lālani.

No laila, lilo ka ʻōlelo ma luna i ke ʻano maʻamau NUI (C2:C10,3). Huli ka formula mai nā pūnaewele C2 a i C10 a hoʻihoʻi i ka ʻikepili mai ke kolu o ke kelepona ma kēia pae, ʻo ia hoʻi, C4, no ka mea, hoʻomaka ka helu ʻana mai ka lālani lua.

ʻAʻole makemake e kuhikuhi i ka inoa o ke kūlanakauhale ma ke ʻano? A laila e kākau iā ia i loko o kekahi keena, e ʻōlelo F1, a hoʻohana iā ia ma ke ʻano he kuhikuhi ma ka MATCH() formula. A hoʻopau ʻoe me kahi ʻano hulina ikaika:

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

Mea nui! Ka helu o nā laina i loko e kū'ē i INDEX() pono e like me ka helu o nā lālani i loko i manaʻo ʻia ʻo array i MATCH (), i ʻole e loaʻa iā ʻoe ka hopena hewa.

E kali i kekahi minuke, no ke aha ʻaʻole e hoʻohana wale i ka palapala VLOOKUP()?

=VLOOKUP(F1, A2:C10, 3, Hoʻopunipuni)

 He aha ke kumu o ka hoʻopau manawa i ka hoʻāʻo ʻana e ʻike i kēia mau paʻakikī āpau o INDEX MATCH?

I kēia hihia, ʻaʻole pili ka hana e hoʻohana ai. He laʻana wale kēia no ka hoʻomaopopo ʻana i ka hana like ʻana o ka INDEX() a me MATCH(). E hōʻike ana nā hiʻohiʻona ʻē aʻe i ka mea hiki i kēia mau hana i nā kūlana i mana ʻole ʻia ka VLOOKUP. 

INDEX MATCH a i ʻole VLOOKUP

I ka hoʻoholo ʻana i ke ʻano hulina e hoʻohana ai, ua ʻae ka poʻe he nui loa ka INDEX() a me MATCH() ma mua o VLOOKUP. Eia nō naʻe, hoʻohana ka poʻe he nui i ka VLOOKUP (). ʻO ka mea mua, ʻoi aku ka maʻalahi o VLOOKUP(), a ʻo ka lua, ʻaʻole maopopo nā mea hoʻohana i nā pono āpau o ka hana ʻana me INDEX() a me MATCH(). Me ka ʻole o kēia ʻike, ʻaʻohe mea e ʻae e hoʻolilo i ko lākou manawa e aʻo ai i kahi ʻōnaehana paʻakikī.

Eia nā pono nui o INDEX() a me MATCH() ma luna o VLOOKUP():

• Huli mai ka ʻākau a i ka hema. ʻAʻole hiki iā VLOOKUP() ke ʻimi mai ka ʻākau a i ka hema, no laila pono mau nā waiwai āu e ʻimi nei ma nā kolamu hema loa o ka papaʻaina. Akā ʻo INDEX () a me MATCH () hiki ke mālama i kēia me ka pilikia ʻole. E haʻi aku kēia ʻatikala iā ʻoe i ke ʻano o ka hana: pehea e ʻike ai i ka waiwai i makemake ʻia ma ka ʻaoʻao hema.

1. Hoʻohui palekana a wehe paha i nā kolamu. Hōʻike ka formula VLOOKUP() i nā hualoaʻa hewa ke wehe a hoʻohui i nā kolamu no ka mea pono ʻo VLOOKUP() i ka helu kolamu pololei e holomua. ʻO ka mea maʻamau, ke hoʻohui a wehe ʻia nā kolamu, hoʻololi pū ko lākou helu. 

A i loko o ka INDEX() a me MATCH(), ua kuhikuhi ʻia ke ʻano o nā kolamu, ʻaʻole nā ​​kolamu pākahi. ʻO ka hopena, hiki iā ʻoe ke hoʻohui a wehe i nā kolamu me ka ʻole e hoʻohou i ke ʻano i kēlā me kēia manawa.

1. ʻAʻohe palena o ka nui hulina. I ka hoʻohana ʻana iā VLOOKUP(), ʻaʻole pono e ʻoi aku ka nui o nā koina hulina ma mua o 255 mau huaʻōlelo a i ʻole e loaʻa iā ʻoe kahi #VALUE! No laila, inā loaʻa i kāu ʻikepili ka nui o nā huaʻōlelo, ʻo INDEX () a me MATCH () ke koho maikaʻi loa.
2. ʻO ka wikiwiki hana kiʻekiʻe. Inā liʻiliʻi kāu mau papaʻaina, a laila ʻaʻole paha ʻoe e ʻike i kekahi ʻokoʻa. Akā, inā loaʻa i ka papaʻaina he mau haneli a mau kaukani lālani, a no laila, aia nā haneli a me nā kaukani o nā kumu hoʻohālikelike, ʻoi aku ka wikiwiki o ka INDEX () a me MATCH () ma mua o VLOOKUP (). ʻO ka mea ʻoiaʻiʻo, e hana wale ʻo Excel i nā kolamu i kuhikuhi ʻia i loko o ke ʻano, ma kahi o ka hana ʻana i ka papaʻaina holoʻokoʻa. 

E ʻike nui ʻia ka hopena o ka hana o VLOOKUP() inā loaʻa i kāu pepa haʻawina he helu nui o nā formula e like me VLOOKUP() a me SUM(). Pono nā mākaʻikaʻi kaʻawale o nā hana VLOOKUP() e hoʻokaʻawale i kēlā me kēia waiwai i kahi ʻano. No laila pono ʻo Excel e hoʻoponopono i ka nui o ka ʻike, a hoʻolohi nui kēia i ka hana.

Nā laʻana hoʻohālike 

Ua ʻike mua mākou i ka pono o kēia mau hana, no laila hiki iā mākou ke neʻe i ka ʻāpana hoihoi loa: ka hoʻohana ʻana i ka ʻike ma ka hana.

ʻO ke kumu e huli ai mai ka ʻākau a i ka hema

E like me ka mea i ʻōlelo mua ʻia, ʻaʻole hiki iā VLOOKUP ke hana i kēia ʻano ʻimi. No laila, inā ʻaʻole i loko o ka kolamu hema nā waiwai i makemake ʻia, ʻaʻole e hoʻopuka ʻo VLOOKUP() i kahi hopena. ʻOi aku ka maʻalahi o nā hana INDEX () a me MATCH (), a ʻaʻole nui ka wahi o nā waiwai no lākou e hana.

No ka laʻana, e hoʻohui mākou i kahi kolamu kūlana ma ka ʻaoʻao hema o kā mākou papaʻaina a e hoʻāʻo e noʻonoʻo i ke kūlana e pili ana i ka heluna kanaka e noho ai ke kapikala o ko mākou ʻāina.

I loko o ke kelepona G1, kākau mākou i ka waiwai e loaʻa ai, a laila hoʻohana i kēia ʻano kumu e huli ai ma ka laulā C1:C10 a hoʻihoʻi i ka waiwai kūpono mai A2:A10:

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

Hoʻomaka. Inā hoʻolālā ʻoe e hoʻohana i kēia ʻano kumu no nā cell he nui, e hōʻoia ʻoe e hoʻoponopono i nā pae me ka hoʻohana ʻana i ka ʻōlelo kikoʻī (no ka laʻana, $A$2: $A$10 a me $C$2: 4C$10).

INDEX MORE EXPOSED MORE EXPOSED  e huli ma na kolamu a me na lalani

Ma nā hiʻohiʻona i luna, ua hoʻohana mākou i kēia mau hana ma ke ʻano he pani no VLOOKUP() e hoʻihoʻi i nā waiwai mai kahi ʻano o nā lālani i koho mua ʻia. Akā, pehea inā pono ʻoe e hana i kahi matrix a i ʻole ʻaoʻao ʻaoʻao ʻelua?

He paʻakikī ke kani ʻana, akā ua like ke ʻano no ia mau helu ʻana me ke ʻano maʻamau INDEX() MATCH(), me hoʻokahi ʻokoʻa: pono e hoʻohana ʻelua i ka formula MATCH(). ʻO ka manawa mua e loaʻa ai ka helu lālani, a i ka lua o ka manawa e loaʻa ai ka helu kolamu:

=INDEX(waiwai, MATCH(waiwai hulina pololei, kolamu huli, 0), MATCH(waiwai huli hulina, lalani huli, 0))

E nānā kākou i ka pākaukau ma lalo nei a e hoʻāʻo e hana i ke ʻano INDEX() EXPRESS() EXPRESS() i mea e hōʻike ai i ka heluna kanaka ma kekahi ʻāina kikoʻī no ka makahiki i koho ʻia.

Aia ka ʻāina i hoʻopaʻa ʻia i loko o ka cell G1 (lookup vertical) a ʻo ka makahiki i manaʻo ʻia aia ma ka cell G2 (lookup horizontal). Penei ke ano o ka formula:

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

Pehea e hana ai kēia formula

E like me nā hoʻohālikelike paʻakikī ʻē aʻe, ʻoi aku ka maʻalahi o ka hoʻomaopopo ʻana ma ka hoʻokaʻawale ʻana iā lākou i nā hoʻohālikelike pākahi. A laila hiki iā ʻoe ke hoʻomaopopo i ka hana a kēlā me kēia kanaka:

• MAKEMAKE (G1,A2:A11,0) – nānā i kahi waiwai (G1) ma ka laulā A2:A11 a hōʻike i ka helu o kēia waiwai, i kā mākou hihia he 2;
• HULI(G2,B1:D1,0) – ʻimi i kahi waiwai (G2) ma ka laulā B1:D1. I kēia hihia, he 3 ka hopena.

Hoʻouna ʻia nā helu lālani a me nā kolamu i loaʻa i ka waiwai pili i ka INDEX() formula:

=INDEX(B2:D11,2,3)

ʻO ka hopena, loaʻa iā mākou kahi waiwai i loko o ke kelepona ma ke kikowaena o 2 lālani a me 3 kolamu ma ka laulā B2:D11. A hōʻike ke kumu i ka waiwai i makemake ʻia, aia ma ke kelepona D3.

Huli ma nā kūlana he nui me INDEX a me MATCH

Inā ua heluhelu ʻoe i kā mākou alakaʻi iā VLOOKUP(), ua hoʻāʻo paha ʻoe i nā ʻano ʻimi hulina he nui. Akā hoʻokahi palena koʻikoʻi o kēia ʻano hulina - ka pono e hoʻohui i kahi kolamu kōkua.

Akā ʻo ka nūhou maikaʻi kēlā Me INDEX() a me MATCH() hiki iā ʻoe ke ʻimi i nā kūlana he nui me ka ʻole e hoʻoponopono a hoʻololi paha i kāu pepa hana.

Eia ke ʻano hulina kūlana lehulehu no INDEX() MATCH():

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

ʻO ka memo: pono e hoʻohana pū ʻia kēia ʻano kumu me ka pōkole kīpē CTRL+SHIFT+ENTER.

Inā pono ʻoe e ʻimi i ka waiwai āu e ʻimi nei ma muli o 2 mau kūlana: mea kūʻai mai и Kahuliʻi.

Pono kēia i kēia ʻano hana:

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

I loko o kēia kaila, C2:C10 ka laulā kahi e huli ai, F1 - keia kulana, A2:A10 — ʻo ia ka laulā e hoʻohālikelike ai i ke kūlana, F2 - kūlana 2, V2:V10 - ka laulā no ka hoʻohālikelike ʻana i ke kūlana 2.

Mai poina e kaomi i ka hui ʻana ma ka hopena o ka hana me ke ʻano CTRL+SHIFT+ENTER - E pani koke ʻo Excel i ke ʻano me nā braces curly, e like me ka mea i hōʻike ʻia ma ka laʻana:

Inā ʻaʻole ʻoe makemake e hoʻohana i kahi papa kuhikuhi no kāu hana, a laila e hoʻohui i kahi INDEX () i ke ʻano a kaomi iā ENTER, e like ia me ka laʻana:

Pehea e hana ai kēia mau formula

Hoʻohana like kēia ʻano hana me ke ʻano maʻamau INDEX() MATCH(). No ka ʻimi ʻana i nā kūlana he nui, hana wale ʻoe i nā kūlana False a ʻOiaʻiʻo e hōʻike ana i nā kūlana pono a me ka hewa. A laila pili kēia mau kūlana i nā mea pili like o ka array. Hoʻololi ka formula i nā hoʻopaʻapaʻa False a ʻOiaʻiʻo i ka 0 a me 1, kēlā me kēia, a hoʻopuka i kahi ʻano kahi i loaʻa ai ka 1 i nā waiwai like i loaʻa i ke kaula. E ʻike ʻo MATCH() i ka waiwai mua e pili ana i ka 1 a hāʻawi iā ia i ke kumu INDEX(). A ʻo ia hoʻi, e hoʻihoʻi i ka waiwai i makemake ʻia ma ka laina i kuhikuhi ʻia mai ke kolamu i makemake ʻia.

ʻO ke kumu me ka ʻole o ka laha e pili ana i ka hiki o INDEX() ke lawelawe iā lākou iho. ʻO ka lua INDEX() i loko o ke kumu hoʻohālikelike e pili ana i ka falsy (0), no laila e hāʻawi i ka hui holoʻokoʻa me kēlā mau waiwai i ka formula MATCH(). 

He wehewehe lōʻihi kēia o ke kumu o kēia ʻano. No ka ʻike hou aku e heluhelu i ka ʻatikala "INDEX MATCH me nā kūlana he nui".

AVERAGE, MAX a me MIN ma INDEX a me MATCH

Loaʻa iā Excel kāna mau hana kūikawā no ka loaʻa ʻana o nā awelika, nā kiʻekiʻe, a me nā mea liʻiliʻi. Akā, pehea inā makemake ʻoe e kiʻi i ka ʻikepili mai ke kelepona pili i kēlā mau waiwai? Ma keia hihia Pono e hoʻohana pū ʻia ka AVERAGE, MAX a me MIN me INDEX a me MATCH.

INDEX MATCH a me MAX

No ka huli ʻana i ka waiwai nui ma ke kolamu D a hōʻike iā ia ma ke kolamu C, e hoʻohana i ke ʻano: 

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

INDEX MATCH a me MIN

No ka huli ʻana i ka waiwai liʻiliʻi loa ma ke kolamu D a hōʻike iā ia ma ke kolamu C, e hoʻohana i kēia ʻano kumu:

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

NUI INDEX a me SERPENT

No ka huli ʻana i ka waiwai awelika ma ke kolamu D a hōʻike i kēia waiwai ma C:

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

Ma muli o ka kākau ʻana o kāu ʻikepili, ʻo ke kolu o ka hoʻopaʻapaʻa i MATCH() ʻo 1, 0, a i ʻole -1:

• inā e hoʻokaʻawale ʻia nā kolamu ma ka ʻaoʻao piʻi, e hoʻonoho i ka 1 (a laila e helu ke kumu i ka waiwai nui, ʻoi aku ka liʻiliʻi a i ʻole like me ka waiwai awelika);
• inā e iho ana ka ʻano, a laila -1 (e hoʻopuka ke kumu i ka waiwai liʻiliʻi i ʻoi aku ka nui a i ʻole like me ka awelika);
• inā loaʻa i ka pūʻulu huli kahi waiwai i like me ka awelika, a laila hoʻonoho iā 0. 

 I kā mākou laʻana, ua hoʻokaʻawale ʻia ka heluna kanaka ma ke ʻano iho, no laila ua kau mākou -1. A ʻo Tokyo ka hopena, ʻoiai ʻo ka heluna kanaka (13,189) ka mea kokoke loa i ka waiwai awelika (000).

Hiki iā VLOOKUP() ke hana i kēlā mau helu ʻana, akā ma ke ʻano he ʻano hoʻonohonoho: VLOOKUP me AVERAGE, MIN a me MAX.

INDEX MATCH a me ESND/IFERROR

Ua ʻike paha ʻoe inā ʻaʻole hiki ke loaʻa i ke kumulāʻau ka waiwai i makemake ʻia, hoʻolei ia i kahi hewa # N / A. Hiki iā ʻoe ke pani i ka memo hewa maʻamau me kahi mea ʻike hou aʻe. No ka laʻana, e hoʻonoho i ka hoʻopaʻapaʻa ma ke ʻano I ka la XNUMX:

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

Me kēia ʻano, inā ʻoe e hoʻokomo i ka ʻikepili ʻaʻole i ka papaʻaina, e hāʻawi ka palapala iā ʻoe i ka memo i kuhikuhi ʻia.

Inā makemake ʻoe e hopu i nā hewa a pau, koe wale nō I ka la XNUMX hiki ke hoʻohana IFERROR:

=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), “Ua hewa kekahi mea!”)

Akā e hoʻomanaʻo ʻaʻole he manaʻo maikaʻi ka masking hewa ma kēia ʻano, no ka mea, hōʻike nā hewa maʻamau i nā hewa i ke ʻano.

Manaʻo mākou ua ʻike ʻoe i kā mākou alakaʻi i ka hoʻohana ʻana i ka hana INDEX MATCH() kōkua.