Yuav ua li cas Siv Excel's LOOKUP Function los nrhiav cov ntaub ntawv

2024 Tus sau: Abigail Brown | [email protected]. Kawg hloov kho: 2024-01-07 19:03

Cov haujlwm Excel LOOKUP muaj ob daim ntawv: Vector Form thiab Daim Ntawv Array. Daim ntawv Array ntawm LOOKUP muaj nuj nqi zoo ib yam li lwm yam Excel nrhiav haujlwm xws li VLOOKUP thiab HLOOKUP. Koj tuaj yeem siv nws los nrhiav lossis saib cov txiaj ntsig tshwj xeeb nyob hauv cov ntaub ntawv.

LOOKUP vs. VLOOKUP and HLOOKUP

Nws txawv li cas:

• Nrog VLOOKUP thiab HLOOKUP, koj tuaj yeem xaiv kab lossis kab twg los xa cov ntaub ntawv tus nqi los ntawm. LOOKUP ib txwm rov qab tus nqi los ntawm kab kawg lossis kab hauv kab hauv array.
• Thaum nrhiav kev sib tw rau tus nqi teev tseg (lub Lookup_value), VLOOKUP tsuas yog tshawb nrhiav thawj kab ntawm cov ntaub ntawv thiab HLOOKUP tsuas yog thawj kab. LOOKUP muaj nuj nqi tshawb nrhiav thawj kab lossis kab, nyob ntawm seb cov duab ntawm cov array.

LOOKUP Function thiab Array Shape

Cov duab ntawm cov array tuaj yeem yog square (sib npaug ntawm kab thiab kab) lossis ib daim duab plaub (tsis sib npaug ntawm kab thiab kab). Cov duab cuam tshuam qhov twg LOOKUP muaj nuj nqi tshawb nrhiav cov ntaub ntawv:

• Yog tias ib qho array yog square lossis yog tias nws yog lub duab plaub siab (siab dua li nws qhov dav), LOOKUP xav tias cov ntaub ntawv tau teeb tsa hauv txhua kab thiab tshawb nrhiav qhov sib tw rau Lookup_value hauv thawj kab ntawm array.
• Yog tias ib qho array yog ib daim duab plaub dav (dav dua li nws siab), LOOKUP xav tias cov ntaub ntawv tau teeb tsa hauv kab thiab tshawb nrhiav qhov sib tw rau Lookup_value hauv thawj kab ntawm cov array.

Lub LOOKUP Function Syntax thiab Cov Lus Sib Tham: Daim Ntawv Sau Npe

Cov lus sib dhos rau Daim Ntawv Array ntawm LOOKUP muaj nuj nqi yog:

=LOOKUP(Lookup_value, Array)
Lookup_value (yuav tsum tau): Tus nqi uas lub luag haujlwm tshawb nrhiav hauv array. Lookup_value tuaj yeem yog tus lej, ntawv nyeem, tus nqi tsim nyog, lossis lub npe lossis xovtooj siv uas hais txog tus nqi.
Array (yuav tsum tau): Ntau cov hlwb uas ua haujlwm tshawb nrhiav kom pom Lookup_value. Cov ntaub ntawv tuaj yeem yog cov ntawv nyeem, lej, lossis qhov muaj txiaj ntsig.

Piv txwv Siv Daim Ntawv Array ntawm LOOKUP Function
Qhov piv txwv no siv Daim Ntawv Array ntawm LOOKUP muaj nuj nqi txhawm rau nrhiav tus nqi ntawm Whachamacallit hauv daim ntawv teev npe.
Cov duab ntawm cov array yog siab daim duab plaub, thiab cov haujlwm xa rov qab tus nqi nyob rau hauv kab kawg ntawm daim ntawv teev npe.
Ua raws li qhov piv txwv no, nkag mus rau cov ntaub ntawv qhia hauv daim ntawv ua haujlwm hauv qab no.






Xaiv cov ntaub ntawv
Koj yuav tsum txheeb cov ntaub ntawv hauv array hauv kev nce qib kom lub LOOKUP muaj nuj nqi ua haujlwm zoo. Thaum txheeb cov ntaub ntawv hauv Excel, xaiv cov kab thiab kab ntawm cov ntaub ntawv los txheeb xyuas thawj zaug, uas feem ntau suav nrog cov kab ntawv kab lus.


