VLOOKUP ir XLOOKUP: kaip surasti duomenis kitoje lentelėje

Norint Excel'yje surasti duomenis kitoje lentelėje, naudojama paieškos funkcija: ji pagal raktą (pvz., prekės kodą) suranda eilutę kitoje lentelėje ir grąžina reikiamą reikšmę (pvz., kainą). Tai gali padaryti trimis būdais – VLOOKUP, XLOOKUP arba INDEX+MATCH. Jei tavo Excel versija naujesnė nei 2019, rinkis XLOOKUP. Jei failą atidarys žmonės su senesnėmis versijomis – INDEX+MATCH.

Žemiau tą patį pavyzdį išsprendžiu visais trimis būdais, kad matytum skirtumą. Pavyzdžio failą rasi nemokamuose prieduose.

Pavyzdys: prekių kodai ir kainos

Turi du lapus tame pačiame faile.

Lapas „Kainos“ – visų prekių sąrašas:

	A	B	C
1	Kodas	Prekė	Kaina
2	P-101	Varžtai	15,99
3	P-102	Klijai	4,99
4	P-103	Rankena	8,99
…	…	…	…

Lapas „Užsakymai“ – čia įrašai prekės kodą ir kiekį, o kaina turi atsirasti pati:

	A	B	C
1	Kodas	Kiekis	Kaina
2	P-103	28	?
3	P-101	16	?

Užduotis: į C2 įrašyti formulę, kuri pagal kodą A2 suranda kainą lape „Kainos“.

Formulėse argumentus atskiriu kabliataškiu ; – taip Excel veikia, kai kompiuteryje dešimtainis skyriklis yra kablelis. Jei tavo Excel'yje formulė su ; neveikia, rašyk kablelį.

1 būdas: VLOOKUP

=VLOOKUP(A2;Kainos!$A$2:$C$200;3;FALSE)

Ką tai reiškia, iš eilės:

1. A2 – ko ieškai (kodas).
2. Kainos!$A$2:$C$200 – kur ieškai. Svarbu: kodas turi būti pirmame šios srities stulpelyje. Sritis užrakinta su $, kad tempiant formulę žemyn ji nepasislinktų.
3. 3 – iš kelinto srities stulpelio grąžinti rezultatą (kaina yra trečiame).
4. FALSE – ieškoti tikslaus atitikmens.

Ką reikia žinoti apie VLOOKUP:

• Ieško tik į dešinę. Jei kodas būtų C stulpelyje, o pavadinimas A, VLOOKUP pavadinimo nesurastų.
• Stulpelio numeris įrašytas ranka. Jei kas nors kainų lape įterps stulpelį tarp B ir C, formulė toliau grąžins trečią stulpelį – jau ne kainą. Klaidos nebus, tik neteisingas skaičius.
• Pamiršus FALSE, formulė ieško apytikriai. Tada nesant tikslaus kodo ji gali grąžinti kitos prekės kainą. Tai dažniausia VLOOKUP klaida.

2 būdas: XLOOKUP

=XLOOKUP(A2;Kainos!$A$2:$A$200;Kainos!$C$2:$C$200)

1. A2 – ko ieškai.
2. Kainos!$A$2:$A$200 – stulpelis, kuriame ieškai.
3. Kainos!$C$2:$C$200 – stulpelis, iš kurio grąžini.

Viskas. Tikslus atitikmuo – numatytasis, stulpelių skaičiuoti nereikia, o grąžinamas stulpelis gali būti ir kairėje, ir dešinėje. Įterpus naują stulpelį, formulė nesulūžta, nes nurodai patį stulpelį, ne jo numerį.

Ketvirtas argumentas leidžia iš karto pasakyti, ką rodyti, jei kodo nėra:

=XLOOKUP(A2;Kainos!$A$2:$A$200;Kainos!$C$2:$C$200;"Nėra kainų sąraše")

Taip nebereikia apgaubti formulės IFERROR.

3 būdas: INDEX+MATCH

=INDEX(Kainos!$C$2:$C$200;MATCH(A2;Kainos!$A$2:$A$200;0))

Tai dvi funkcijos kartu:

