Bendroji formulė
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Santrauka
Norėdami pakeisti VLOOKUP, ty surasti pradinę paieškos vertę naudodami VLOOKUP formulės rezultatą, galite naudoti sudėtingą formulę, pagrįstą funkcija CHOOSE, arba paprastesnes formules, pagrįstas INDEX ir MATCH arba XLOOKUP, kaip paaiškinta toliau. Parodytame pavyzdyje formulė H10 yra:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Pasirinkus šią sąranką, VLOOKUP randa parinktį, susietą su 3000 kaštų, ir pateikia „C“.
Pastaba: tai yra labiau pažengusi tema. Jei dar tik pradedate naudotis „VLOOKUP“, pradėkite čia.
Įvadas
Pagrindinis VLOOKUP apribojimas yra tai, kad jis gali tik ieškoti reikšmių dešinėje. Kitaip tariant, stulpelis su paieškos reikšmėmis turi būti kairėje nuo reikšmių, kurias norite gauti naudodami VLOOKUP. Dėl to, naudojant standartinę konfigūraciją, jokiu būdu negalima naudoti „VLOOKUP“ norint „žiūrėti į kairę“ ir pakeisti pirminę paiešką.
VLOOKUP požiūriu problemą galime vizualizuoti taip:
Žemiau paaiškintame sprendime naudojama funkcija PASIRINKTI pertvarkant lentelę VLOOKUP viduje.
Paaiškinimas
Pradedant nuo pradžios, H5 formulė yra įprasta VLOOKUP formulė:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Naudojant G5 kaip paieškos vertę („C“) ir duomenis B5: D8 kaip lentelių masyvą, VLOOKUP atlieka B stulpelio reikšmių paiešką ir grąžina atitinkamą vertę iš 3 stulpelio (D stulpelis), 3000. nulis (0) pateikiamas kaip paskutinis argumentas priversti tikslią atitiktį.
G10 formulė paprasčiausiai ištraukia rezultatą iš H5:
=H5 // 3000
Norėdami atlikti atvirkštinę paiešką, H10 formulė yra:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Keblus dalykas yra funkcija PASIRINKTI, naudojama pertvarkyti lentelių masyvą taip, kad pirmasis stulpelis būtų „Kaina“, o paskutinis - „Option“:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Funkcija PASIRINKTI yra skirta pasirinkti reikšmę pagal skaitinį indeksą. Šiuo atveju masyvo konstanta pateikiame tris indekso reikšmes:
(3,2,1) // array constant
Kitaip tariant, mes prašome 3 stulpelio, tada 2 stulpelio, tada 1 stulpelio. Po to seka trys diapazonai, kurie nurodo kiekvieną lentelės stulpelį ta tvarka, kokia jie rodomi darbalapyje.
Pasirinkus šią konfigūraciją, CHOOSE grąžina visus tris stulpelius viename 2D masyve taip:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Jei vizualizuojame šį masyvą kaip lentelę darbalapyje, turime:
Pastaba: antraštės nėra masyvo dalis ir čia pateikiamos tik aiškumo sumetimais.
Iš tikrųjų mes pakeitėme 1 ir 3 stulpelius. Pertvarkyta lentelė grąžinama tiesiai į VLOOKUP, kuri atitinka 3000, ir grąžina atitinkamą vertę iš 3 stulpelio „C“.
Su INDEX ir MATCH
Pirmiau pateiktas sprendimas veikia gerai, tačiau jį sunku rekomenduoti, nes dauguma vartotojų nesupras, kaip veikia formulė. Geresnis sprendimas yra INDEX ir MATCH, naudojant tokią formulę:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Čia funkcija MATCH randa 3000 reikšmę D5: D8 ir grąžina savo padėtį 3:
MATCH(G10,D5:D8,0) // returns 3
Pastaba: MATCH yra sukonfigūruotas tiksliai atitikčiai, nustatant paskutinį argumentą į nulį (0).
MATCH grąžina rezultatą tiesiai į INDEX kaip eilutės numerį, taigi formulė tampa:
=INDEX(B5:B8,3) // returns "C"
ir INDEX grąžina vertę iš trečios B5 eilutės: B8, "C".
Ši formulė parodo, kaip INDEX ir MATCH gali būti lankstesni nei VLOOKUP.
Su XLOOKUP
XLOOKUP taip pat pateikia labai gerą sprendimą. Ekvivalentinė formulė yra:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Naudodamas paieškos vertę iš G10 (3000), visus D5: D8 masyvus (išlaidos) ir rezultatų B5: B8 (pasirinktys) masyvą, XLOOKUP nustato 3000 paieškos masyvą ir grąžina atitinkamą elementą iš rezultatų masyvo, „C“. Kadangi pagal numatytuosius nustatymus „XLOOKUP“ atlieka tikslią atitiktį, nereikia aiškiai nustatyti atitikties režimo.
