✅ Kako učiniti Excel VLOOKUP osjetljivim na velika i mala slova

Sadržaj

Učiniti VLOOKUP osjetljivim na velika i mala slova

Prema zadanim postavkama, vrijednost pretraživanja u funkciji VLOOKUP ne razlikuje velika i mala slova. Na primjer, ako je vaša vrijednost pretraživanja MATT, mat ili Matt, sve je to isto za funkciju VLOOKUP. Vratit će prvu odgovarajuću vrijednost neovisno o slučaju.

Učiniti VLOOKUP osjetljivim na velika i mala slova

Pretpostavimo da imate podatke kao što je prikazano u nastavku:

Kao što vidite, postoje tri ćelije s istim imenom (A2, A4 i A5), ali s različitim slovima. S desne strane (u E2: F4) imamo tri imena (Matt, MATT i mat) zajedno s njihovim rezultatima u matematici.

Excel VLOOKUP funkcija nije opremljena za rukovanje vrijednostima pretraživanja osjetljivim na velika i mala slova. U ovom gornjem primjeru, bez obzira na to koja je vrijednost vrijednosti pretraživanja (Matt, MATT ili mat), uvijek će se vratiti 38 (što je prva podudarajuća vrijednost).

U ovom vodiču naučit ćete kako učiniti VLOOKUP osjetljivim na velika i mala slova:

Korištenje stupca pomoćnika.
Bez korištenja pomoćnog stupca i korištenja formule.

Učiniti VLOOKUP osjetljivim na velika i mala slova - pomoću kolone pomoćnika

Stupac pomoćnik može se koristiti za dobivanje jedinstvene vrijednosti pretraživanja za svaku stavku u nizu za pretraživanje. To pomaže u razlikovanju imena s različitim slovima.

Evo koraka za to:

Umetnite pomoćni stupac lijevo od stupca odakle želite dohvatiti podatke. U donjem primjeru morate umetnuti pomoćni stupac između stupaca A i C.
U pomoćni stupac unesite formulu = ROW (). U svaku ćeliju umetnut će broj retka.
Koristite sljedeću formulu u ćeliji F2 da biste dobili rezultat pretraživanja osjetljiv na velika i mala slova.
```
= VLOOKUP (MAX (TOČNO (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
```
Kopirajte ga zalijepite za preostale ćelije (F3 i F4).

Bilješka: Budući da je ovo formula niza, koristite Control + Shift + Enter umjesto samo enter.

Kako ovo radi?

Razložimo formulu da bismo razumjeli kako ona radi:

TOČNO (E2, $ A $ 2: $ A $ 9) - Ovaj dio uspoređuje vrijednost pretraživanja u E2 sa svim vrijednostima u A2: A9. Vraća niz TRUE/FALSEs gdje se TRUE vraća kada postoji točno podudaranje. U ovom slučaju, gdje je vrijednost u E2 Matt, vratila bi sljedeći niz:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
TOČNO (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - Ovaj dio množi niz TRUE/FALSE s brojem retka A2: A9. Gdje god postoji TRUE, to je daje broj retka, inače daje 0. U ovom slučaju vratilo bi se {2; 0; 0; 0; 0; 0; 0; 0}.
MAX (TOČNO (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))) - Ovaj dio vraća maksimalnu vrijednost iz niza brojeva. U ovom slučaju vratilo bi se 2 (što je broj retka gdje postoji točno podudaranje).
Sada jednostavno koristimo ovaj broj kao vrijednost pretraživanja i koristimo polje za pretraživanje kao B2: C9.

Napomena: Pomoćni stupac možete umetnuti bilo gdje u skupu podataka. Samo provjerite je li lijevo od stupca odakle želite dohvatiti podatke. Zatim morate prema tome prilagoditi broj stupca u funkciji VLOOKUP.

Sada, ako niste ljubitelj pomoćnog stupca, možete izvršiti i pretraživanje velikih i malih slova bez pomoćnog stupca.

Učiniti VLOOKUP osjetljivim na velika i mala slova - bez pomoćnog stupca

Čak i kad ne želite koristiti stupac pomoćnika, i dalje morate imati virtualni stupac pomoćnika. Ovaj virtualni stupac nije dio radnog lista, već je konstruiran unutar formule (kao što je prikazano u nastavku).

Evo formule koja će vam dati rezultat bez stupca pomoćnika:

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

Kako ovo radi?

Formula također koristi koncept pomoćnog stupca. Razlika je u tome što umjesto stavljanja stupca pomoćnika na radni list, smatrajte to virtualnim podacima pomoćnika koji su dio formule.

Evo dijela koji radi kao pomoćni podaci (označen narančastom bojom):

= VLOOKUP (MAX (TOČNO (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))),ODABERITE ({1,2}, ROW ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)

Dopustite mi da vam pokažem što mislim pod virtualnim podacima pomoćnika.

Na gornjoj ilustraciji, dok odabirem dio IZBOR formule i pritisnem F9, ona prikazuje rezultat koji bi dala formula IZABERI.

Rezultat je {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}

To je niz u kojem zarez predstavlja sljedeću ćeliju u istom retku, a točka -zarez predstavlja da su sljedeći podaci u sljedećem retku. Dakle, ova formula stvara 2 stupca podataka - jedan stupac ima broj retka, a jedan matematički rezultat.

Sada, kada koristite funkciju VLOOKUP, ona jednostavno traži vrijednost pretraživanja u prvom stupcu (ovih podataka iz virtualnog 2 stupca) i vraća odgovarajuću ocjenu. Ovdje je vrijednost pretraživanja broj koji dobivamo kombinacijom funkcije MAX i EXACT.

Preuzmite datoteku primjera