Excel alternative VLOOKUP (kazalo)

  • Namesto VLOOKUP
  • Primeri funkcij INDEX in MATCH z omejitvijo VLOOKUP
  • Kako uporabljati VLOOKUP alternative v Excelu?

Namesto VLOOKUP

Excel je poln formul, vrstic in stolpcev. VLOOKUP je ena od pomembnih formul v excelu. Formule Alternativa VLOOKUP v excelu ne poznate kot odličen uporabnik, vendar formulo dobro poznate.

Če pa menite, da je poznavanje formule VLOOKUP najboljša stvar v excelu, morate nemudoma spremeniti svojo misel (po mojem mnenju sem vedel, da je VLOOKUP najboljša stvar v excelu že na začetku moje kariere).

Ko začnete napredovati na napredno raven, boste začeli čutiti omejitve formule VLOOKUP. Ne vem, ali se zavedate ali ne, da vam lahko VLOOKUP podatke prinese samo od leve proti desni. Obstajajo primeri, ko sem želel pridobiti podatke od desne proti levi, vendar mi VLOOKUP v teh primerih ni pomagal. Bil sem prisiljen preurediti svoje podatke, da bi delo opravil v korist mene.

Vendar to še ni konec, saj imamo na voljo le malo drugih možnosti, da presežemo omejitve VLOOKUP-a.

Primeri funkcij INDEX in MATCH z omejitvijo VLOOKUP

Ta dva sta alternativa VLOOKUP v excelu. Za alternativo VLOOKUP si moramo zapomniti številko stolpca v primeru številnih stolpcev, vendar v teh formulah ne potrebujemo ničesar, preprosto moramo razumeti logiko formule.

Formula INDEX:

  • Niz: Kakšna je vrednost matrike, ki jo želite pridobiti?
  • Številka vrstice: iz katere vrstice želite pridobiti podatke.
  • Številka stolpca: iz katere številke stolpca želite pridobiti podatke.

Funkcija indeksa - Primer # 1

Oglejmo si nekaj primerov nadomestnih datotek v Excelu za VLOOKUP.

Te alternative za predlogo VLOOKUP Excel lahko prenesete tukaj - Alternativa predlogi VLOOKUP Excel

Imam preprosto prodajno tabelo.

Podatke lahko prenašam z uporabo VLOOKUP, tukaj pa bom uporabil formulo INDEX. Odprite formulo v celici E2.

Zdaj izberite zahtevane vrednosti stolpca z rezultati. Tu so moje zahtevane vrednosti od B2 do B5. Izbral bom obseg kot B2: B5 in ga zaklenil.

Zdaj moram omeniti številko vrstice. V regiji Jug je številka vrstic 2.

Zdaj moram omeniti iz katerega stolpca poskušamo pridobiti podatke. V tem primeru je prvi stolpec, ker smo v argumentu matrike izbrali samo en stolpec.

INDEX mi bo prinesel podatke za JUŽNO regijo.

Vau !!! INDEX je zame naredil trik.

Vendar se bo težava začela zdaj. Ko kopiram in prilepim formulo v spodnje celice, moram spremeniti številko vrstice, ko se celica spreminja. V trenutni številki vrstice celice je 1, ko se premaknem v naslednjo celico, mora biti 2. INDEX ne bo samodejno sprejel dodatne vrstice.

To nalogo lahko avtomatiziramo s funkcijo MATCH.

Funkcija MATCH - Primer # 2

Vrnil bo številko vrstice izbrane vrednosti v priloženem območju.

  • Vrednost iskanja: Katero vrednost iskanja iščete številko vrstice?
  • Lookup Array: Iz katerega obsega poskušate najti številko vrstice
  • Vrsta ujemanja: kakšen rezultat želite iskati. Ne glede na to, ali gre za približno ujemanje pod vrednostjo iskanja (1) ali natančno ujemanje (0) ali približno ujemanje nad vrednostjo iskanja (-1).

Zdaj bom uporabil iste podatke iz zgornjega primera. Poskušal bom pogledati številko vrstice za vsako regijo.

Odprite formulo v celici E2 in izberite vrednost iskanja kot vrednost celice D2.

Zdaj je niz za iskanje iz katere tabele želite izvedeti številko vrstice. Naša matrična tabela je od A2 do A5.

Vrsta ujemanja mora biti natančna, zato bom kot argument navedel 0.

