Key Takeaways
- The MATCH function syntax is =MATCH(Lookup_value, Lookup_array, Match_type).
- Yuav nkag tau manually. Lossis, txhawm rau siv Excel lub zog ua haujlwm, xaiv Formulas > Lookup & Reference > MATCH.
- Rov qab tus lej uas qhia txog thawj qhov txheeb ze ntawm cov ntaub ntawv hauv ib daim ntawv teev npe, array, lossis xaiv ntau yam ntawm cov hlwb.
Ntawm no yog yuav ua li cas siv Excel's MATCH muaj nuj nqi los nrhiav txoj haujlwm ntawm tus nqi hauv kab, kab, lossis rooj. Qhov no yuav pab tau thaum koj yuav tsum nrhiav cov khoom qhov chaw hauv daim ntawv tsis yog cov khoom nws tus kheej.
MATCH Function Syntax
A muaj nuj nqi syntax yog hais txog kev teeb tsa ntawm lub luag haujlwm thiab suav nrog cov haujlwm lub npe, cov hlua khi, cov cim cais, thiab cov lus sib cav. Cov syntax rau MATCH muaj nuj nqi yog:
=MATCH(Lookup_value, Lookup_array, Match_type)
MATCH Function Arguments
Txhua qhov kev tawm tswv yim uas koj muab rau qhov ua haujlwm yog hu ua kev sib cav. Feem ntau ntawm cov haujlwm uas pom hauv Excel xav tau qee cov tswv yim lossis cov ntaub ntawv txhawm rau xam kom raug.
Cov no yog MATCH muaj nuj nqi sib cav:
Lookup_value
Lookup_value (yuav tsum tau) yog tus nqi uas koj xav pom hauv daim ntawv teev cov ntaub ntawv. Qhov kev sib cav no tuaj yeem yog tus lej, ntawv nyeem, tus nqi tsim nyog, lossis kev siv ntawm tes.
Lookup_array
Lookup_array (yuav tsum tau) yog qhov ntau ntawm cov hlwb tshawb nrhiav.
match_type
Match_type (optional) qhia Excel yuav ua li cas kom phim Lookup_value nrog cov nqi hauv Lookup_array. Tus nqi pib rau qhov kev sib cav no yog 1. Cov kev xaiv yog -1, 0, lossis 1.
- Yog Match_type sib npaug 1 lossis raug rho tawm, MATCH pom tus nqi loj tshaj plaws uas tsawg dua lossis sib npaug rau Lookup_value. Cov ntaub ntawv Lookup_array yuav tsum tau muab faib ua ntu zus.
- Yog Match_type sib npaug 0, MATCH pom thawj tus nqi uas yog sib npaug rau Lookup_value. Cov ntaub ntawv Lookup_array tuaj yeem txheeb tau hauv txhua qhov kev txiav txim.
- Yog Match_type sib npaug -1, MATCH pom tus nqi tsawg tshaj plaws uas ntau dua lossis sib npaug rau Lookup_value. Cov ntaub ntawv Lookup_array yuav tsum tau txheeb xyuas los ntawm tus lej.
Yuav ua li cas Siv MATCH Function hauv Excel
Tus piv txwv MATCH qhia hauv qhov kev qhia no siv lub luag haujlwm los nrhiav txoj haujlwm ntawm lub sijhawm Gizmos hauv daim ntawv teev npe. Cov kev ua haujlwm syntax tuaj yeem nkag mus rau hauv lub xovtooj ntawm tes lossis siv Excel cov haujlwm ua haujlwm, raws li qhia ntawm no.
Nkag mus rau MATCH muaj nuj nqi thiab kev sib cav:
-
Qhib ib daim ntawv Excel dawb paug thiab sau cov ntaub ntawv hauv kab C, D, thiab E, raws li pom hauv daim duab hauv qab no. Cia lub cell D2 dawb paug, raws li lub xov tooj ntawm tes yuav tuav lub luag haujlwm.
Image - Xaiv cell D2 ua kom nws lub cev muaj zog.
- Xaiv tus Formulas tab ntawm ribbon ntawv qhia.
- Choose Lookup & Reference txhawm rau qhib qhov Function npe poob.
- Xaiv MATCH hauv daim ntawv los qhib lub Function Arguments dialog box. (Hauv Excel rau Mac, Formula Builder qhib.)
- Tau tus cursor rau hauv Lookup_value ntawv lub thawv.
- Xaiv cell C2 hauv daim ntawv ua haujlwm kom nkag mus rau ntawm tes siv.
-
Tau tus cursor rau hauv Lookup_array ntawv lub thawv.
- Highlight cells E2 rau E7 hauv daim ntawv ua haujlwm kom nkag mus rau ntau yam.
- Muab tus cursor rau hauv Match_type ntawv lub thawv.
-
Tau tus lej 0 ntawm kab no txhawm rau nrhiav qhov tseeb phim rau cov ntaub ntawv hauv xov tooj D3.
Image - Xaiv OK kom ua tiav txoj haujlwm. (Hauv Excel rau Mac, xaiv Done.)
- Tus lej 5 tshwm hauv cell D3 txij li lub sijhawm Gizmos yog qhov thib tsib ntawm cov khoom saum toj kawg nkaus hauv daim ntawv teev npe.
-
Thaum koj xaiv cell D3, qhov ua tiav tiav tshwm nyob rau hauv cov qauv bar saum daim ntawv ua haujlwm.
=MATCH(C2, E2:E7, 0)
Combine MATCH Nrog Lwm Cov Haujlwm Excel
Qhov MATCH muaj nuj nqi feem ntau yog siv ua ke nrog rau lwm yam kev saib xyuas, xws li VLOOKUP lossis INDEX thiab siv los ua cov tswv yim rau lwm qhov kev sib cav, xws li:
- The col_index_num argument for VLOOKUP.
- Tus kab_num sib cav rau INDEX muaj nuj nqi.
