Descărcați exemplul de registru de lucru
VLOOKUP sensibil la majuscule - Excel
Acest tutorial va arăta cum să efectuați un VLOOKUP sensibil la majuscule și minuscule în Excel folosind două metode diferite.
Metoda 1 - VLOOKUP sensibil la majuscule și minuscule cu coloană de ajutor
= VLOOKUP (MAX (EXACT (E2, $ B $ 2: $ B $ 7)) * (ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Funcția VLOOKUP
Funcția VLOOKUP este utilizată pentru a căuta o potrivire aproximativă sau exactă pentru o valoare din coloana din stânga a unui interval și returnează valoarea corespunzătoare dintr-o altă coloană. În mod implicit, VLOOKUP va funcționa numai pentru valori care nu sunt sensibile la majuscule și minuscule:
VLOOKUP sensibil la majuscule
Combinând VLOOKUP, EXACT, MAX și ROW, putem crea o formulă VLOOKUP sensibilă la majuscule și minuscule care returnează valoarea corespunzătoare pentru VLOOKUP nostru sensibil la majuscule. Să parcurgem un exemplu.
Avem o listă de articole și prețurile corespunzătoare ale acestora (observați că ID-ul articolului este diferențiat între majuscule și minuscule):
Presupunem că suntem rugați să obținem prețul pentru un articol folosind ID-ul articolului său astfel:
Pentru a realiza acest lucru, trebuie să creăm o coloană de ajutor folosind ROW:
= ROW () faceți clic și trageți (sau faceți dublu clic) pentru a umple toate rândurile din interval
Apoi, combinați VLOOKUP, MAX, EXACT și ROW într-o formulă similară:
= VLOOKUP (MAX (EXACT (,) * (ROW ())), ,, 0)
= VLOOKUP (MAX (EXACT (E2, $ B $ 2: $ B $ 7)) * (ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Cum funcționează formula?
- Funcția EXACT verifică ID-ul articolului în E2 (valoarea de căutare) față de valorile din B2: B7 (intervalul de căutare) și returnează o matrice de ADEVĂRAT unde există o potrivire exactă sau FLASE într-o matrice {FLASE, FLASE, FLASE, FLASE, FLASE, ADEVĂRAT}.
- Această matrice este apoi înmulțită cu matricea ROW {2, 3, 4, 5, 6, 7} (rețineți că aceasta se potrivește cu coloana noastră de ajutor).
- Funcția MAX returnează valoarea maximă din tabloul rezultat {0,0,0,0,0,7}, care este 7 în exemplul nostru.
- Apoi, folosim rezultatul ca valoare de căutare într-un VLOOKUP și alegem coloana de asistență ca interval de căutare. În exemplul nostru, formula returnează valoarea potrivită de 16,00 USD.
Metoda 2 - VLOOKUP sensibil la majuscule și minuscule cu coloană de asistență „virtuală”
= VLOOKUP (MAX (EXACT (D2, $ B $ 2: $ B $ 7)) * (ROW ($ B $ 2: $ B $ 7))), ALEGE ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)
Această metodă utilizează aceeași logică ca prima metodă, dar elimină necesitatea de a crea o coloană de ajutor și în schimb folosește CHOOSE și ROW pentru a crea o coloană de ajutor „virtuală” astfel:
= VLOOKUP (MAX (EXACT (,) * (ROW ())), ALEGE ({1,2}, ROW (),),, 0)
= VLOOKUP (MAX (EXACT (D2, $ B $ 2: $ B $ 7)) * (ROW ($ B $ 2: $ B $ 7))), ALEGE ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)
Cum funcționează formula?
- Prima parte a formulei funcționează la fel ca prima metodă.
- Combinând CHOOSE și ROW se returnează o matrice cu două coloane, una pentru numărul rândului și alta pentru preț. Matricea este separată printr-un punct și virgulă pentru a reprezenta următorul rând și o virgulă pentru următoarea coloană astfel: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Putem folosi rezultatul din prima parte a formulei într-o VLOOKUP pentru a găsi valoarea corespunzătoare din matricea noastră ALEGE și RÂND.
VLOOKUP cu sensibilitate la majuscule și minuscule în Foi de calcul Google
Toate exemplele de mai sus funcționează exact la fel în Foi de calcul Google ca în Excel.