Za vsako regijo sem dobil številke vrstic.

Formulo povlecite in spustite v preostale celice.

Omejitev VLOOKUP - primer # 3

VLOOKUP vedno deluje od leve proti desni. Zdaj pa poglejte spodnje podatke.

Moram izvleči podatke o plačah iz razpona A1 do D21. To lahko storim z uporabo funkcije VLOOKUP.

Rezultat bo naslednji:

Formulo povlecite in spustite v preostale celice.

VLOOKUP trenutno dela namesto mene. Toda zdaj poglejte spodnjo strukturo podatkov.

Na podlagi ID-ja EMP moram izvleči podatke, vendar je ID EMP tam na pravem koncu dejanskih podatkov. VLOOKUP v teh primerih začne zbledeti.

Tako imamo odlično izbiro VLOOKUP, imenovano INDEX & MATCH funkcija.

Kako uporabljati alternativo VLOOKUP v Excelu?

Alternativa VLOOKUP je zelo preprosta in enostavna. Razumejmo, kako uporabiti alternative za VLOOKUP v excelu z nekaj primeri.

Funkcija INDEX + MATCH kot alternativa VLOOKUP - Primer # 1

Zdaj vzemite isto tabelo iz zgornjega primera.

Odprite formulo INDEX v celici H2.

Niz ni nič drugega kot rezultat, ki ga poskušate najti. Tu poskušamo najti vrednost plače, zato izberite celoten razpon plač.

Zdaj moramo omeniti številko vrstice. V primeru funkcije MATCH smo se naučili, da nam lahko funkcija MATCH da številko vrstice. Torej bom v funkciji INDEX uporabil funkcijo MATCH.

LOOKUP pomeni, na podlagi česa poskušam najti številko vrstice. Tukaj za ID EMP poskušam najti številko vrstice, zato je vrednost iskanja EMP ID.

Zdaj poiščite matriko ni nič, ampak je stolpec z vrednostjo glavnega iskanja tabele.

Zdaj naj bo končna vrsta ujemanja, zato navedite vrednost 0.

Rezultat bo naslednji:

Yessssss !!!!!!! INDEX + MATCH deluje točno tako kot VLOOKUP, vendar lahko podatke pridobiva od koder koli in kjer koli.

Formulo povlecite in spustite v preostale celice.

LOOKUP funkcija kot alternativa VLOOKUP - primer # 2

LOOKUP je vgrajena funkcija v excelu.

  • Vrednost iskanja: je vrednost, na podlagi katere iščete rezultat.
  • Vektor iskanja: To je območje vrednosti iskanja v glavni tabeli.
  • Vektorica rezultata: To je stolpec z rezultati v glavni tabeli.

Vzemite enake podatke iz prejšnjega primera.

Odprite formulo LOOKUP v celici H2 in izberite vrednost iskanja kot G2.

Zdaj moramo v glavni tabeli izbrati iskalni vektor kot D2 do D21.

Zdaj bi moral biti končni rezultat stolpec rezultatov, ki ga poskušamo izvleči, to je B2 do B21.

Po vnosu formule pritisnite tipko enter in dobili bomo rezultat.

Formulo povlecite in spustite v preostale celice.

Spomniti se na Excel alternative VLOOKUP

  • VLOOKUP lahko deluje le od leve proti desni.
  • Funkcija MATCH bo vrnila številko vrstice.
  • Funkcije INDEX + MATCH in LOOKUP ne zahtevata številke stolpcev, za razliko od VLOOKUP pa zahteva, da številka stolpcev pridobi podatke, čeprav je že izbran želen stolpec.
  • Struktura podatkov ni pomembna za funkcije INDEX + MATCH in LOOKUP. Toda za VLOOKUP je pomembna struktura podatkov.

Priporočeni članki

To je vodnik za alternative VLOOKUP v Excelu. Tukaj razpravljamo o primerih nadomestnih nadomestnih datotek VLOOKUP, kot so INDEX, MATCH in LOOKUP, skupaj s praktičnimi primeri in naloženo predlogo excela. Ogledate si lahko tudi druge naše predlagane članke -

  1. VLOOKUP funkcija v Excelu
  2. Vadnice za funkcijo MATCH v Excelu
  3. Kako uporabljati LOOKUP v Excelu?
  4. IFERROR z VLOOKUP v Excelu
  5. Primeri maklov Excela

Kategorija:

Excel Nasveti