Yuav ua li cas Siv INDEX thiab MATCH Function hauv Excel

2024 Tus sau: Abigail Brown | [email protected]. Kawg hloov kho: 2023-12-17 06:49

Yuav Paub

Lub INDEX muaj nuj nqi tuaj yeem siv ib leeg, tab sis nesting MATCH muaj nuj nqi hauv nws tsim qhov kev saib xyuas siab heev.
Qhov kev ua haujlwm nested no hloov tau yooj yim dua li VLOOKUP thiab tuaj yeem ua tau sai dua.

Kab lus no piav qhia yuav ua li cas siv INDEX thiab MATCH ua haujlwm ua ke hauv txhua yam ntawm Excel, suav nrog Excel 2019 thiab Microsoft 365.

INDEX thiab MATCH yog dab tsi?

INDEX thiab MATCH yog Excel nrhiav haujlwm. Thaum lawv yog ob txoj haujlwm sib cais tag nrho uas tuaj yeem siv tau ntawm lawv tus kheej, lawv tuaj yeem ua ke los tsim cov qauv siab.

INDEX muaj nuj nqi xa rov qab tus nqi lossis siv rau tus nqi los ntawm kev xaiv tshwj xeeb. Piv txwv li, nws tuaj yeem siv los nrhiav tus nqi hauv kab thib ob ntawm cov ntaub ntawv teeb tsa, lossis hauv kab thib tsib thiab kab thib peb.

Thaum INDEX tuaj yeem siv tau zoo ib leeg, nesting MATCH hauv cov qauv ua rau nws muaj txiaj ntsig ntau dua. MATCH muaj nuj nqi tshawb nrhiav ib yam khoom tshwj xeeb hauv ntau lub hlwb thiab tom qab ntawd rov qab qhov chaw txheeb ze ntawm cov khoom hauv qhov ntau. Piv txwv li, nws tuaj yeem siv los txiav txim siab tias lub npe tshwj xeeb yog qhov thib peb hauv cov npe npe.

INDEX thiab MATCH Syntax & Arguments

Qhov no yog ob txoj haujlwm yuav tsum tau sau kom Excel kom nkag siab lawv:

=INDEX(array, row_num, [column_num])

array yog ntau lub hlwb uas tus qauv yuav siv. Nws tuaj yeem yog ib lossis ntau kab thiab kab, xws li A1: D5. Yuav tsum tau.
row_num yog kab hauv cov array uas yuav rov qab tus nqi, xws li 2 lossis 18. Nws yuav tsum tau tshwj tsis yog column_num tam sim no.
column_num yog kem hauv cov array uas yuav rov qab tus nqi, xws li 1 lossis 9. Nws yog xaiv tau.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value yog tus nqi koj xav kom phim hauv lookup_array. Nws tuaj yeem yog tus lej, ntawv nyeem, lossis tus nqi tsim nyog uas tau ntaus manually lossis xa mus los ntawm kev siv ntawm tes. Qhov no yuav tsum tau.
lookup_array yog ntau lub hlwb los saib. Nws tuaj yeem yog ib kab lossis ib kab, xws li A2: D2 lossis G1: G45. Qhov no yuav tsum tau.
match_type tuaj yeem yog -1, 0, lossis 1. Nws qhia meej tias lookup_value tau sib phim nrog cov txiaj ntsig hauv lookup_array li cas (saib hauv qab). 1 yog tus nqi pib yog tias qhov kev sib cav no raug tshem tawm.

