VLOOKUP su MATCH | Sukurkite lanksčią formulę naudodami VLOOKUP MATCH

„Vlookup“ formulė veikia tik tada, kai lentelės masyvas formulėje nesikeičia, tačiau jei į lentelę įterptas naujas stulpelis arba ištrintas stulpelis, formulė pateikia neteisingą rezultatą arba atspindi klaidą, kad formulėje nebūtų klaidų tokiose dinaminėse situacijose mes naudojame atitikimo funkciją, kad iš tikrųjų atitiktų duomenų rodyklę ir grąžintume faktinį rezultatą.

Sujunkite VLOOKUP su „Match“

„Vlookup“ formulė yra dažniausiai naudojama funkcija, naudojama ieškoti ir grąžinti tą pačią reikšmę nurodytame stulpelio rodyklėje arba vertę iš kito stulpelio indekso, atsižvelgiant į suderintą pirmojo stulpelio vertę. Pagrindinis iššūkis, su kuriuo susiduriama naudojant „vlookup“, yra tai, kad nurodomas stulpelių rodiklis yra statinis ir neturi dinaminės funkcijos. Ypač tada, kai dirbate pagal kelis kriterijus, pagal kuriuos reikia rankiniu būdu pakeisti nuorodos stulpelio rodyklę. Tokiu būdu šis poreikis yra tenkinamas naudojant „MATCH“ formulę, kad geriau suprastumėte arba valdytumėte VLOOKUP formulėje dažnai besikeičiančią stulpelio rodyklę.

„VLookup“ ir „Match Formula“

# 1 - „VLOOKUP“ formulė

Funkcijos VLOOKUP formulė „Excel“

Čia visi įvesti argumentai yra privalomi.

  • Paieškos vertė - čia reikia įvesti nuorodos langelį arba tekstą su dvigubomis kabutėmis, kad būtų galima identifikuoti stulpelių diapazone.
  • Lentelių masyvas -   šiam argumentui reikia įvesti lentelės diapazoną, kur reikia ieškoti „Lookup_value“, o gauti duomenys yra tam tikrame stulpelių diapazone.
  • Col_index_num - šiame argumente reikia įvesti stulpelio indekso numerį arba stulpelio skaičių iš pirmojo pamatinio stulpelio, iš kurio reikia ištraukti atitinkamą vertę iš tos pačios padėties, kaip ir pirmame stulpelyje ieškota vertė.
  • [Range_lookup] - šis argumentas duos dvi parinktis.
  • TRUE - apytikslė atitiktis: - argumentą galima įvesti kaip TRUE arba skaitmeninį „1“, kuris pateikia apytikslę atitiktį, atitinkančią pamatinį stulpelį arba pirmąjį stulpelį. Be to, pirmojo lentelės masyvo stulpelio vertės turi būti rūšiuojamos didėjimo tvarka.
  • FALSE - Tiksli atitiktis: - Čia įvestinas argumentas gali būti FALSE arba skaitmeninis „0“. Ši parinktis pateiks tik tikslios vertės atitiktį, kurią reikia nustatyti iš pozicijos pirmojo stulpelio diapazone. Nepavykus ieškoti vertės iš pirmojo stulpelio, bus pateiktas klaidos pranešimas „# N / A“.

# 2 - rungtynių formulė

Funkcija „Atitikimas“ grąžina nurodytos lentelės masyvui įvestos vertės langelio padėtį.

Visi sintaksės argumentai yra privalomi.

  • „Lookup_value“ - čia įvestas argumentas gali būti vertės langelio nuoroda arba teksto eilutė su dvigubomis kabutėmis, kurių langelio poziciją reikia ištraukti.
  • „Lookup_array“ - reikia įvesti lentelės masyvo diapazoną, kurio vertę ar langelio turinį norima nustatyti.
  • [atitikties tipas] - šiame argumente pateikiamos trys parinktys, kaip paaiškinta toliau.
  • „1-Less than“ - čia įvedamas argumentas yra skaitinis „1“, kuris grąžins reikšmę, kuri yra mažesnė arba lygi paieškos vertei. Taip pat paieškos masyvas turi būti rūšiuojamas didėjimo tvarka.
  • „0-Tiksli atitiktis“ - čia įvedamas argumentas turėtų būti skaitmeninis „0“. Ši parinktis grąžins tikslią suderintos paieškos vertės padėtį. Tačiau paieškos masyvas gali būti bet kokia tvarka.
  • „-1-didesnis nei“ -  įvedamas argumentas turėtų būti skaitinis „-1“. Trečioji parinktis nustato mažiausią vertę, didesnę arba lygią paieškos vertei. Čia paieškos masyvo užsakymas turi būti pateikiamas mažėjančia tvarka.

# 3 - VLOOKUP su „MATCH Formula“

= VLOOKUP (look_value, table_array, MATCH (look__value, look_array, [match_type]), [range lookup])

Kaip naudoti „VLOOKUP“ su „Match Formula“ programoje „Excel“?

Žemiau pateiktas pavyzdys padės suprasti, kaip „vlookup“ ir „match“ formulė veikia sudedant.

