Ngano nga ang INDEX ug MATCH mas maayo kaysa VLOOKUP sa Excel

Gipasabot na namo kaniadto sa mga nagsugod kung unsaon paggamit ang mga batakang gimbuhaton sa VLOOKUP (Iningles nga VLOOKUP, ang abbreviation nagpasabut sa "vertical lookup function"). Ug ang mga eksperyensiyadong tiggamit gipakita sa daghang mas komplikado nga mga pormula.

Ug sa niini nga artikulo kita mosulay sa paghatag og impormasyon mahitungod sa laing paagi sa pagtrabaho uban sa bertikal search.

Tingali nahibulong ka: "Nganong gikinahanglan kini?". Ug kini gikinahanglan aron mapakita ang tanan nga posible nga mga pamaagi sa pagpangita. Dugang pa, daghang mga pagdili sa VLOOKUP kanunay nga nagpugong sa pagkuha sa gitinguha nga resulta. Niining bahina, ang INDEX() MATCH() mas magamit ug lainlain, ug sila usab adunay gamay nga mga pagdili.

Mga sukaranan nga INDEX MATCH

Tungod kay ang katuyoan niini nga giya mao ang pagpakita kung unsa ka maayo kini nga bahin, kami Atong tan-awon ang sukaranan nga kasayuran bahin sa mga prinsipyo sa operasyon niini. Ug magpakita kami mga pananglitan, ug hunahunaa usab kung ngano, mas maayo kini kaysa VLOOKUP ().

INDEX Function Syntax ug Paggamit

Kini nga function makatabang sa pagpangita sa gitinguha nga kantidad taliwala sa mga piho nga lugar sa pagpangita base sa numero sa kolum o linya. Sintaks:

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

• array – ang dapit diin ang pagpangita mahitabo;
• numero sa linya – ang numero sa linya nga pangitaon sa gipiho nga laray. Kung ang numero sa laray wala mahibal-an, ang numero sa kolum kinahanglan itakda;
• numero sa kolum – ang numero sa kolum nga makit-an sa gipiho nga laray. Kung wala mahibal-an ang kantidad, gikinahanglan ang numero sa linya.

Usa ka pananglitan sa usa ka yano nga pormula:

=INDEX(A1:S10,2,3)

Ang function mangita sa range gikan sa A1 hangtod C10. Gipakita sa mga numero kung asa nga laray (2) ug kolum (3) aron ipakita ang gusto nga kantidad. Ang resulta mao ang cell C2.

Medyo simple, di ba? Apan kung nagtrabaho ka gamit ang tinuod nga mga dokumento, dili ka tingali adunay kasayuran bahin sa mga numero sa kolum o mga selyula. Mao kana ang gamit sa MATCH() function.

MATCH Function Syntax ug Paggamit

Ang MATCH() function nangita sa gusto nga bili ug nagpakita sa iyang gibanabana nga gidaghanon sa gipiho nga search area.

Ang searchpos() syntax ingon niini:

=MATCH(bili sa pagpangita, han-ay sa pagpangita, tipo sa pagpares)

• bili sa pagpangita – ang numero o teksto nga makit-an;
• searched array – ang dapit diin ang pagpangita mahitabo;
• tipo sa pagpares – nagtino kung pangitaon ba ang eksaktong kantidad o ang mga kantidad nga labing duol niini:
  • 1 (o walay bili nga espesipiko) – ibalik ang kinadak-ang bili nga katumbas o ubos pa sa bili nga gipiho;
  • 0 – nagpakita ug eksaktong tugma sa gipangita nga bili. Sa kombinasyon nga INDEX() MATCH() magkinahanglan ka ug eksaktong tugma, mao nga nagsulat mi og 0;
  • -1 – Nagpakita sa pinakagamay nga bili nga mas dako o katumbas sa bili nga gipiho sa pormula. Ang paghan-ay gihimo sa paubos nga han-ay.

Pananglitan, sa range B1:B3 New York, Paris, London ang narehistro. Ang pormula sa ubos magpakita sa numero 3 tungod kay ang London ikatulo sa listahan:

=EXPOSE(London,B1:B3,0)

Giunsa pagtrabaho ang function sa INDEX MATCH 

