Yuav ua li cas Siv VLOOKUP Function hauv Excel

Cov txheej txheem:

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

Key Takeaways

VLOOKUP muaj nuj nqi hauv Excel yog siv los nrhiav tus nqi hauv daim ntawv nthuav qhia.
Cov syntax thiab arguments yog =VLOOKUP(search_value, lookup_table, column_number, [approximate_match])

Kab lus no piav qhia yuav ua li cas siv VLOOKUP muaj nuj nqi hauv txhua hom Excel, suav nrog Excel 2019 thiab Microsoft 365.

VLOOKUP Function yog dab tsi?

VLOOKUP muaj nuj nqi hauv Excel yog siv los nrhiav qee yam hauv lub rooj. Yog tias koj muaj kab ntawm cov ntaub ntawv teeb tsa los ntawm kab ntawv kab lus, VLOOKUP tuaj yeem siv los nrhiav tus nqi siv kab.

Thaum koj ua VLOOKUP, koj tab tom qhia Excel ua ntej nrhiav cov kab uas muaj cov ntaub ntawv koj xav tau rov qab, thiab tom qab ntawd xa rov qab tus nqi uas nyob hauv ib kem tshwj xeeb hauv kab ntawd.

VLOOKUP Function Syntax & Arguments

Muaj plaub feem ntawm txoj haujlwm no:

=VLOOKUP(search_value, lookup_table, column_number, [approximate_match])

search_value yog tus nqi koj tab tom nrhiav. Nws yuav tsum nyob hauv thawj kab ntawm lookup_table.
lookup_table yog qhov koj tab tom nrhiav hauv. Qhov no suav nrog search_value.
column_number yog tus lej uas sawv cev rau pes tsawg kab rau hauv lookup_table, ntawm sab laug, yuav tsum yog kab uas VLOOKUP rov qab tus nqi los ntawm.
approximate_match yog xaiv tau thiab tuaj yeem yog TRUE lossis FALSE. Nws txiav txim siab seb puas yuav nrhiav tau qhov sib tw tiag tiag lossis qhov kwv yees sib tw. Thaum tshem tawm, lub neej ntawd yog TRUE, txhais tau tias nws yuav pom qhov sib tw kwv yees.

VLOOKUP Function Piv txwv

Ntawm no yog qee qhov piv txwv uas qhia VLOOKUP muaj nuj nqi hauv kev nqis tes ua:

Nrhiav Tus Nqi Tom ntej ntawm ib lo lus ntawm lub rooj

=VLOOKUP("Lemons", A2:B5, 2)

Nov yog ib qho piv txwv yooj yim ntawm VLOOKUP muaj nuj nqi uas peb yuav tsum nrhiav seb muaj pes tsawg txiv qaub peb muaj hauv Tshuag los ntawm cov npe ntawm ob peb yam khoom. Qhov ntau peb tab tom saib yog A2: B5 thiab tus lej peb yuav tsum rub yog nyob rau hauv kab 2 txij li "Nyob hauv Tshuag" yog kab thib ob ntawm peb qhov ntau. Qhov tshwm sim ntawm no yog 22.

Nrhiav tus neeg ua haujlwm tus lej siv lawv lub npe

=VLOOKUP(A8, B2:D7, 3)

=VLOOKUP(A9, A2:D7, 2)

Ntawm no yog ob qho piv txwv uas peb sau VLOOKUP muaj nuj nqi sib txawv me ntsis. Lawv ob leeg siv cov ntaub ntawv zoo sib xws tab sis txij li thaum peb tab tom rub cov ntaub ntawv los ntawm ob kab sib cais, 3 thiab 2, peb ua qhov sib txawv ntawm qhov kawg ntawm cov qauv-thawj tus tuav txoj hauj lwm ntawm tus neeg hauv A8 (Finley) thaum. tus qauv thib ob rov qab lub npe uas phim tus lej neeg ua haujlwm hauv A9 (819868). Txij li cov qauv yog xa mus rau cov hlwb thiab tsis yog ib txoj kab ntawv tshwj xeeb, peb tuaj yeem tso tawm cov nqe lus.

Siv IF nqe lus nrog VLOOKUP

=IF(VLOOKUP(A2, Sheet4!A2:B5, 2)>10, "No", "Yes")

