[TIP] Vlookup Multiple Display

[DR.ACCESS]

La unul din cursurile de Excel, o studenta ma intreaba daca Vlookup poate returna toate valorile corespondente celei cautate. Raspunsul: NU. Vlookup nu este o functie de tip array. Imi spune apoi ca un coleg a "facut" o formula care returneaza toate valorile corespondente. Evident, nu era Vlookup si ma indoiesc ca a fost creatia colegului atata timp cat solutia completa cu explicatii se afla aici.
Nu asta ar fi problema, ci faptul ca nu a inteles cum functioneaza formula, si de ce trebuie introdusa ca formula de tip array (cu CTRL+SHIFT+ENTER).
Acum, fie vorba intre noi, eu as fi folosit un filtru avansat intr-un mic macro, dar, daca se doreste utilizarea formulelor... am zis sa explic ce reprezinta acea formula, si de ce este necesara operatia pe vectori, poate sunt si altii interesati de raspuns.
In primul rand, iata despre ce formula este vorba:
=IF(ISERROR(INDEX($A$1:$B$7;SMALL(IF($A$1:$A$7=$A$10;ROW($A$1:$A$7));ROW(2:2));2));"";INDEX($A$1:$B$7;SMALL(IF($A$1:$A$7=$A$10;ROW($A$1:$A$7));ROW(2:2));2))
Inainte de toate ar trebui sa stim ce face fiecare dintre functii si pentru ce a fost introdusa in formula, ca sa putem urmari logica intregii expresii.
Functia IF(daca la cautare am eroare, atunci nu afiseaza nimic, altfel afiseaza rezultatul gasit)
Functia INDEX ne ajuta sa identificam o celula intr-o matrice. Aici a fost folosita varianta INDEX(matrice, nr.linie, nr.coloana)
matrice=domeniul $A$1:$B$7 din fisierul exemplu
Nr. linie= rezultatul functiei SMALL
Nr. coloana=2
Functia SMALL aplicata unui sir returneaza cea mai mica a n-a valoare (puteti face un "top" cu ea)
In formula de mai sus, returneaza numarul randului pe care se regaseste informatia asociata cuvantului pe care il cautam.
Functia IF($A$1:$A$7=$A$10;ROW($A$1:$A$7)) necesita introducerea ei de tip CSE( CTRL+SHIFT+ENTER), ca sa poata evalua un sir de conditii si sa returneze, de asemenea, un sir (sirul cu numarul de ordine al liniilor pe care se afla informatia cautata).
Desfasurat, functia noastra ar arata cam asa:
IF({A1=A10,A2=A10,A3=A10)},ROW({A1,A2,A3}) (Compara fiecare celula de pe prima coloana cu cea din A10 si daca sunt adevarate ne returneaza din sirul de nr. de rand, pe cele aflate in dreptul valorii true. De fapt returneaza tot un sir in care pentru conditiile false avem corespondent tot false, iar pentru true obtinem nr. randului
Rezultatul IF-ului de mai sus fiind tot un vector de tipul: {false,2,false,4,5,false,7}
Acest vector, va fi primul argument al functiei small, care are pe a doua pozitie functia ce returneaza pentru prima aplicare, numarul 1 (se utilizeaza functia row(1:1) doar pentru a putea folosi o incrementare de tip autonumber). La copierea formulei cu un rand mai jos, functia row se transforma in ROW(2;2) care returneaza 2. Aceste enumerari au urmatoarea semnificatie, ca argument al functiei Small: "Da-mi informatia aflata pe randul cu cel mai mic numar( prima aparitie), apoi pe al doilea (a doua aparitie), etc"

Mi-a placut formula, dar, repet, din principiu (nu poti sti cam cate aparitii ai si cat trebuie sa "tragi" ca sa-ti returneze toate informatiile) prefer acele unelte care returneaza exact ce vrei, cum vrei, unde vrei, fara un grad atat de mare de "neprevazut". Si, totusi, mi-a placut sa studiez aceasta formula. E intr-adevar o creatie de exceptie, ceva in stilul lui gecs

Vlookup Multiple Display.xlsx