Tingali nagsugod ka na sa pagsabut sa prinsipyo diin ang hiniusang buhat niini nga mga gimbuhaton gitukod. Sa laktod, unya Ang INDEX() nangita sa gitinguha nga bili taliwala sa gipiho nga mga laray ug mga kolum. Ug ang MATCH() nagpakita sa mga numero niini nga mga bili:

=INDEX(kolum diin ang bili gibalik, MATCH(bili sa pagpangita, kolum sa pagpangita, 0))

Naglisud pa ba sa pagsabut kung giunsa kini molihok? Tingali ang usa ka pananglitan mas makapatin-aw. Ibutang ta nga ikaw adunay usa ka lista sa mga kapital sa kalibutan ug ang ilang populasyon:

Aron mahibal-an ang gidak-on sa populasyon sa usa ka kapital, pananglitan, ang kapital sa Japan, among gigamit ang mosunod nga pormula:

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

Pagpatin-aw:

• Ang MATCH() function mangita alang sa bili - "Japan" sa array A2:A10 ug ibalik ang numero 3, tungod kay ang Japan mao ang ikatulo nga bili sa listahan. 
• Kini nga numero moadto sanumero sa linya” sa INDEX() nga pormula ug nagsulti sa function sa pag-imprinta sa usa ka bili gikan niini nga laray.

Busa ang pormula sa ibabaw nahimong sumbanan nga pormula INDEKS(C2:C10,3). Ang pormula nangita gikan sa mga cell C2 hangtod sa C10 ug nagbalik sa datos gikan sa ikatulo nga cell sa kini nga range, kana mao, C4, tungod kay ang pag-ihap magsugod gikan sa ikaduhang laray.

Dili gusto nga magreseta sa ngalan sa lungsod sa pormula? Dayon isulat kini sa bisan unsang cell, isulti ang F1, ug gamita kini isip reference sa MATCH() formula. Ug matapos nimo ang usa ka dinamikong pormula sa pagpangita:

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

Importante! Gidaghanon sa mga linya sa pagsugyot INDEX() kinahanglan nga pareho sa gidaghanon sa mga laray sa sulod giisip nga array sa MATCH(), kung dili makuha nimo ang sayup nga resulta.

Paghulat usa ka minuto, nganong dili na lang gamiton ang VLOOKUP() nga pormula?

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

 Unsa man ang punto sa pag-usik sa oras sa pagsulay nga mahibal-an ang tanan nga mga pagkakomplikado sa INDEX MATCH?

Sa kini nga kaso, dili igsapayan kung unsang function ang gamiton. Kini usa lamang ka pananglitan aron masabtan kung giunsa ang INDEX () ug MATCH () nga mga function nagtinabangay. Ang ubang mga pananglitan magpakita kung unsa ang mahimo niini nga mga gimbuhaton sa mga sitwasyon diin ang VLOOKUP walay gahum. 

INDEX MATCH o VLOOKUP

Kung magdesisyon kung unsang pormula sa pagpangita ang gamiton, daghan ang miuyon nga ang INDEX () ug MATCH () labi ka labaw sa VLOOKUP. Bisan pa, daghang mga tawo ang naggamit gihapon sa VLOOKUP (). Una, ang VLOOKUP() mas simple, ug ikaduha, ang mga tiggamit dili hingpit nga makasabut sa tanang mga bentaha sa pagtrabaho uban sa INDEX() ug MATCH(). Kung wala kini nga kahibalo, walay usa nga mouyon sa paggugol sa ilang oras sa pagtuon sa usa ka komplikado nga sistema.

Ania ang mahinungdanong mga bentaha sa INDEX() ug MATCH() kay sa VLOOKUP():

• Pangitaa gikan sa tuo hangtod sa wala. Ang VLOOKUP() dili makapangita gikan sa tuo ngadto sa wala, mao nga ang mga kantidad nga imong gipangita kinahanglang anaa kanunay sa pinakawala nga mga kolum sa lamesa. Apan ang INDEX() ug MATCH() makahimo niini nga walay problema. Kini nga artikulo magsulti kanimo kung unsa ang hitsura niini sa praktis: kung giunsa pagpangita ang gitinguha nga kantidad sa wala nga bahin.

