Yuav Paub
- Add lookup_value > table_array > col_index_num > range_lookupthiab nias Enter.
- Qhov kev sib cav range_lookup yog xaiv tau. Siv TRUE rau qhov sib tw ze thiab FALSE rau qhov sib tw tiag tiag.
- N/A thiab REF yuam kev los ntawm cov lus tsis txaus los yog tsis raug rau lookup_value, table_array, lossis range_lookup.
Excel's VLOOKUP muaj nuj nqi, uas sawv cev rau "kev saib ntsug," saib tus nqi hauv thawj kab ntawm ib qho, thiab xa rov qab tus nqi hauv lwm kab hauv tib kab. Yog tias koj nrhiav tsis tau lub xovtooj twg muaj cov ntaub ntawv tshwj xeeb, VLOOKUP yog txoj hauv kev zoo los nrhiav cov ntaub ntawv ntawd. Nws yog qhov tshwj xeeb tshaj yog muaj txiaj ntsig hauv cov ntawv loj loj uas nws nyuaj rau nrhiav cov ntaub ntawv. Cov lus qhia no siv rau Excel rau Microsoft 365, Excel 2019, 2016, 2013, 2010, Excel rau Mac, thiab Excel Online.
Yuav ua li cas VLOOKUP Function ua haujlwm
VLOOKUP feem ntau xa rov qab ib daim teb ntawm cov ntaub ntawv raws li nws cov zis.
Yuav ua li cas qhov no:
- Koj muab lub npe los yog lookup_value uas qhia VLOOKUP kab ntawm cov ntaub ntawv twg los nrhiav cov ntaub ntawv xav tau.
- Koj muab tus lej kab raws li col_index_num sib cav, uas qhia VLOOKUP kab ntawv twg muaj cov ntaub ntawv koj nrhiav.
- Lub luag haujlwm saib rau lookup_value hauv thawj kab ntawm cov ntaub ntawv.
- VLOOKUP tom qab ntawd nrhiav thiab xa cov ntaub ntawv rov qab los ntawm kab lej koj tau teev tseg hauv col_index_num, los ntawm tib kab raws li tus nqi nrhiav.
VLOOKUP Function Arguments and Syntax
Tus syntax rau VLOOKUP muaj nuj nqi yog:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
VLOOKUP muaj nuj nqi yuav zoo li tsis meej pem vim nws muaj plaub qhov kev sib cav, tab sis nws ncaj nraim rau kev siv.
Plaub qhov kev sib cav rau VLOOKUP muaj nuj nqi yog raws li hauv qab no:
lookup_value (yuav tsum tau): Tus nqi los nrhiav hauv thawj kab ntawm lub rooj array.
table_array (yuav tsum tau) - Qhov no yog lub rooj ntawm cov ntaub ntawv (ntau lub hlwb) uas VLOOKUP tshawb nrhiav kom pom cov ntaub ntawv koj xav tau.
- Table_array yuav tsum muaj tsawg kawg yog ob kab ntawm cov ntaub ntawv
- thawj kab yuav tsum muaj qhov lookup_value
col_index_num (yuav tsum tau) - Qhov no yog kab zauv ntawm tus nqi koj xav nrhiav.
- Tus lej pib nrog kab 1
- Yog tias koj siv tus lej ntau dua li cov kab hauv kab lus, cov haujlwm yuav rov qab REF! yuam kev
range_lookup (optional) - Qhia seb puas lossis tsis yog tus nqi nrhiav poob rau hauv qhov ntau uas muaj nyob rau hauv lub rooj array. Qhov kev sib cav range_lookup yog "TRUE" lossis "FALSE." Siv TRUE rau qhov kwv yees qhov sib tw thiab FALSE rau qhov sib tw tiag tiag. Yog tias tshem tawm, tus nqi yog qhov tseeb los ntawm lub neej ntawd.
Yog qhov sib cav_lookup yog qhov tseeb, ces:
- Tus lookup_value yog tus nqi uas koj xav tshuaj xyuas seb nws puas poob rau hauv ib thaj tsam uas tau teev tseg los ntawm table_array.
- Table_array muaj tag nrho cov kab thiab kab uas muaj cov nqi ntau (xws li siab, nruab nrab, lossis qis).
- Cov lus sib cav col_index_num yog qhov txiaj ntsig qhov txiaj ntsig.
How the Range_Lookup Argument Works
Siv qhov xaiv tau range_lookup kev sib cav nyuaj rau ntau tus neeg kom nkag siab, yog li nws tsim nyog saib tus qauv nrawm.
Qhov piv txwv hauv daim duab saum toj no siv VLOOKUP muaj nuj nqi txhawm rau nrhiav tus nqi luv nqi nyob ntawm tus lej ntawm cov khoom yuav.
piv txwv qhia tias qhov luv nqi rau kev yuav khoom 19 yog 2% vim 19 poob ntawm 11 txog 21 hauv Quantity kab ntawm lub rooj saib.
Vim li ntawd, VLOOKUP rov qab tus nqi los ntawm kab thib ob ntawm lub rooj saib txij li kab ntawd muaj qhov tsawg kawg nkaus ntawm qhov ntau. Lwm txoj hauv kev los teeb tsa lub rooj saib ntau yam yuav yog los tsim ib kab thib ob rau qhov siab tshaj plaws, thiab qhov ntau yam no yuav muaj qhov tsawg kawg nkaus ntawm 11 thiab qhov siab tshaj plaws ntawm 20. Tab sis qhov tshwm sim ua haujlwm tib yam.
Tus piv txwv siv cov qauv hauv qab no uas muaj VLOOKUP muaj nuj nqi txhawm rau nrhiav tus luv nqi rau cov khoom yuav khoom.
=VLOOKUP(C2, $C$5:$D$8, 2, TRUE)
- C2: Qhov no yog tus nqi nrhiav, uas tuaj yeem nyob hauv txhua lub xov tooj hauv daim ntawv nthuav qhia.
- $C$5:$D$8: Qhov no yog lub rooj ruaj khov uas muaj tag nrho cov kab uas koj xav siv.
- 2: Qhov no yog kab hauv kab lus saib ntau yam uas koj xav kom LOOKUP muaj nuj nqi rov qab.
- TRUE: Ua kom lub range_lookup feature ntawm qhov haujlwm no.
Thaum koj tau nias Enter thiab cov txiaj ntsig rov qab rau hauv thawj lub xovtooj, koj tuaj yeem ua tiav tag nrho cov kab ntawv txhawm rau saib cov txiaj ntsig ntau ntawm cov cell hauv lub nrhiav kab.
Cov lus sib cav range_lookup yog ib txoj hauv kev zoo los txheeb cov kab ntawm cov lej sib xyaw ua ntau pawg.
VLOOKUP yuam kev: N/A thiab REF
VLOOKUP muaj nuj nqi tuaj yeem rov qab qhov yuam kev hauv qab no.
N/A yog qhov "tus nqi tsis muaj" yuam kev thiab tshwm sim hauv cov xwm txheej hauv qab no:
- The lookup _value is not found in the first kem of the table_array argument
- The Table_array kev sib cav tsis raug. Piv txwv li, qhov kev sib cav yuav suav nrog cov kab khoob ntawm sab laug ntawm qhov ntau
- The Range_lookup argument yog teem rau FALSE, thiab qhov tseeb match rau lookup_value sib cav tsis pom nyob rau hauv thawj kab ntawm table_array
- The range_lookup kev sib cav yog teem rau TRUE, thiab tag nrho cov txiaj ntsig hauv thawj kab ntawm table_array loj dua qhov lookup_value
REF! ("reference out of range") yuam kev tshwm sim yog tias col_index_num ntau dua li cov kab hauv table_array.