• MATCH(A2;Kainos!$A$2:$A$200;0) suranda, kelintoje eilutėje yra kodas (pvz., 3). Nulis gale reiškia tikslų atitikmenį.
• INDEX(Kainos!$C$2:$C$200;3) grąžina trečią kainų stulpelio reikšmę.

Rezultatas toks pat kaip XLOOKUP: ieško bet kuria kryptimi, nesulūžta įterpus stulpelį. Skirtumas – užrašyti sunkiau, bet veikia visose Excel versijose. Prieš atsirandant XLOOKUP, INDEX+MATCH buvo standartas tiems, kam VLOOKUP apribojimai trukdė.

Kurį būdą rinktis

	VLOOKUP	XLOOKUP	INDEX+MATCH
Ieško į kairę	Ne	Taip	Taip
Nesulūžta įterpus stulpelį	Ne	Taip	Taip
Tikslus atitikmuo be papildomo argumento	Ne (reikia FALSE)	Taip	Ne (reikia 0)
Savas tekstas, kai nerasta	Tik su IFERROR	Taip, 4 argumentas	Tik su IFERROR
Veikia Excel 2016 ir 2019	Taip	Ne	Taip
Veikia Google Sheets	Taip	Taip	Taip

Mano taisyklė paprasta:

1. Naujam failui – XLOOKUP, jei visi, kas juo naudosis, turi Microsoft 365, Excel 2021 ar naujesnę versiją arba dirba Google Sheets.
2. INDEX+MATCH – jei failą siunti klientams ar kolegoms, kurių Excel versijos nežinai. Senesnėje versijoje XLOOKUP rodys #NAME?.
3. VLOOKUP – užtenka mokėti perskaityti, nes jo pilni seni failai. Jei rašai pats, visada su FALSE gale.

Mano patarimas: prieš rašydamas paieškos formulę, pirmiausia sutvarkyk lentelę, kurioje ieškosi. Vienas stulpelis – vienas duomenų tipas, kodai be tarpų, jokių sujungtų langelių, viena eilutė – viena prekė. Dauguma „formulė neveikia“ atvejų yra ne formulės, o netvarkingos lentelės problema. Kai lentelė tvarkinga, bet kuris iš trijų būdų veikia.

Kodėl paieška grąžina #N/A

#N/A reiškia „neradau“. Jei esi tikras, kad reikšmė lentelėje yra, patikrink šiuos dalykus iš eilės:

1. Tarpai. „P-101“ ir „P-101 “ (su tarpu gale) Excel'iui skirtingi. Patikrink su =LEN(A2) – ar simbolių skaičius toks, koks turėtų būti. Valyti padeda =TRIM(A2).
2. Skaičius kaip tekstas. Jei kodai yra skaičiai, viename lape jie gali būti įrašyti kaip skaičiai, kitame – kaip tekstas (dažnai su žaliu trikampiu kampe). Pažymėk stulpelį ir pakeisk į skaičius per šauktuko piktogramą.
3. Neužrakinta sritis. Tempiant formulę žemyn be $, paieškos sritis slenka kartu, ir apatinės eilutės nebeatsiduria srityje.
4. VLOOKUP: kodas ne pirmame stulpelyje. Srities pirmas stulpelis turi būti tas, kuriame ieškai.
5. Kodo tikrai nėra. Tada formulė veikia teisingai – trūksta duomenų kainų lape.

Dar trys situacijos, kurios pasitaiko dažnai

Paieška pagal du požymius

Kartais vienas kodas neunikalus – pavyzdžiui, ta pati prekė turi skirtingą kainą skirtinguose sandėliuose. Tada ieškai pagal du požymius iš karto. XLOOKUP leidžia juos sujungti su &:

=XLOOKUP(A2&B2;Kainos!$A$2:$A$200&Kainos!$B$2:$B$200;Kainos!$C$2:$C$200)

Čia A2 – kodas, B2 – sandėlis. Formulė sujungia abu į vieną raktą (pvz., „P-101Vilnius“) ir ieško tokio pat sujungimo kainų lape. Senesnėse versijose šis būdas neveiks – tada paprasčiausia kainų lape pridėti pagalbinį stulpelį =A2&B2 ir ieškoti jame.