1. Luwas nga pagdugang o pagtangtang sa mga kolum. Ang VLOOKUP() nga pormula nagpakita ug sayop nga mga resulta sa pagtangtang o pagdugang sa mga kolum tungod kay ang VLOOKUP() nagkinahanglan sa eksaktong numero sa kolum aron magmalampuson. Natural, kung ang mga kolum idugang o kuhaon, ang ilang mga numero usab mausab. 

Ug sa INDEX() ug MATCH() nga mga pormula, usa ka han-ay sa mga kolum ang gipiho, dili indibidwal nga mga kolum. Ingon usa ka sangputanan, luwas ka nga makadugang ug makatangtang sa mga kolum nga dili kinahanglan nga i-update ang pormula matag higayon.

1. Walay mga limitasyon sa gidaghanon sa pagpangita. Kung gigamit ang VLOOKUP(), ang kinatibuk-ang ihap sa mga pamatasan sa pagpangita kinahanglan dili molapas sa 255 nga mga karakter o makakuha ka usa ka #VALUE! Mao nga kung ang imong data adunay daghang mga karakter, INDEX () ug MATCH () ang labing kaayo nga kapilian.
2. Taas nga katulin sa pagproseso. Kung ang imong mga lamesa gamay ra, nan dili nimo mamatikdan ang bisan unsang kalainan. Apan, kung ang lamesa adunay gatusan o liboan nga mga laray, ug, sumala niana, adunay gatusan ug liboan nga mga pormula, ang INDEX () ug MATCH () mas paspas nga makasagubang kaysa VLOOKUP (). Ang tinuod mao nga ang Excel magproseso lamang sa mga kolum nga gitakda sa pormula, imbes nga pagproseso sa tibuok lamesa. 

Ang epekto sa pasundayag sa VLOOKUP() labi nga mamatikdan kung ang imong worksheet adunay daghang mga pormula sama sa VLOOKUP() ug SUM(). Ang bulag nga mga pagsusi sa VLOOKUP() nga mga gimbuhaton gikinahanglan aron ma-parse ang matag kantidad sa usa ka array. Mao nga kinahanglan nga iproseso sa Excel ang daghang kasayuran, ug kini makapahinay sa trabaho.

Mga Ehemplo sa Pormula 

Nahibal-an na namon ang kapuslanan niini nga mga gimbuhaton, aron makapadayon kami sa labing makapaikag nga bahin: ang paggamit sa kahibalo sa praktis.

Pormula sa pagpangita gikan sa tuo ngadto sa wala

Sama sa nahisgotan na, ang VLOOKUP dili makahimo niini nga matang sa pagpangita. Mao nga, kung ang gusto nga mga kantidad wala sa pinakawala nga kolum, ang VLOOKUP() dili makahatag usa ka sangputanan. Ang INDEX() ug MATCH() nga mga function kay mas versatile, ug ang lokasyon sa mga values ​​walay dako nga papel para sa ilang pagtrabaho.

Pananglitan, magdugang kami og kolum sa ranggo sa wala nga bahin sa among lamesa ug sulayan nga mahibal-an kung unsa nga ranggo sa mga termino sa populasyon ang giokupar sa kaulohan sa Atong Nasud.

Sa cell G1, atong isulat ang bili nga makit-an, ug dayon gamiton ang mosunod nga pormula sa pagpangita sa han-ay C1:C10 ug ibalik ang katugbang nga bili gikan sa A2:A10:

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

Giaghat. Kung nagplano ka nga gamiton kini nga pormula alang sa daghang mga cell, siguruha nga ayohon nimo ang mga han-ay gamit ang hingpit nga pagtubag (pananglitan, $A$2: $A$10 ug $C$2: 4C$10).

INDEX MORE EXPOSED MORE EXPOSED  sa pagpangita sa mga kolum ug mga laray

Sa mga pananglitan sa ibabaw, gigamit namon kini nga mga gimbuhaton ingon usa ka kapuli sa VLOOKUP () aron ibalik ang mga kantidad gikan sa usa ka gitakda nang daan nga mga linya. Apan komosta kung kinahanglan nimo nga buhaton ang usa ka matrix o duha ka kilid nga pagpangita?

