Üldine valem
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Kokkuvõte
VLOOKUPi ümberpööramiseks - st VLOOKUPi valemitulemuse abil algse otsingu väärtuse leidmiseks - võite kasutada funktsiooni CHOOS põhinevat keerukat valemit või sirgemaid valemeid, mis põhinevad INDEXil ja MATCHil või XLOOKUPil, nagu allpool selgitatud. Näidatud näites on H10 valem:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Selle seadistusega leiab VLOOKUP suvandi, mis on seotud maksumusega 3000, ja tagastab "C".
Märkus: see on arenenum teema. Kui olete VLOOKUPiga alles alustanud, alustage siit.
Sissejuhatus
VLOOKUPi peamine piirang on see, et see saab väärtusi otsida ainult paremale. Teisisõnu, otsinguväärtustega veerg peab olema VLOOKUP-iga allalaaditavatest väärtustest vasakul. Selle tulemusel ei saa standardse konfiguratsiooni korral VLOOKUP-i kasutada "vasakule vaatamiseks" ja algse otsingu ümberpööramiseks.
VLOOKUPi vaatevinklist võime probleemi visualiseerida järgmiselt:
Allpool selgitatud lahendus kasutab funktsiooni CHOOSE VLOOKUP-is oleva laua ümberkorraldamiseks.
Selgitus
Algusest peale on H5 valem tavaline VLOOKUP valem:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Kasutades otsingu väärtusena G5 ("C") ja tabeli massiivina B5: D8 andmeid, teostab VLOOKUP otsingu veerus B olevate väärtuste kohta ja tagastab vastava väärtuse veerust 3 (veerg D), 3000. Viimase argumendina täpse vaste sundimiseks esitatakse null (0).
G10 valem tõmbab tulemuse lihtsalt H5-st:
=H5 // 3000
Pöördotsingu tegemiseks on H10 valem:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Keeruline bitt on funktsioon CHOOSE, mida kasutatakse tabeli massiivi ümberkorraldamiseks nii, et esimene veerg oleks Kulu ja viimane oleks Option:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Funktsioon CHOOSE on loodud väärtuse valimiseks numbrilise indeksi alusel. Sel juhul esitame massiivikonstandis kolm indeksi väärtust:
(3,2,1) // array constant
Teisisõnu, palume veergu 3, seejärel veergu 2 ja seejärel veergu 1. Sellele järgnevad kolm vahemikku, mis tähistavad tabeli igat veergu järjestuses, mis need töölehel kuvatakse.
Selle konfiguratsiooni korral tagastab CHOOSE kõik kolm veergu ühes 2D massiivis järgmiselt:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Kui visualiseerime seda massiivi tabelina tabelina, on meil:
Märkus: pealkirjad ei kuulu massiivi hulka ja neid kuvatakse siin ainult selguse huvides.
Tegelikult oleme vahetanud veerud 1 ja 3. Ümberkorraldatud tabel tagastatakse otse VLOOKUP-i, mis vastab 3000-le, ja tagastab vastava väärtuse veerust 3 "C".
Indeksiga ja MATCH
Ülaltoodud lahendus töötab hästi, kuid seda on raske soovitada, kuna enamik kasutajaid ei saa valemi toimimisest aru. Parem lahendus on INDEX ja MATCH, kasutades sellist valemit:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Siit leiab funktsioon MATCH D5: D8 väärtuse 3000 ja tagastab oma positsiooni 3:
MATCH(G10,D5:D8,0) // returns 3
Märkus: MATCH on konfigureeritud täpse vaste jaoks, seadistades viimase argumendi nulli (0).
MATCH tagastab tulemuse otse INDEX-ile rea numbrina, nii et valemiks saab:
=INDEX(B5:B8,3) // returns "C"
ja INDEX tagastab väärtuse B5 kolmandast reast: B8, "C".
See valem näitab, kuidas INDEX ja MATCH võivad olla paindlikumad kui VLOOKUP.
XLOOKUPiga
XLOOKUP pakub ka väga head lahendust. Samaväärne valem on:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Otsinguväärtusega G10 (3000), kogu D5: D8 massiiv (kulud) ja tulemuste massiivi B5: B8 (valikud), otsib XLOOKUP 3000 otsingumassiivi ja tagastab tulemuste massiivilt vastava üksuse, "C". Kuna XLOOKUP sooritab vaikimisi täpse vaste, pole vaja sobitusrežiimi selgesõnaliselt seada.
