VLOOKUP, občutljiv na velike in male črke - Excel in Google Preglednice

Prenesite primer delovnega zvezka

Prenesite primer delovnega zvezka

Velike in male črke VLOOKUP - Excel

Ta vadnica bo pokazala, kako izvesti VLOOKUP, ki razlikuje med velikimi in malimi črkami v Excelu, z dvema različnima metodama.

Metoda 1 - VLOOKUP, občutljiv na velike in male črke, s pomožnim stolpcem

= VLOOKUP (MAX (TOČNO (E2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)

Funkcija VLOOKUP

Funkcija VLOOKUP se uporablja za iskanje približnega ali natančnega ujemanja vrednosti v skrajnem levem stolpcu obsega in vrne ustrezno vrednost iz drugega stolpca. VLOOKUP bo privzeto deloval le za vrednosti, ki ne razlikujejo med velikimi in malimi črkami:

VLOOKUP, občutljiv na velike in male črke

S kombinacijo VLOOKUP, EXACT, MAX in ROW lahko ustvarimo formulo VLOOKUP, ki razlikuje med velikimi in malimi črkami, ki vrne ustrezno vrednost za naš VLOOKUP, ki razlikuje med velikimi in malimi črkami. Pojdimo skozi primer.

Imamo seznam artiklov in njihovih ustreznih cen (upoštevajte, da je ID elementa edinstven za velike in male črke):

Predvidevamo, da za izdelek dobimo ceno z ID -jem artikla:

Da bi to dosegli, moramo najprej ustvariti pomožni stolpec z uporabo vrstice ROW:

= Kliknite ROW () in povlecite (ali dvokliknite), da vnaprej izpolnite vse vrstice v obsegu

Nato združite VLOOKUP, MAX, EXACT in ROW v formulo tako:

= VLOOKUP (MAX (TOČNO (,)*(ROW ())), ,, 0)

= VLOOKUP (MAX (TOČNO (E2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)

Kako deluje formula?

1. Funkcija EXACT preveri ID elementa v E2 (iskalna vrednost) glede na vrednosti v B2: B7 (iskalno območje) in vrne polje TRUE, če obstaja natančno ujemanje ali FLASE v nizu {FLASE, FLASE, FLASE, FLASE, FLASE, TRUE}.
2. Ta matrika se nato pomnoži z nizom ROW {2, 3, 4, 5, 6, 7} (upoštevajte, da se to ujema z našim stolpcem pomočnikom).
3. Funkcija MAX vrne največjo vrednost iz nastale matrike {0,0,0,0,0,7}, kar je v našem primeru 7.
4. Nato uporabimo rezultat kot našo iskalno vrednost v VLOOKUP -u in izberemo naš stolpec pomočnik kot obseg iskanja. V našem primeru formula vrne ujemajočo se vrednost 16,00 USD.

Metoda 2 - VLOOKUP, ki razlikuje med velikimi in malimi črkami z "virtualnim" pomožnim stolpcem

= VLOOKUP (MAX (TOČNO (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), IZBERI ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)

Ta metoda uporablja isto logiko kot prva metoda, vendar odpravlja potrebo po ustvarjanju stolpca pomočnikov in namesto tega uporablja CHOOSE in ROW za ustvarjanje »navideznega« stolpca pomočnika:

= VLOOKUP (MAX (TOČNO (,)*(ROW ())), IZBERI ({1,2}, ROW (),),, 0)

= VLOOKUP (MAX (TOČNO (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), IZBERI ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)

Kako deluje formula?

1. Prvi del formule deluje na enak način kot prva metoda.
2. Kombinacija IZBERI in RAVI vrne polje z dvema stolpcema, enega za številko vrstice in drugega za ceno. Polje je ločeno s podpičjem, ki predstavlja naslednjo vrstico, in vejico za naslednji stolpec: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
3. Nato lahko iz rezultata prvega dela formule v VLOOKUP -u poiščemo ustrezno vrednost iz našega niza CHOOSE in ROW.

VLOOKUP, občutljiv na velike in male črke, v Google Preglednicah

Vsi zgornji primeri delujejo popolnoma enako v Google Preglednicah kot v Excelu.