Kini daw komplikado, apan ang pormula alang sa maong mga kalkulasyon susama sa standard nga INDEX() MATCH() nga pormula, nga adunay usa lamang ka kalainan: ang MATCH() nga pormula kinahanglang gamiton sa makaduha. Ang unang higayon aron makuha ang numero sa laray, ug ang ikaduhang higayon aron makuha ang numero sa kolum:

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

Atong tan-awon ang lamesa sa ubos ug sulayan paghimo og pormula INDEX() EXPRESS() EXPRESS() aron ipakita ang mga demograpiko sa usa ka piho nga nasud alang sa usa ka pinili nga tuig.

Ang target nga nasud naa sa cell G1 (vertical lookup) ug ang target nga tuig naa sa cell G2 (horizontal lookup). Ang pormula tan-awon sama niini:

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

Giunsa kini nga pormula molihok

Sama sa uban pang komplikado nga mga pormula, mas dali silang masabtan pinaagi sa pagbungkag niini sa mga indibidwal nga equation. Ug dayon masabtan nimo kung unsa ang gibuhat sa matag indibidwal nga function:

• MATCH(G1,A2:A11,0) – mangita sa usa ka bili (G1) sa han-ay A2:A11 ug nagpakita sa gidaghanon niini nga bili, sa atong kaso kini mao ang 2;
• PAGPANGITA(G2,B1:D1,0) – mangita og value (G2) sa range B1:D1. Sa kini nga kaso, ang resulta mao ang 3.

Ang nakit-an nga mga numero sa linya ug kolum gipadala sa katumbas nga kantidad sa INDEX() nga pormula:

=INDEX(B2:D11,2,3)

Ingon nga resulta, kita adunay usa ka bili nga anaa sa usa ka cell sa intersection sa 2 row ug 3 column sa range B2:D11. Ug ang pormula nagpakita sa gitinguha nga bili, nga anaa sa cell D3.

Pagpangita pinaagi sa daghang mga kondisyon nga adunay INDEX ug MATCH

Kung nabasa nimo ang among giya sa VLOOKUP(), lagmit nakasulay ka na sa daghang mga pormula sa pagpangita. Apan kini nga pamaagi sa pagpangita adunay usa ka hinungdanon nga limitasyon - ang panginahanglan sa pagdugang usa ka kolum sa auxiliary.

Apan ang maayong balita mao kana Uban sa INDEX() ug MATCH() makapangita ka ug daghang mga kondisyon nga dili na kinahanglang usbon o usbon ang imong worksheet.

Ania ang kinatibuk-ang multi-kondisyon nga pormula sa pagpangita alang sa INDEX() MATCH():

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

Ang nota: kini nga pormula kinahanglang gamiton uban sa keyboard shortcut CTRL+SHIFT+ENTER.

Ibutang ta nga kinahanglan nimo pangitaon ang kantidad nga imong gipangita base sa 2 nga mga kondisyon: buyer и Produkto.

Kini nagkinahanglan sa mosunod nga pormula:

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

Niini nga pormula, ang C2:C10 mao ang gilay-on diin ang pagpangita mahitabo, F1 - kini nga kahimtang, A2:A10 — mao ang range aron itandi ang kahimtang, F2 - kondisyon 2, V2:V10 – range para sa pagtandi sa kondisyon 2.

Ayaw kalimti nga ipadayon ang kombinasyon sa katapusan sa trabaho nga adunay pormula CTRL+SHIFT+ENTER - Awtomatikong isira sa Excel ang pormula gamit ang mga kulot nga braces, sama sa gipakita sa pananglitan:

Kung dili nimo gusto nga mogamit usa ka pormula sa array para sa imong trabaho, unya pagdugang usa ka INDEX () sa pormula ug pindota ang ENTER, kini tan-awon sama sa pananglitan:

Giunsa kini nga mga pormula molihok

