Jei turite Microsoft 365 ar Excel 2021, dažniausiai paprasčiau naudoti XLOOKUP. INDEX ir MATCH vis tiek verta mokėti dėl dviejų priežasčių: jos veikia senesnėse Excel versijose, kurios dar naudojamos daugelyje įmonių, ir jas rasite beveik kiekviename paveldėtame faile.
Kaip veikia kiekviena funkcija atskirai
MATCH grąžina vietą sąraše. Jei prekės kodas „P-104“ yra ketvirtoje D2:D500 intervalo eilutėje:
=MATCH("P-104";D2:D500;0)
grąžins 4. Paskutinis argumentas 0 reiškia tikslų atitikimą. Jį praleidus, MATCH ieško apytiksliai ir nerūšiuotame sąraše grąžina neteisingą eilutę, todėl rašykite jį visada.
INDEX grąžina reikšmę pagal vietą:
=INDEX(B2:B500;4)
grąžins ketvirtą B2:B500 intervalo reikšmę.
Kartu: INDEX ir MATCH
Sujungus, MATCH suranda eilutę, o INDEX paima reikšmę iš reikiamo stulpelio:
=INDEX(Prekės!B:B;MATCH(G2;Prekės!D:D;0))
Čia G2 yra ieškomas kodas, D stulpelyje ieškoma, o B stulpelyje yra grąžinama reikšmė, pavyzdžiui, pavadinimas. Atkreipkite dėmesį: B yra kairiau už D. VLOOKUP to padaryti negali, nes visada ieško pirmame stulpelyje ir grąžina reikšmę į dešinę.
Abu intervalai turi prasidėti toje pačioje eilutėje. Jei vienas yra B2:B500, o kitas D1:D500, rezultatas bus pasislinkęs per vieną eilutę ir klaida nesimatys iš karto.
Kodėl ne VLOOKUP
| VLOOKUP | INDEX ir MATCH | |
|---|---|---|
| Paieška į kairę | Negalima | Galima |
| Įterpus stulpelį | Gali grąžinti ne tą stulpelį | Veikia toliau |
| Paieška pagal kelias sąlygas | Tik su pagalbiniu stulpeliu | Galima viena formule |
| Excel versijos | Visos | Visos |
Paieška pagal dvi sąlygas
Kaina priklauso ir nuo prekės, ir nuo tiekėjo. Prekė yra A stulpelyje, tiekėjas B, kaina C. Ieškome prekės iš G2 ir tiekėjo iš H2:
=INDEX(C2:C500;MATCH(1;(A2:A500=G2)*(B2:B500=H2);0))
Kiekviena sąlyga sukuria TRUE ir FALSE sąrašą, o daugyba juos paverčia vienetais ir nuliais. Vienetas lieka tik ten, kur abi sąlygos teisingos, ir MATCH suranda pirmą tokią eilutę.
Microsoft 365 ir Excel 2021 versijose formulė veikia įprastai. Excel 2019 ir senesnėse ją reikia patvirtinti Ctrl+Shift+Enter, kitaip bus rodoma klaida. Tokioje formulėje nenaudokite viso stulpelio A:A, nes ji tikrins milijoną eilučių ir failas sulėtės.
Eilutės ir stulpelio sankirta
Kai turite kainų lentelę, kurioje eilutėse prekės, o stulpeliuose mėnesiai, INDEX gali gauti ir eilutės, ir stulpelio numerį:
=INDEX(B2:M50;MATCH(G2;A2:A50;0);MATCH(H1;B1:M1;0))
Pirmas MATCH suranda prekės eilutę, antras mėnesio stulpelį. Tokia formulė pravers tarifų, kainoraščių ar darbo grafikų lentelėse.
Kai reikšmė nerandama
Jei reikšmės nėra, formulė grąžina #N/A. Vietoje klaidos galima parodyti tekstą:
=IFNA(INDEX(B:B;MATCH(G2;D:D;0));"Nerasta")
IFNA geriau nei IFERROR, nes paslepia tik tą atvejį, kai reikšmė nerasta. IFERROR paslėptų ir kitas klaidas, pavyzdžiui, neteisingą nuorodą, ir jų nepastebėtumėte.
Jei #N/A rodoma, nors reikšmė tikrai yra, dažniausiai skiriasi tarpai arba vienur kodas yra skaičius, o kitur tekstas. Kaip tai pataisyti, aprašiau straipsniuose kaip sutvarkyti netvarkingą lentelę ir skaičiai ir datos kaip tekstas.
Google Sheets
INDEX ir MATCH Google Sheets veikia taip pat. Paieškai pagal kelias sąlygas formulę apgaubkite ARRAYFORMULA arba naudokite FILTER, kuri čia dažnai yra paprastesnė.