Vim li cas INDEX thiab MATCH zoo dua VLOOKUP hauv Excel

Peb tau piav qhia yav dhau los rau cov neeg pib tshiab yuav ua li cas siv cov haujlwm tseem ceeb ntawm VLOOKUP (Lus Askiv VLOOKUP, cov ntawv luv luv rau "ntsuas nrhiav haujlwm"). Thiab cov neeg siv kev paub dhau los tau pom ntau cov qauv nyuaj.

Thiab hauv tsab xov xwm no peb yuav sim muab cov ntaub ntawv hais txog lwm txoj hauv kev ua haujlwm nrog kev tshawb nrhiav ntsug.

Tej zaum koj yuav xav tias: "Vim li cas qhov no tsim nyog?". Thiab qhov no yog qhov tsim nyog txhawm rau txhawm rau qhia txhua txoj hauv kev tshawb nrhiav tau. Tsis tas li ntawd, ntau qhov kev txwv VLOOKUP feem ntau tiv thaiv kom tau txais qhov xav tau. Hauv qhov no, INDEX( ) MATCH( ) ua haujlwm ntau dua thiab muaj ntau yam, thiab lawv kuj muaj kev txwv tsawg dua.

Basics INDEX MATCH

Txij li lub hom phiaj ntawm phau ntawv qhia no yog los qhia tias qhov no zoo npaum li cas, peb Cia peb saib cov ntaub ntawv yooj yim hais txog cov hauv paus ntsiab lus ntawm nws txoj haujlwm. Thiab peb yuav ua piv txwv, thiab tseem xav txog vim li cas, nws zoo dua VLOOKUP ().

INDEX Function Syntax thiab Kev Siv

Qhov kev ua haujlwm no pab nrhiav tus nqi uas xav tau ntawm cov chaw tshawb nrhiav raws li kab ntawv lossis kab xov tooj. Syntax:

=INDEX(array, kab zauv, kab zauv):

• array - thaj chaw uas qhov kev tshawb nrhiav yuav tshwm sim;
• kab zauv – tus naj npawb ntawm kab yuav tsum tau tshawb nyob rau hauv lub array teev. Yog tsis paub tus naj npawb kab, kab zauv yuav tsum tau teev tseg;
• kab zauv – tus naj npawb ntawm kem yuav pom nyob rau hauv lub array teev. Yog tias tus nqi tsis paub, yuav tsum muaj tus lej kab.

Ib qho piv txwv ntawm tus qauv yooj yim:

=INDEX(A1:S10,2,3)

Cov haujlwm yuav tshawb hauv thaj tsam ntawm A1 txog C10. Cov lej qhia cov kab (2) thiab kab (3) los qhia qhov xav tau los ntawm. Qhov tshwm sim yuav yog cell C2.

Zoo nkauj yooj yim, txoj cai? Tab sis thaum koj ua haujlwm nrog cov ntaub ntawv tiag tiag, koj yuav tsis zoo li muaj cov ntaub ntawv hais txog kab zauv lossis cov hlwb. Qhov ntawd yog qhov MATCH() muaj nuj nqi rau.

MATCH Function Syntax thiab Kev Siv

MATCH() muaj nuj nqi tshawb nrhiav tus nqi uas xav tau thiab qhia nws tus lej kwv yees hauv cheeb tsam tshawb nrhiav.

Lub searchpos() syntax zoo li no:

=MATCH(tus nqi rau lookup, array rau lookup, match hom)

• nrhiav tus nqi – tus naj npawb lossis cov ntawv yuav pom;
• tshawb nrhiav array - thaj chaw uas qhov kev tshawb nrhiav yuav tshwm sim;
• hom kev sib tw - qhia seb puas yuav nrhiav tus nqi tiag tiag lossis tus nqi ze tshaj rau nws:
  • 1 (lossis tsis muaj nqi teev tseg) - rov qab tus nqi loj tshaj plaws uas sib npaug los yog tsawg dua tus nqi uas tau teev tseg;
  • 0 - qhia qhov tseeb match nrog tus nqi nrhiav. Hauv kev sib xyaw ua ke INDEX() MATCH() koj yuav luag ib txwm xav tau qhov sib tw, yog li peb sau 0;
  • -1 - Qhia tus nqi tsawg tshaj plaws uas ntau dua lossis sib npaug rau tus nqi teev hauv cov qauv. Kev txheeb xyuas yog ua nyob rau hauv qis qis.

Piv txwv li, hauv thaj tsam B1: B3 New York, Paris, London tau sau npe. Cov qauv hauv qab no yuav qhia tus lej 3 vim tias London yog thib peb ntawm cov npe:

=EXPOSE(London,B1:B3,0)

Yuav ua li cas ua haujlwm nrog INDEX MATCH muaj nuj nqi 

Tej zaum koj twb tau pib nkag siab txog lub hauv paus ntsiab lus uas kev sib koom ua haujlwm ntawm cov haujlwm no tau tsim. Hauv luv, ces INDEX() tshawb nrhiav tus nqi uas xav tau ntawm cov kab thiab kab. Thiab MATCH() qhia cov lej ntawm cov nqi no:

=INDEX(kem los ntawm tus nqi xa rov qab, MATCH(tus nqi rau kev tshawb nrhiav, kab mus tshawb hauv, 0))

Tseem muaj lub sijhawm nyuaj nkag siab tias nws ua haujlwm li cas? Tej zaum ib qho piv txwv yuav piav tau zoo dua. Piv txwv tias koj muaj cov npe ntawm lub ntiaj teb cov peev thiab lawv cov pej xeem:

Yuav kom paub qhov loj ntawm cov pej xeem ntawm ib lub peev, piv txwv li, lub peev ntawm Nyiv, peb siv cov qauv hauv qab no:

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

Piav qhia:

• MATCH() muaj nuj nqi saib rau tus nqi - "Nyiv" nyob rau hauv array A2: A10 thiab xa rov qab tus naj npawb 3, vim tias Nyiv yog tus nqi thib peb hauv daim ntawv teev npe. 
• Daim duab no mus raukab xov tooj” nyob rau hauv tus qauv INDEX() thiab qhia cov haujlwm los luam tus nqi ntawm kab no.

Yog li cov qauv saum toj no dhau los ua tus qauv qauv INDEX(C2:C10,3). Cov mis tshawb nrhiav los ntawm cov hlwb C2 mus rau C10 thiab xa rov qab cov ntaub ntawv los ntawm peb lub xov tooj ntawm tes hauv qhov no, uas yog, C4, vim tias kev suav rov qab pib los ntawm kab thib ob.

Tsis xav sau npe lub nroog hauv cov qauv? Tom qab ntawd sau nws rau hauv ib lub xovtooj, hais F1, thiab siv nws raws li kev siv hauv MATCH() qauv. Thiab koj xaus nrog cov qauv tshawb nrhiav dynamic:

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

Tseem ceeb! Tus naj npawb ntawm kab hauv array INDEX() yuav tsum yog tib yam li cov kab hauv suav tias yog array hauv MATCH(), txwv tsis pub koj yuav tau txais cov txiaj ntsig tsis raug.

Tos ib pliag, vim li cas ho tsis siv VLOOKUP() formula?

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

 Dab tsi yog lub ntsiab lus ntawm nkim sij hawm sim los txheeb xyuas tag nrho cov kev nyuaj ntawm INDEX MATCH?

Hauv qhov no, nws tsis muaj teeb meem uas yuav siv tau. Qhov no tsuas yog piv txwv kom nkag siab tias INDEX() thiab MATCH() ua haujlwm ua haujlwm ua ke li cas. Lwm cov piv txwv yuav qhia tias cov haujlwm no muaj peev xwm ua tau li cas hauv cov xwm txheej uas VLOOKUP tsis muaj zog. 

INDEX MATCH los yog VLOOKUP

Thaum txiav txim siab seb qhov kev tshawb fawb twg siv, ntau tus pom zoo tias INDEX() thiab MATCH() yog qhov zoo tshaj rau VLOOKUP. Txawm li cas los xij, ntau tus neeg tseem siv VLOOKUP(). Ua ntej, VLOOKUP() yog qhov yooj yim dua, thiab qhov thib ob, cov neeg siv tsis nkag siab tag nrho cov txiaj ntsig ntawm kev ua haujlwm nrog INDEX() thiab MATCH(). Yog tsis muaj qhov kev paub no, tsis muaj leej twg yuav pom zoo siv lawv lub sijhawm kawm txog kev ua haujlwm nyuaj.

Nov yog qhov txiaj ntsig tseem ceeb ntawm INDEX() thiab MATCH() dhau VLOOKUP():

• Nrhiav ntawm sab xis mus rau sab laug. VLOOKUP() tsis tuaj yeem tshawb nrhiav los ntawm sab xis mus rau sab laug, yog li cov txiaj ntsig koj tab tom nrhiav yuav tsum nyob rau hauv kab laug sab ntawm lub rooj. Tab sis INDEX() thiab MATCH() tuaj yeem daws qhov no yam tsis muaj teeb meem. Kab lus no yuav qhia koj seb nws zoo li cas hauv kev xyaum: yuav ua li cas thiaj nrhiav tau tus nqi xav tau ntawm sab laug.

