Formula generică
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
rezumat
Pentru a inversa un VLOOKUP - adică pentru a găsi valoarea de căutare originală utilizând un rezultat al formulei VLOOKUP - puteți utiliza o formulă complicată bazată pe funcția ALEGE, sau formule mai simple bazate pe INDEX și MATCH sau XLOOKUP așa cum se explică mai jos. În exemplul prezentat, formula din H10 este:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Cu această configurare, VLOOKUP găsește opțiunea asociată cu un cost de 3000 și returnează „C”.
Notă: acesta este un subiect mai avansat. Dacă tocmai începeți cu VLOOKUP, începeți aici.
Introducere
O limitare cheie a VLOOKUP este că poate căuta doar valori în dreapta. Cu alte cuvinte, coloana cu valori de căutare trebuie să fie în stânga valorilor pe care doriți să le extrageți cu VLOOKUP. Ca urmare, cu configurația standard, nu există nicio modalitate de a utiliza VLOOKUP pentru a „arăta la stânga” și a inversa căutarea originală.
Din punctul de vedere al VLOOKUP, putem vizualiza problema astfel:
Soluția explicată mai jos folosește funcția CHOOSE pentru a rearanja masa din VLOOKUP.
Explicaţie
Începând de la început, formula din H5 este o formulă normală de VLOOKUP:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Folosind G5 ca valoare de căutare („C”) și datele din B5: D8 ca matrice de tabele, VLOOKUP efectuează o căutare a valorilor din coloana B și returnează valoarea corespunzătoare din coloana 3 (coloana D), 3000. Notificare zero (0) este furnizat ca ultim argument pentru a forța o potrivire exactă.
Formula din G10 extrage pur și simplu rezultatul din H5:
=H5 // 3000
Pentru a efectua o căutare inversă, formula din H10 este:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Bitul dificil este funcția ALEGE, care este utilizată pentru a rearanja matricea de tabel, astfel încât Costul să fie prima coloană și Opțiunea să fie ultima:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Funcția ALEGE este proiectată pentru a selecta o valoare pe baza unui index numeric. În acest caz, furnizăm trei valori de index într-o constantă de matrice:
(3,2,1) // array constant
Cu alte cuvinte, cerem coloana 3, apoi coloana 2, apoi coloana 1. Urmează cele trei intervale care reprezintă fiecare coloană a tabelului în ordinea în care apar pe foaia de lucru.
Cu această configurație, CHOOSE returnează toate cele trei coloane într-o singură matrice 2D astfel:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Dacă vizualizăm această matrice ca un tabel pe foaia de lucru, avem:
Notă: titlurile nu fac parte din matrice și sunt afișate aici doar pentru claritate.
Efectiv, am schimbat coloanele 1 și 3. Tabelul reorganizat este returnat direct la VLOOKUP, care se potrivește cu 3000 și returnează valoarea corespunzătoare din coloana 3, „C”.
Cu INDEX și MATCH
Soluția de mai sus funcționează bine, dar este greu de recomandat, deoarece majoritatea utilizatorilor nu vor înțelege cum funcționează formula. O soluție mai bună este INDEX și MATCH, folosind o formulă ca aceasta:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Aici, funcția MATCH găsește valoarea 3000 în D5: D8 și își returnează poziția, 3:
MATCH(G10,D5:D8,0) // returns 3
Notă: MATCH este configurat pentru o potrivire exactă prin setarea ultimului argument la zero (0).
MATCH returnează un rezultat direct la INDEX ca număr de rând, astfel încât formula devine:
=INDEX(B5:B8,3) // returns "C"
iar INDEX returnează valoarea din al treilea rând al lui B5: B8, „C”.
Această formulă arată cum INDEX și MATCH pot fi mai flexibile decât VLOOKUP.
Cu XLOOKUP
XLOOKUP oferă, de asemenea, o soluție foarte bună. Formula echivalentă este:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Cu o valoare de căutare de la G10 (3000), o matrice ookup de D5: D8 (costuri) și o matrice de rezultate de B5: B8 (opțiuni), XLOOKUP localizează 3000 în matrice de căutare și returnează elementul corespunzător din matricea de rezultate, „C”. Deoarece XLOOKUP efectuează o potrivire exactă în mod implicit, nu este necesar să setați modul de potrivire în mod explicit.