Kini nga pormula nagtrabaho sa parehas nga paagi sama sa sumbanan nga INDEX() MATCH() nga pormula. Aron makapangita og daghang kondisyon, maghimo ka lang og daghang False ug True nga kondisyon nga nagrepresentar sa husto ug sayop nga indibidwal nga kondisyon. Ug unya kini nga mga kondisyon magamit sa tanan nga katugbang nga mga elemento sa array. Ang pormula nag-convert sa False ug True nga mga argumento ngadto sa 0 ug 1, matag usa, ug nagpagawas sa usa ka laray diin ang 1 mao ang katugbang nga mga bili nga nakit-an sa string. Ang MATCH() mangita sa unang value nga motakdo sa 1 ug ipasa kini sa INDEX() formula. Ug kini, sa baylo, ibalik ang gusto na nga kantidad sa piho nga linya gikan sa gusto nga kolum.

Ang usa ka pormula nga walay laray nagdepende sa abilidad sa INDEX() sa pagdumala niini sa iyang kaugalingon. Ang ikaduha nga INDEX() sa pormula motakdo sa bakak (0), mao nga gipasa niini ang tibuok han-ay uban sa maong mga bili ngadto sa MATCH() nga pormula. 

Kini usa ka taas nga pagpatin-aw sa lohika sa luyo niini nga pormula. Alang sa dugang nga kasayuran basaha ang artikulo nga "INDEX MATCH nga adunay daghang mga kondisyon".

AVERAGE, MAX ug MIN sa INDEX ug MATCH

Ang Excel adunay kaugalingon nga espesyal nga mga gimbuhaton alang sa pagpangita sa mga average, maximum, ug minimum. Apan unsa man kung gusto nimo makakuha mga datos gikan sa cell nga may kalabotan sa mga kantidad? Niini nga kaso AVERAGE, MAX ug MIN kinahanglan gamiton inubanan sa INDEX ug MATCH.

INDEX MATCH ug MAX

Aron makit-an ang pinakadako nga kantidad sa column D ug ipakita kini sa column C, gamita ang pormula: 

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

INDEX MATCH ug MIN

Aron makit-an ang pinakagamay nga kantidad sa column D ug ipakita kini sa column C, gamita ang mosunod nga pormula:

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

SEARCH INDEX ug SERPENT

Aron makit-an ang kasagaran nga kantidad sa kolum D ug ipakita kini nga kantidad sa C:

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

Depende kung giunsa pagsulat ang imong datos, ang ikatulo nga argumento sa MATCH() kay 1, 0, o -1:

• kung ang mga kolum gihan-ay sa ascending order, itakda ang 1 (dayon ang pormula magkalkula sa labing taas nga kantidad, nga mas ubos o katumbas sa kasagaran nga kantidad);
• kung ang klase nagpaubos, nan -1 (ang pormula magpagawas sa minimum nga kantidad nga mas dako o katumbas sa kasagaran);
• kung ang hanay sa pagpangita adunay usa ka kantidad nga parehas sa kasagaran, unya ibutang kini sa 0. 

 Sa among pananglitan, ang populasyon gihan-ay sa paubos nga pagkasunod-sunod, mao nga among gibutang ang -1. Ug ang resulta mao ang Tokyo, tungod kay ang kantidad sa populasyon (13,189) mao ang labing duol sa kasagaran nga kantidad (000).

Ang VLOOKUP() mahimo usab nga maghimo sa ingon nga mga kalkulasyon, apan ingon usa ka pormula sa array: VLOOKUP nga adunay AVERAGE, MIN ug MAX.

INDEX MATCH ug ESND/IFERROR

Tingali namatikdan na nimo nga kung ang pormula dili makit-an ang gitinguha nga kantidad, kini adunay sayup # N / A. Mahimo nimong pulihan ang sukaranan nga mensahe sa sayup sa usa ka butang nga labi ka kasayuran. Pananglitan, ibutang ang argumento sa pormula Sa ika-XNUMX:

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

Uban niini nga pormula, kung mosulod ka sa datos nga wala sa lamesa, ang porma maghatag kanimo sa piho nga mensahe.

Kung gusto nimo makuha ang tanan nga mga sayup, unya gawas sa Sa ika-XNUMX mahimong magamit IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Adunay problema!”)

Apan hinumdomi nga ang pag-mask sa mga sayup sa niini nga paagi dili maayo nga ideya, tungod kay ang mga standard nga mga sayup nagreport sa mga paglapas sa pormula.

Kami nanghinaut nga imong nakit-an ang among giya sa paggamit sa INDEX MATCH() function nga makatabang.