Vispārēja formula
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Kopsavilkums
Lai mainītu VLOOKUP - ti, lai atrastu sākotnējo uzmeklēšanas vērtību, izmantojot VLOOKUP formulas rezultātu, varat izmantot sarežģītu formulu, kuras pamatā ir funkcija CHOOSE, vai vienkāršākas formulas, kuru pamatā ir INDEX un MATCH vai XLOOKUP, kā paskaidrots tālāk. Parādītajā piemērā H10 formula ir:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Izmantojot šo iestatījumu, VLOOKUP atrod iespēju, kas saistīta ar 3000 izmaksām, un atgriež "C".
Piezīme: šī ir progresīvāka tēma. Ja jūs tikko sākat darbu ar VLOOKUP, sāciet šeit.
Ievads
Galvenais VLOOKUP ierobežojums ir tas, ka vērtības var meklēt tikai pa labi. Citiem vārdiem sakot, kolonnai ar uzmeklēšanas vērtībām jābūt pa kreisi no vērtībām, kuras vēlaties izgūt, izmantojot VLOOKUP. Rezultātā ar standarta konfigurāciju nav iespējams izmantot VLOOKUP, lai "skatītos pa kreisi" un mainītu sākotnējo uzmeklēšanu.
No VLOOKUP viedokļa problēmu varam vizualizēt šādi:
Tālāk paskaidrotais risinājums izmanto funkciju IZVĒLE, lai pārkārtotu tabulu VLOOKUP iekšpusē.
Paskaidrojums
Sākot no sākuma, H5 formula ir parasta VLOOKUP formula:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Izmantojot G5 kā uzmeklēšanas vērtību ("C") un datus tabulas B5: D8 kā tabulas masīvu, VLOOKUP veic uzmeklēšanu par vērtībām B slejā un atgriež atbilstošo vērtību no 3. slejas (D sleja), 3000. nulle (0) ir norādīta kā pēdējais arguments precīzas atbilstības piespiešanai.
G10 formula vienkārši izvelk rezultātu no H5:
=H5 // 3000
Lai veiktu reverso meklēšanu, H10 formula ir šāda:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Viltīgais bits ir funkcija CHOOSE, ko izmanto, lai pārkārtotu tabulas masīvu tā, lai maksa būtu pirmā kolonna, bet Option - pēdējā:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Funkcija CHOOSE ir paredzēta, lai atlasītu vērtību, pamatojoties uz skaitlisko indeksu. Šajā gadījumā masīva konstantē mēs piegādājam trīs indeksa vērtības:
(3,2,1) // array constant
Citiem vārdiem sakot, mēs lūdzam 3. kolonnu, pēc tam 2. kolonnu, pēc tam 1. kolonnu. Tam seko trīs diapazoni, kas atspoguļo katru tabulas kolonnu tādā secībā, kādā tie parādās darblapā.
Izmantojot šo konfigurāciju, CHOOSE atgriež visas trīs kolonnas vienā 2D masīvā šādi:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Ja mēs vizualizējam šo masīvu kā tabulu darblapā, mums ir:
Piezīme: virsraksti nav masīva daļa, un šeit tie tiek parādīti tikai skaidrības labad.
Faktiski mēs esam nomainījuši 1. un 3. kolonnu. Pārkārtotā tabula tiek atgriezta tieši VLOOKUP, kas atbilst 3000, un atgriež atbilstošo vērtību no 3. slejas "C".
Ar INDEX un MATCH
Iepriekš minētais risinājums darbojas labi, taču to ir grūti ieteikt, jo lielākā daļa lietotāju nesapratīs, kā formula darbojas. Labāks risinājums ir INDEX un MATCH, izmantojot šādu formulu:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Šeit funkcija MATCH atrod vērtību 3000 D5: D8 un atgriež savu pozīciju 3:
MATCH(G10,D5:D8,0) // returns 3
Piezīme: MATCH ir konfigurēts precīzai atbilstībai, pēdējo argumentu iestatot uz nulli (0).
MATCH atgriež rezultātu tieši INDEX kā rindas numuru, tāpēc formula kļūst:
=INDEX(B5:B8,3) // returns "C"
un INDEX atgriež vērtību no B5 trešās rindas: B8, "C".
Šī formula parāda, kā INDEX un MATCH var būt elastīgākas nekā VLOOKUP.
Ar XLOOKUP
XLOOKUP nodrošina arī ļoti labu risinājumu. Ekvivalenta formula ir:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Izmantojot uzmeklēšanas vērtību no G10 (3000), visu D5: D8 masīvu (izmaksas) un rezultātu masīvu B5: B8 (opcijas), XLOOKUP atrod 3000 uzmeklēšanas masīvā un atgriež atbilstošo vienumu no rezultātu masīva, "C". Tā kā pēc noklusējuma XLOOKUP veic precīzu atbilstību, nav nepieciešams skaidri iestatīt spēles režīmu.
