VLOOKUP osjetljiv na velika i mala slova - Excel i Google tablice

Preuzmite Primjer radne knjige

Preuzmite primjer radne knjige

VLOOKUP koji razlikuje velika i mala slova - Excel

Ovaj će vodič pokazati kako izvesti VLOOKUP osjetljiv na velika i mala slova u Excelu pomoću dvije različite metode.

Metoda 1 - VLOOKUP osjetljiv na velika i mala slova s ​​pomoćnim stupcem

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

Funkcija VLOOKUP

Funkcija VLOOKUP koristi se za traženje približnog ili točnog podudaranja vrijednosti u krajnjem lijevom stupcu raspona i vraća odgovarajuću vrijednost iz drugog stupca. Prema zadanim postavkama, VLOOKUP će raditi samo za vrijednosti koje ne razlikuju velika i mala slova:

VLOOKUP osjetljiv na velika i mala slova

Kombiniranjem VLOOKUP, EXACT, MAX i ROW, možemo stvoriti formulu VLOOKUP koja razlikuje velika i mala slova koja vraća odgovarajuću vrijednost za naš VLOOKUP osjetljiv na velika i mala slova. Prođimo kroz primjer.

Imamo popis artikala i odgovarajuće cijene (imajte na umu da je ID stavke jedinstven za velika i mala slova):

Pretpostavimo da se od nas traži da dobijemo cijenu za artikl koristeći njegov ID stavke na sljedeći način:

Da bismo to postigli, prvo moramo stvoriti pomoćni stupac pomoću ROW:

= ROW () kliknite i povucite (ili dvaput kliknite) za unaprijed popunjavanje svih redaka u rasponu

Zatim kombinirajte VLOOKUP, MAX, EXACT i ROW u formuli ovako:

= 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 funkcionira formula?

1. Funkcija EXACT provjerava ID stavke u E2 (vrijednost pretraživanja) u odnosu na vrijednosti u B2: B7 (raspon pretraživanja) i vraća niz TRUE gdje postoji točno podudaranje ili FLASE u nizu {FLASE, FLASE, FLASE, FLASE, FLASE, ISTINITO}.
2. Taj se niz zatim množi s nizom ROW {2, 3, 4, 5, 6, 7} (imajte na umu da se to podudara s našim pomoćnim stupcem).
3. MAX funkcija vraća maksimalnu vrijednost iz rezultirajućeg niza {0,0,0,0,0,7}, što je 7 u našem primjeru.
4. Zatim koristimo rezultat kao vrijednost traženja u VLOOKUP -u i odabiremo pomoćni stupac kao raspon pretraživanja. U našem primjeru formula vraća odgovarajuću vrijednost od 16,00 USD.

Metoda 2 - VLOOKUP osjetljiv na velika i mala slova s ​​"virtualnim" stupcem pomoćnika

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

Ova metoda koristi istu logiku kao i prva metoda, ali uklanja potrebu za stvaranjem pomoćnog stupca i umjesto toga koristi CHOOSE i ROW za stvaranje "virtualnog" stupca pomoćnika na sljedeći način:

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

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

Kako funkcionira formula?

1. Prvi dio formule radi na isti način kao i prva metoda.
2. Kombinirajući CHOOSE i ROW vraća se niz s dva stupca, jedan za broj retka, a drugi za cijenu. Niz je odvojen točkom -zarezom kako bi predstavljao sljedeći redak i zarezom za sljedeći stupac na sljedeći način: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
3. Zatim možemo koristiti rezultat iz prvog dijela formule u VLOOKUP -u kako bismo pronašli odgovarajuću vrijednost iz našeg niza CHOOSE i ROW.

VLOOKUP osjetljiv na velika i mala slova u Google tablicama

Svi gornji primjeri rade potpuno isto u Google tablicama kao i u Excelu.