Yuav Ua Li Cas Siv Tau
Match Type		Rule	Example
1	Nrhiav tus nqi loj tshaj plaws uas tsawg dua lossis sib npaug rau lookup_value.	Tus lookup_array qhov tseem ceeb yuav tsum muab tso rau hauv qhov kev txiav txim nce mus ntxiv (piv txwv li, -2, -1, 0, 1, 2; lossis A-Z;, lossis FALSE, TRUE.	lookup_value yog 25 tab sis nws ploj lawm ntawm lookup_array, yog li txoj hauj lwm ntawm tus lej qis tshaj tom ntej, zoo li 22, rov qab los.
0	Nrhiav thawj tus nqi uas yog sib npaug rau lookup_value.	Tus nqi lookup_array tuaj yeem ua tau raws li qhov kev txiav txim.	lookup_value yog 25, yog li nws rov qab txoj haujlwm ntawm 25.
-1	Nrhiav tus nqi tsawg tshaj plaws uas ntau dua lossis sib npaug rau lookup_value.	Tus nqi lookup_array yuav tsum tau muab tso rau hauv qhov kev txiav txim qis (piv txwv li, 2, 1, 0, -1, -2).	lookup_value yog 25 tab sis nws ploj lawm ntawm lookup_array, yog li txoj haujlwm ntawm tus lej loj tshaj plaws tom ntej, zoo li 34, rov qab hloov dua.

Siv 1 lossis -1 rau lub sijhawm thaum koj xav tau khiav qhov kwv yees saib raws li qhov ntsuas, zoo li thaum cuam tshuam nrog cov lej thiab thaum kwv yees ua tau zoo. Tab sis nco ntsoov tias yog tias koj tsis qhia meej match_type, 1 yuav yog lub neej ntawd, uas tuaj yeem cuam tshuam cov txiaj ntsig yog tias koj xav tau qhov tseeb tiag.

Example INDEX thiab MATCH Formulas

Ua ntej peb saib yuav ua li cas muab INDEX thiab MATCH ua ib qho qauv, peb yuav tsum nkag siab tias cov haujlwm no ua haujlwm li cas ntawm lawv tus kheej.

INDEX Piv txwv

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

=INDEX(2:2, 1)=INDEX(B1:B2, 1)

Hauv qhov piv txwv thawj no, muaj plaub tus qauv INDEX peb tuaj yeem siv kom tau txais qhov sib txawv:

=INDEX(A1:B2, 2, 2) saib dhau A1:B2 kom pom tus nqi hauv kab thib ob thiab kab thib ob, uas yog Stacy.
=INDEX(A1:B1, 1) saib dhau A1:B1 kom pom tus nqi hauv thawj kab, uas yog Jon.
=INDEX(2:2, 1) saib txhua yam hauv kab thib ob txhawm rau txheeb xyuas tus nqi hauv thawj kab, uas yog Tim.
=INDEX(B1:B2, 1) saib los ntawm B1:B2 txhawm rau txheeb xyuas tus nqi hauv thawj kab, uas yog Amy.

MATCH Piv txwv

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

Ntawm no yog plaub qhov piv txwv yooj yim ntawm MATCH muaj nuj nqi:

=MATCH("Stacy", A2:D2, 0) tab tom nrhiav Stacy hauv thaj tsam A2:D2 thiab xa rov qab 3 raws li qhov tshwm sim.
=MATCH(14, D1:D2) tab tom nrhiav rau 14 hauv thaj tsam D1:D2, tab sis vim nws tsis pom hauv lub rooj, MATCH pom tus nqi loj tshaj plaws tom ntej uas yog tsawg dua los yog sib npaug rau 14, uas nyob rau hauv cov ntaub ntawv no yog 13, uas yog nyob rau hauv txoj hauj lwm 1 ntawm lookup_array.
=MATCH(14, D1:D2, -1) zoo ib yam rau cov qauv saum toj no, tab sis txij li cov array tsis nyob rau hauv nqe lus txiav txim zoo li -1 xav tau, peb tau txais qhov yuam kev.
=MATCH(13, A1:D1, 0) tab tom nrhiav 13 nyob rau hauv thawj kab ntawm daim ntawv, uas rov 4 vim nws yog plaub yam khoom nyob rau hauv no array.

INDEX-MATCH Piv txwv

Ntawm no yog ob qho piv txwv uas peb tuaj yeem ua ke INDEX thiab MATCH hauv ib qho qauv:

Nrhiav Cell Reference hauv Table

=INDEX(B2:B5, MATCH(F1, A2:A5))

Qhov piv txwv no yog nesting MATCH formula nyob rau hauv tus qauv INDEX. Lub hom phiaj yog txhawm rau txheeb xyuas cov khoom xim uas siv tus lej khoom.

Yog koj saib daim duab no koj yuav pom nyob rau hauv kab "Sib cais" yuav ua li cas cov qauv yuav sau rau ntawm lawv tus kheej, tab sis txij li thaum peb tab tom ua zes lawv, qhov no tshwm sim:

MATCH(F1, A2:A5) tab tom nrhiav tus nqi F1 (8795) hauv cov ntaub ntawv teev A2:A5. Yog tias peb suav cov kab ke, peb tuaj yeem pom nws yog 2, yog li ntawd yog qhov MATCH muaj nuj nqi xwb.
INDEX array yog B2:B5 txij li thaum kawg peb tab tom nrhiav tus nqi hauv kab ntawv.
Lub luag haujlwm INDEX tam sim no tuaj yeem rov sau dua li qhov no txij li 2 yog qhov MATCH pom: INDEX(B2:B5, 2, [column_num]).
Vim column_num yog xaiv tau, peb tuaj yeem tshem tawm qhov uas yuav tsum tau tso nrog qhov no: INDEX(B2:B5, 2).
Yog li tam sim no, qhov no zoo li cov qauv INDEX ib txwm uas peb tab tom nrhiav tus nqi ntawm cov khoom thib ob hauv B2:B5, uas yog liab.

Nrhiav Los Ntawm Kab thiab Kab Kab

=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

Hauv qhov piv txwv ntawm MATCH thiab INDEX, peb tab tom ua ob txoj kev saib. Lub tswv yim yog saib seb peb tau nyiaj npaum li cas ntawm cov khoom ntsuab hauv lub Tsib Hlis. Qhov no yeej zoo ib yam li cov piv txwv saum toj no, tab sis ib qho ntxiv MATCH formula yog nested hauv INDEX.

MATCH(G1, A2:A13, 0) yog thawj yam uas daws tau hauv cov qauv no. Nws tab tom nrhiav G1 (lo lus "Tswj") hauv A2: A13 kom tau txais tus nqi tshwj xeeb. Peb tsis pom ntawm no tab sis yog 5.
MATCH(G2, B1:E1, 0) yog tus qauv MATCH thib ob, thiab nws yeej zoo ib yam li thawj zaug tab sis tab tom nrhiav G2 (lo lus "ntsuab") hauv kab ntawv kab lus ntawm B1: E1. Qhov no daws tau 3.
Tam sim no Peb tuaj yeem sau cov qauv INDEX zoo li qhov no kom pom qhov tshwm sim: =INDEX(B2:E13, 5, 3). Qhov no tab tom saib tag nrho cov lus, B2:E13, rau kab thib tsib thiab kab thib peb, uas rov qab $ 180.

MATCH thiab INDEX Cov Cai

Muaj ntau yam uas yuav tsum nco ntsoov thaum sau cov qauv nrog cov haujlwm no:

MATCH tsis yog cov ntaub ntawv rhiab, yog li cov tsiaj ntawv loj thiab tus tsiaj ntawv me raug kho tib yam thaum sib piv cov ntawv tseem ceeb.
MATCH rov N / A rau ntau qhov laj thawj: yog match_type yog 0 thiab lookup_value tsis pom yog match_type yog -1 thiab lookup_array tsis nyob rau hauv nqe lus txiav txim, yog match_type yog 1 thiab lookup_array tsis nyob rau hauv ascending kev txiav txim, thiab yog lookup_array tsis yog ib kab lossis kab.
Koj tuaj yeem siv tus cim wildcard hauv qhov kev sib cav lookup_value yog match_type yog 0 thiab lookup_value yog cov ntawv nyeem. Ib lo lus nug cim phim ib tus cwj pwm thiab lub hnub qub sib tw ua ke ntawm cov cim (e.g., =MATCH("Jo", 1:1, 0)). Txhawm rau siv MATCH kom pom cov lus nug tiag tiag lossis lub hnub qub, ntaus ~ ua ntej.
INDEX rov REF! yog row_num thiab column_num tsis taw tes rau lub xov tooj ntawm tes hauv qhov array.

Related Excel Functions

MATCH muaj nuj nqi zoo ib yam li LOOKUP, tab sis MATCH xa rov qab txoj haujlwm ntawm cov khoom es tsis txhob ntawm cov khoom nws tus kheej.

VLOOKUP yog lwm txoj haujlwm saib koj tuaj yeem siv hauv Excel, tab sis tsis zoo li MATCH uas xav tau INDEX rau kev nrhiav qib siab, VLOOKUP cov qauv tsuas xav tau ib qho haujlwm xwb.