Apytikslė paieška: nuolaida pagal kiekį

Yra atvejų, kai tikslaus atitikmens ir nereikia. Pavyzdžiui, nuolaidų lentelė:

Kiekis nuo	Nuolaida
0	0 %
50	5 %
100	10 %

Užsakius 72 vienetus, turi būti 5 %. XLOOKUP penktas argumentas -1 reiškia „tikslus arba artimiausias mažesnis“:

=XLOOKUP(B2;Nuolaidos!$A$2:$A$4;Nuolaidos!$B$2:$B$4;0;-1)

Tai vienintelis atvejis, kai VLOOKUP su TRUE irgi tinka – bet tik jei pirmas stulpelis surikiuotas didėjimo tvarka.

Grąžinti kelis stulpelius iš karto

Jei reikia ne tik kainos, bet ir pavadinimo, XLOOKUP gali grąžinti kelis gretimus stulpelius viena formule:

=XLOOKUP(A2;Kainos!$A$2:$A$200;Kainos!$B$2:$C$200)

Rezultatas „išsilieja“ į du langelius dešinėn. Svarbu, kad tie langeliai būtų tušti, kitaip matysi #SPILL!.

Kaip patikrinti, ar paieška grąžina teisingai

Formulė be klaidos dar nereiškia, kad rezultatas teisingas. Prieš pasitikėdamas nauja paieškos formule:

1. Patikrink rankomis dvi ar tris eilutes – pirmą, paskutinę ir vieną iš vidurio.
2. Suskaičiuok, kiek eilučių grąžino „Nėra kainų sąraše“: =COUNTIF(C2:C500;"Nėra kainų sąraše"). Jei skaičius netikėtai didelis, problema greičiausiai duomenyse, ne formulėje.
3. Jei kainų lapas pildomas iš kitos sistemos, po kiekvieno atnaujinimo pažiūrėk, ar nepasikeitė stulpelių tvarka ar kodų formatas.

Užduotis tau

1. Surask failą, kuriame dabar kopijuoji duomenis iš vienos lentelės į kitą (kainas, el. pašto adresus, darbuotojų skyrius).
2. Sutvarkyk šaltinio lentelę pagal patarimą viršuje.
3. Įrašyk vieną paieškos formulę ir nutempk ją žemyn.
4. Įsirašyk į kalendorių 10 min. patikrą po savaitės: ar formulė vis dar grąžina teisingus duomenis, kai lentelė pasipildė.

Dažni klausimai

Kuo XLOOKUP geresnė už VLOOKUP?

XLOOKUP pagal nutylėjimą ieško tikslaus atitikmens, gali grąžinti reikšmę iš stulpelio kairėje ir nesulūžta, kai lentelėje įterpiamas naujas stulpelis. Be to, joje iš karto galima nurodyti, ką rodyti, kai reikšmė nerasta. VLOOKUP visa tai reikia apeiti papildomais argumentais ar funkcijomis.

Kodėl VLOOKUP grąžina #N/A, nors reikšmė lentelėje yra?

Dažniausiai dėl nematomų tarpų reikšmės pradžioje ar gale arba todėl, kad viename lape skaičius įrašytas kaip tekstas. Taip pat patikrink, ar ieškomas stulpelis yra pirmas paieškos srityje ir ar sritis užrakinta su $. Nuosekliai šie žingsniai aprašyti skyriuje „Kodėl paieška grąžina #N/A“.

Ar XLOOKUP yra senesnėse Excel versijose?

Ne. XLOOKUP yra Microsoft 365, Excel 2021, Excel 2024 ir Excel internete, bet jos nėra Excel 2019 ir 2016. Jei failą atidarys žmonės su senesnėmis versijomis, naudok INDEX+MATCH – ji veikia visur. Google Sheets XLOOKUP taip pat turi.

Šaltiniai

• Microsoft. XLOOKUP funkcija: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Microsoft. VLOOKUP funkcija: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
• Google. Google Sheets funkcijų sąrašas: https://support.google.com/docs/table/25273