Highlight cells A4 to C10 hauv daim ntawv ua haujlwm.







Nyob rau ntawm kab, mus rau Data tab.







In the Sort & Filter pab pawg, xaiv Sort txhawm rau qhib lub dialog box.







Nyob hauv qab Kem nqe lus, xaiv cov ntawv qhia zaub mov thiab xaiv xaiv los ntawm Part.







Nyob rau hauv Saib ntawm nqe lus, xaiv cov ntawv qhia zaub mov thiab xaiv Cell Values.







Nyob hauv Order nqe lus, xaiv cov ntawv qhia zaub mov thiab xaiv A rau Z.







Xaiv OK txhawm rau txheeb cov ntaub ntawv thiab kaw lub dialog box.








LOOKUP Function Piv txwv
Txawm hais tias nws tuaj yeem ntaus LOOKUP muaj nuj nqi, =LOOKUP(A2, A5:C10), rau hauv daim ntawv xovtooj, koj yuav pom tias nws tsis meej pem rau kev siv lub luag haujlwm. dialog box. Lub dialog box tso cai rau koj nkag mus rau txhua qhov kev sib cav ntawm ib kab sib cais yam tsis muaj kev txhawj xeeb txog kev ua haujlwm ntawm syntax, xws li kab lus thiab cov cim cais ntawm cov lus sib cav.
Cov kauj ruam hauv qab no qhia meej tias txoj haujlwm LOOKUP tau nkag mus rau hauv cell B2 siv lub thawv sib tham li cas.
Yog tias txoj haujlwm tsis tuaj yeem nrhiav qhov tseeb match rau Lookup_value, nws xaiv tus nqi loj tshaj plaws hauv Array uas tsawg dua lossis sib npaug ntawm tus nqi rau Lookup_value. Yog tias Lookup_value ploj lawm lossis tsawg dua txhua qhov tseem ceeb hauv Array, LOOKUP muaj nuj nqi rov qab ua qhov yuam kev N/A.


Hauv daim ntawv ua haujlwm, xaiv cell B2 ua kom nws lub cev muaj zog.







mus rau Formula tab.







Xaiv Nrhiav thiab siv txhawm rau qhib lub luag haujlwm cov npe poob.







Xaiv LOOKUP txhawm rau tso tawm Xaiv Cov Lus Sib Tham dialog box.







Xaiv lookup_value, array, thiab xaiv OK los tso saib Function Arguments dialog box.







Nyob hauv lub dialog box, xaiv lub Lookup_value ntawv lub thawv.







Hauv daim ntawv ua haujlwm, xaiv lub xovtooj A2 txhawm rau nkag mus rau qhov kev siv ntawm tes rau hauv lub thawv sib tham.







Nyob hauv lub dialog box, xaiv lub Array ntawv lub thawv.







Nyob rau hauv daim ntawv ua haujlwm, qhia cov hlwb A5 rau C10 txhawm rau nkag mus rau qhov ntau yam no rau hauv lub thawv sib tham. Cov kab no muaj cov ntaub ntawv uas yuav tsum tau tshawb nrhiav los ntawm txoj haujlwm.






Xaiv OK kom ua tiav txoj haujlwm thiab kaw lub thawv sib tham.

An N/A yuam kev tshwm hauv cell B2 vim tias koj yuav tsum ntaus ib feem npe hauv cell A2.








Ntaus tus nqi nrhiav
Ntawm no yog yuav sau ib lub npe mus nrhiav tus nqi ntawm ib yam khoom:


Xaiv tes A2, ntaus Whachamacallit, thiab nias lub Enter tus yuam sij ntawm cov keyboard.






Tus nqi $23.56 tshwm hauv cell B2. Qhov no yog tus nqi ntawm Whachamacallit nyob rau hauv kab kawg ntawm lub rooj ntaub ntawv.
Test the function by typing other part names into cell A2. Tus nqi rau txhua feem hauv daim ntawv teev npe tshwm hauv cell B2.
Thaum koj xaiv lub xovtooj B2 ua tiav =LOOKUP(A2, A5:C10) tshwm hauv cov qauv bar saum daim ntawv ua haujlwm.