Šį „VLookup with Match Excel“ šabloną galite atsisiųsti čia - „VLookup with Match Excel“ šablonas

Apsvarstykite toliau pateiktą duomenų lentelę, kurioje aprašytos perkamos transporto priemonės specifikacijos.

Norėdami sužinoti, kokia aiški funkcija yra „vlookup“ ir „match“ funkcija, supraskime, kaip veikia individuali formulė, ir tada pasieksime „vlookup“ atitikties rezultatus, kai ji bus sudaryta.

1 žingsnis - pritaikykime „vlookup“ formulę individualiu lygiu, kad pasiektume rezultatą.

Rezultatas parodytas žemiau:

Čia paieškos vertė nurodoma $ B9, kuris yra „E“ modelis, o paieškos masyvas pateikiamas kaip duomenų lentelės diapazonas su absoliučia verte „$“, stulpelio indeksas nurodomas „4“ stulpelyje, kuris yra stulpelyje „Tipas“ ir diapazono paieškai pateikiama tiksli atitiktis.

Taigi, norint grąžinti stulpelio „Kuras“ vertę, taikoma ši formulė .

Rezultatas parodytas žemiau:

Čia paieškos vertė su absoliučia eilute „$“, pritaikyta paieškos vertei ir lookup_array, padeda nustatyti referencinį langelį, net jei formulė nukopijuojama į kitą langelį. Stulpelyje „Kuras“ turime pakeisti stulpelio rodyklę į „5“, nes keičiasi reikšmė, iš kurios reikia gauti duomenis.

2 žingsnis -  Dabar pritaikykime atitikties formulę, kad gautume nurodytos paieškos vertės poziciją.

Rezultatas parodytas žemiau:

Kaip matyti iš pirmiau pateikto ekrano, čia mes bandome gauti stulpelio padėtį iš lentelės masyvo. Šiuo atveju traukiamas stulpelio numeris vadinamas langeliu C8, kuris yra stulpelis „Tipas“, o ieškomas paieškos diapazonas nurodomas kaip stulpelių antraščių diapazonas, o atitikties tipui tiksli atitiktis turi būti „ 0 “.

Taigi šioje lentelėje bus pateiktas norimas rezultatas stulpelio „Kuras“ pozicijoms.

Dabar čia ieškomas stulpelis yra langelis D8, o norimas stulpelio indeksas yra „5“.

3 žingsnis - Dabar „Vlookup“ funkcijoje bus naudojama atitikties formulė, kad gautumėte vertę iš nustatytos stulpelio padėties.

Rezultatas parodytas žemiau:

Pirmiau pateiktoje formulėje atitikties funkcija dedama vietoje „vlookup“ funkcijos stulpelio indekso parametro. Čia atitikties funkcija nustatys paieškos vertės nuorodos langelį „C8“ ir grąžins stulpelio numerį per pateiktą lentelės masyvą. Ši stulpelio padėtis bus naudojama kaip įvestis į „vlookup“ funkcijos stulpelio indekso argumentą. Kas savo ruožtu padės „vlookup“ nustatyti vertę, kurią reikia grąžinti iš gauto stulpelio indekso numerio?

Stulpelyje „Kuras“ taip pat pritaikėme „vlookup“ su atitikties formule.

Rezultatas parodytas žemiau:

Taigi šią kombinavimo funkciją galime pritaikyti ir kitose skiltyse „Tipas“ ir „Kuras“.

Ką reikia atsiminti

  • VLOOKUP gali būti taikomas paieškos reikšmėms tik priekinėje kairėje pusėje. Bet kurios reikšmės, kurių reikia ieškoti dešinėje duomenų lentelės pusėje, grąžins klaidos reikšmę „# N / A“.
  • Antrame argumente įvestas „table_array“ diapazonas turėtų būti absoliutus langelio nuoroda „$“. Tai išlaikys fiksuotą lentelės masyvo diapazoną taikant paieškos formulę kitoms ląstelėms, arba lentelės masyvo diapazono nuorodos ląstelės bus perkeltos į kitą langelį nuoroda.
  • Paieškos reikšmėje įvesta vertė neturėtų būti mažesnė už mažiausią reikšmę pirmajame lentelės masyvo stulpelyje, kitaip funkcija grąžins klaidos reikšmę „# N / A“.
  • Prieš taikydami apytikslį atitikimą „TRUE“ arba „1“ paskutiniame argumente, visada nepamirškite rūšiuoti lentelių masyvo didėjimo tvarka.
  • Funkcija „Match“ grąžina tik vertės padėtį „vlookup“ lentelės masyve ir negrąžina vertės.
  • Jei funkcija „Rungtynių funkcija“ negali nustatyti paieškos vertės pozicijos lentelės masyve, formulė grąžina klaidos reikšmę „# N / A“.
  • „Vlookup“ ir „match“ funkcijos neskiria didžiųjų ir mažųjų raidžių, kai paieškos reikšmė suderinama su atitinkama teksto verte lentelės masyve.

$config[zx-auto] not found$config[zx-overlay] not found