VLOOKUP tseem tuaj yeem ua ke nrog lwm cov haujlwm Excel thiab siv cov ntaub ntawv los ntawm lwm nplooj ntawv. Peb tab tom ua ob qho tib si hauv qhov piv txwv no txhawm rau txiav txim siab seb peb yuav tsum xaj ntau npaum li cas hauv Kab A. Peb siv IF muaj nuj nqi kom yog tias tus nqi hauv txoj hauj lwm 2 hauv Sheet4!A2:B5 yog ntau dua 10, peb sau Tsis yog qhia tias peb tsis tas yuav xaj ntau ntxiv.

Nrhiav tus lej ze tshaj plaws hauv lub rooj

=VLOOKUP(D2, $A$2:$B$6, 2)

Hauv qhov piv txwv kawg no, peb tab tom siv VLOOKUP los nrhiav qhov feem pua ntawm cov luv nqi uas yuav tsum tau siv rau ntau yam kev txiav txim ntawm nkawm khau. Qhov luv nqi peb tab tom nrhiav rau yog nyob rau hauv kab D, qhov ntau uas suav nrog cov ntaub ntawv luv nqi yog A2: B6, thiab nyob rau hauv qhov ntau yog kab 2 uas muaj cov luv nqi. Txij li thaum VLOOKUP tsis tas yuav nrhiav qhov sib tw tiag tiag, kwv yees_match tau tso tseg kom pom tseeb. Yog tias tsis pom qhov sib tw tiag tiag, lub luag haujlwm yuav siv tus nqi qis dua ntxiv.

Koj tuaj yeem pom tias hauv thawj qhov piv txwv ntawm 60 xaj, qhov luv nqi tsis pom nyob rau hauv lub rooj rau sab laug, yog li tom ntej me me ntawm 50 yog siv, uas yog 75% luv nqi. Kem F yog tus nqi kawg thaum tus nqi luv nqi hauv.

VLOOKUP yuam kev & Txoj Cai

Ntawm no yog qee yam yuav tsum nco ntsoov thaum siv VLOOKUP muaj nuj nqi hauv Excel:

Yog search_value yog ib txoj hlua ntawv, nws yuav tsum nyob ib puag ncig hauv cov lus.
Excel yuav rov NO MATCH yog tias VLOOKUP nrhiav tsis tau qhov tshwm sim.
Excel yuav rov qab NO MATCH yog tias tsis muaj tus lej hauv lookup_table uas ntau dua lossis sib npaug rau search_value.
Excel yuav rov qab REF! yog column_number ntau dua tus naj npawb ntawm kab hauv lookup_table.
search_value yeej ib txwm nyob rau sab laug ntawm lookup_table thiab yog txoj hauj lwm 1 thaum txiav txim column_number.
Yog koj qhia FALSE rau kwv yees_match thiab tsis pom qhov sib tw tiag tiag, VLOOKUP yuav rov qab N/A.
Yog koj qhia tseeb rau kwv yees_match thiab tsis pom qhov sib tw tiag tiag, tus nqi me dua tom ntej yuav rov qab los.
Cov ntxhuav tsis tau txheeb xyuas yuav tsum siv FALSE rau kwv yees_match kom thawj qhov sib tw raug xa rov qab.
Yog tias kwv yees_match yog qhov tseeb lossis raug tshem tawm, thawj kab yuav tsum tau txheeb cov tsiaj ntawv los yog lej. Yog tias nws tsis raug txheeb xyuas, Excel tuaj yeem xa rov qab tus nqi uas tsis xav txog.
Siv cov ntaub ntawv xovtooj tseem ceeb cia koj autofill cov qauv yam tsis hloov lookup_table.

Lwm txoj haujlwm zoo li VLOOKUP

VLOOKUP ua haujlwm ntsug saib, txhais tau tias nws khaws cov ntaub ntawv los ntawm suav cov kab. Yog tias cov ntaub ntawv raug teeb tsa kab rov tav thiab koj xav suav cov kab kom rov qab tau tus nqi, koj tuaj yeem siv HLOOKUP muaj nuj nqi.

XLOOKUP muaj nuj nqi zoo ib yam tab sis ua haujlwm nyob rau hauv txhua qhov kev taw qhia.