1. Kev nyab xeeb ntxiv lossis tshem tawm cov kab. Tus qauv VLOOKUP() qhia tau tias tsis raug thaum tshem tawm lossis ntxiv txhua kab vim VLOOKUP() xav tau tus lej kab ke kom ua tiav. Lawm, thaum txhua kab ntxiv lossis tshem tawm, lawv cov lej kuj hloov. 

Thiab hauv INDEX() thiab MATCH() cov qauv, ntau cov kab lus tau teev tseg, tsis yog ib tus kab ke. Yog li ntawd, koj tuaj yeem ruaj ntseg ntxiv thiab tshem tawm txhua kab yam tsis tas yuav hloov kho cov qauv txhua zaus.

1. Tsis muaj kev txwv ntawm kev tshawb nrhiav ntim. Thaum siv VLOOKUP(), tag nrho cov txheej txheem tshawb nrhiav yuav tsum tsis pub tshaj 255 tus cim lossis koj yuav tau txais #VALUE! Yog li yog tias koj cov ntaub ntawv muaj ntau tus cim, INDEX() thiab MATCH() yog qhov kev xaiv zoo tshaj.
2. Kev ua haujlwm siab ceev. Yog tias koj cov ntxhuav me me, ces koj yuav tsis pom qhov txawv. Tab sis, yog tias lub rooj muaj ntau pua lossis ntau txhiab kab, thiab, raws li, muaj ntau pua thiab txhiab tus qauv, INDEX () thiab MATCH () yuav tiv tau sai dua VLOOKUP (). Qhov tseeb yog tias Excel yuav ua haujlwm tsuas yog cov kab ntawv teev tseg hauv cov qauv, tsis yog ua rau tag nrho cov lus. 

Qhov kev ua tau zoo ntawm VLOOKUP() yuav pom tshwj xeeb yog tias koj daim ntawv ua haujlwm muaj ntau tus qauv xws li VLOOKUP() thiab SUM(). Sib cais cov tshev ntawm VLOOKUP() cov haujlwm yuav tsum tau txheeb xyuas txhua tus nqi hauv ib qho array. Yog li Excel yuav tsum tau ua cov ntaub ntawv ntau heev, thiab qhov no ua haujlwm qeeb qeeb.

Cov qauv piv txwv 

Peb twb xam pom qhov muaj txiaj ntsig ntawm cov haujlwm no, yog li peb tuaj yeem txav mus rau qhov nthuav dav tshaj plaws: kev siv kev paub hauv kev xyaum.

Formula nrhiav los ntawm sab xis mus rau sab laug

Raws li twb tau hais lawm, VLOOKUP tsis tuaj yeem ua daim ntawv tshawb fawb no. Yog li, yog tias qhov xav tau qhov tseem ceeb tsis nyob hauv kab laug sab laug, VLOOKUP() yuav tsis tsim qhov tshwm sim. INDEX() thiab MATCH() ua haujlwm muaj ntau yam, thiab qhov chaw ntawm qhov tseem ceeb tsis ua lub luag haujlwm loj rau lawv ua haujlwm.

Piv txwv li, peb yuav ntxiv ib kab ntawv nyob rau sab laug ntawm peb lub rooj thiab sim xyuas seb qhov qeb duas ntawm cov pej xeem lub peev ntawm Peb Lub Tebchaws nyob.

Hauv cell G1, peb sau tus nqi kom pom, thiab tom qab ntawd siv cov qauv hauv qab no los tshawb nrhiav hauv thaj tsam C1: C10 thiab xa rov qab tus nqi sib xws ntawm A2: A10:

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

Ceev faj. Yog tias koj npaj yuav siv cov qauv no rau ntau lub hlwb, xyuas kom koj kho qhov ntau yam siv qhov chaw nyob (piv txwv li, $A $2: $A $10 thiab $C$2: 4C$10).

INDEX EXPOSED ntau tshaj tawm  mus nrhiav hauv kab thiab kab

Hauv cov piv txwv saum toj no, peb tau siv cov haujlwm no los hloov VLOOKUP() kom rov qab tau cov txiaj ntsig los ntawm cov kab uas tau teev ua ntej. Tab sis yuav ua li cas yog tias koj yuav tsum tau ua ib tug matrix los yog ob sab kev tshawb fawb?

Nws suab nyuaj, tab sis cov qauv rau cov kev suav no zoo ib yam li tus qauv INDEX() MATCH() formula, tsuas yog ib qho sib txawv: tus qauv MATCH() yuav tsum tau siv ob zaug. Thawj zaug kom tau tus lej kab, thiab zaum ob kom tau tus lej kab:

=INDEX(array, MATCH(tus nqi nrhiav ntsug, nrhiav kab, 0), MATCH( kab rov tav nrhiav tus nqi, nrhiav kab, 0))

Cia peb saib cov lus hauv qab no thiab sim ua ib qho qauv INDEX() EXPRESS() EXPRESS() txhawm rau ua kom pom cov pej xeem nyob hauv ib lub tebchaws tshwj xeeb rau ib lub xyoo xaiv.

Lub hom phiaj lub teb chaws yog nyob rau hauv cell G1 ( ntsug lookup) thiab lub hom phiaj xyoo yog nyob rau hauv cell G2 (kab rov tav lookup). Cov mis yuav zoo li no:

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

Cov qauv no ua haujlwm li cas

Raws li nrog rau lwm cov qauv, lawv nkag siab yooj yim dua los ntawm kev rhuav tshem lawv mus rau hauv ib qho kev sib npaug. Thiab tom qab ntawd koj tuaj yeem nkag siab tias txhua tus neeg ua haujlwm ua haujlwm li cas:

• MATCH(G1,A2:A11,0) - saib rau tus nqi (G1) nyob rau hauv qhov ntau A2: A11 thiab qhia tus naj npawb ntawm tus nqi no, hauv peb rooj plaub nws yog 2;
• SEARCH(G2,B1:D1,0) - saib rau tus nqi (G2) hauv qhov ntau B1: D1. Hauv qhov no, qhov tshwm sim yog 3.

Cov kab uas pom thiab kab zauv raug xa mus rau tus nqi sib xws hauv INDEX() qauv:

=INDEX(B2:D11,2,3)

Yog li ntawd, peb muaj tus nqi uas yog nyob rau hauv ib lub xovtooj ntawm qhov kev sib tshuam ntawm 2 kab thiab 3 kab hauv qhov ntau B2: D11. Thiab cov qauv qhia txog tus nqi xav tau, uas yog nyob rau hauv cell D3.

Nrhiav los ntawm ntau yam xwm txheej nrog INDEX thiab MATCH

Yog tias koj tau nyeem peb phau ntawv qhia rau VLOOKUP(), tej zaum koj tau sim ntau cov qauv tshawb nrhiav. Tab sis txoj kev tshawb nrhiav no muaj ib qho kev txwv tseem ceeb - qhov yuav tsum tau ntxiv ib kab ntawv pab.

Tab sis txoj xov zoo yog qhov ntawd Nrog INDEX() thiab MATCH() koj tuaj yeem tshawb nrhiav ntau yam yam tsis tas yuav hloov lossis hloov koj daim ntawv ua haujlwm.

Ntawm no yog qhov kev tshawb nrhiav ntau qhov xwm txheej rau INDEX() MATCH():

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

Daim ntawv: cov qauv no yuav tsum tau siv ua ke nrog cov keyboard shortcut CTRL + SHIFT + ENTER.

Xav tias koj yuav tsum nrhiav tus nqi koj tab tom nrhiav raws li 2 yam xwm txheej: buyer и Khoom.

Qhov no yuav tsum muaj cov qauv hauv qab no:

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

Hauv cov qauv no, C2: C10 yog qhov ntau ntawm qhov kev tshawb nrhiav yuav tshwm sim, F1 - qhov xwm txheej no, A2: A10 - yog qhov ntau los sib piv qhov xwm txheej, F2 – condition 2, v2:v 10 - Ntau yam rau kev sib piv ntawm cov xwm txheej 2.

Tsis txhob hnov ​​​​qab nias qhov sib xyaw ua ke thaum kawg ntawm kev ua haujlwm nrog cov qauv CTRL + SHIFT + ENTER - Excel yuav cia li kaw cov mis nrog curly braces, raws li qhia hauv qhov piv txwv:

Yog tias koj tsis xav siv cov qauv array rau koj txoj haujlwm, ces ntxiv lwm INDEX () rau cov qauv thiab nias ENTER, nws yuav zoo li hauv qhov piv txwv:

Cov qauv no ua haujlwm li cas

Cov mis no ua haujlwm zoo ib yam li tus qauv INDEX() MATCH() formula. Txhawm rau tshawb nrhiav ntau yam xwm txheej, koj tsuas yog tsim ntau yam cuav thiab muaj tseeb uas sawv cev rau tus neeg raug thiab tsis raug. Thiab tom qab ntawd cov xwm txheej no siv rau txhua qhov sib txuas ntawm cov array. Cov mis hloov pauv cov lus tsis tseeb thiab qhov tseeb rau 0 thiab 1, raws li, thiab tso tawm ib qho array qhov twg 1 yog qhov sib piv cov txiaj ntsig uas tau pom hauv txoj hlua. MATCH() yuav pom thawj tus nqi uas phim 1 thiab dhau mus rau tus qauv INDEX(). Thiab nws, nyob rau hauv lem, yuav xa rov qab tus nqi uas twb xav tau nyob rau hauv lub teev kab los ntawm cov yam xav tau kem.

Tus qauv tsis muaj ib qho array nyob ntawm lub peev xwm ntawm INDEX() los tswj lawv ntawm nws tus kheej. Qhov thib ob INDEX() nyob rau hauv cov mis matches dag (0), yog li nws hla tag nrho cov array nrog cov nqi rau lub MATCH() formula. 

Qhov no yog ib qho kev piav qhia ntev ntawm lub logic tom qab cov qauv no. Yog xav paub ntxiv nyeem kab lus "INDEX MATCH nrog ntau yam xwm txheej".

AVERAGE, MAX thiab MIN hauv INDEX thiab MATCH

Excel muaj nws cov haujlwm tshwj xeeb rau kev nrhiav qhov nruab nrab, qhov siab tshaj plaws, thiab qhov tsawg kawg nkaus. Tab sis yuav ua li cas yog tias koj xav tau cov ntaub ntawv los ntawm lub xov tooj ntawm tes nrog rau cov nqi no? Hauv qhov no AVERAGE, MAX thiab MIN yuav tsum tau siv ua ke nrog INDEX thiab MATCH.

INDEX MATCH thiab MAX

Txhawm rau nrhiav tus nqi loj tshaj plaws hauv kab D thiab tso rau hauv kab C, siv tus qauv: 

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

INDEX MATCH thiab MIN

Txhawm rau nrhiav tus nqi tsawg tshaj plaws hauv kab D thiab tso nws hauv kab C, siv cov qauv hauv qab no:

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

SEARCH INDEX thiab SERPENT

Txhawm rau nrhiav tus nqi nruab nrab hauv kab D thiab tso saib tus nqi no hauv C:

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

Nyob ntawm seb koj cov ntaub ntawv sau li cas, qhov kev sib cav thib peb rau MATCH() yog 1, 0, lossis -1:

• Yog hais tias cov kab tau txheeb nyob rau hauv ascending kev txiav txim, teem 1 (ces tus qauv yuav xam tus nqi siab tshaj plaws, uas yog tsawg dua los yog sib npaug rau qhov nruab nrab tus nqi);
• yog tias qhov kev txheeb xyuas tau nqis los, ces -1 (tus qauv yuav tso tawm qhov tsawg kawg nkaus tus nqi uas ntau dua lossis sib npaug rau qhov nruab nrab);
• Yog hais tias tus lookup array muaj tus nqi uas yog sib npaug zos rau qhov nruab nrab, ces muab tso rau 0. 

 Hauv peb qhov piv txwv, cov pej xeem raug txheeb xyuas los ntawm tus lej, yog li peb muab -1. Thiab qhov tshwm sim yog Tokyo, txij li cov pej xeem tus nqi (13,189) yog qhov ze tshaj plaws rau tus nqi nruab nrab (000).

VLOOKUP() kuj tuaj yeem ua cov kev suav suav, tab sis tsuas yog raws li cov qauv array: VLOOKUP nrog AVERAGE, MIN thiab MAX.

INDEX MATCH thiab ESND / IFERROR

Tej zaum koj twb pom tias yog cov mis tsis tuaj yeem nrhiav tus nqi xav tau, nws cuam tshuam qhov yuam kev # N / A. Koj tuaj yeem hloov cov lus yuam kev nrog ib yam dab tsi ntxiv. Piv txwv li, teeb qhov kev sib cav hauv cov qauv Hauv xyoo XNUMX:

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

Nrog rau cov qauv no, yog tias koj nkag mus rau cov ntaub ntawv uas tsis nyob hauv lub rooj, daim ntawv yuav muab cov lus teev tseg rau koj.

Yog tias koj xav ntes txhua qhov yuam kev, ces tsuas yog rau Hauv xyoo XNUMX yuav siv tau IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Ib yam dab tsi tsis ncaj ncees lawm!")

Tab sis nco ntsoov tias kev npog qhov yuam kev hauv txoj kev no tsis yog ib lub tswv yim zoo, vim tias cov txheej txheem yuam kev qhia txog kev ua txhaum cai hauv cov qauv.

Peb vam tias koj pom peb phau ntawv qhia siv INDEX MATCH() muaj txiaj ntsig zoo.