Generisk formel
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Resumé
For at vende en VLOOKUP - dvs. for at finde den originale opslagsværdi ved hjælp af et VLOOKUP-formelresultat - kan du bruge en vanskelig formel baseret på CHOOSE-funktionen eller mere ligetil formler baseret på INDEX og MATCH eller XLOOKUP som forklaret nedenfor. I det viste eksempel er formlen i H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Med denne opsætning finder VLOOKUP den mulighed, der er forbundet med en pris på 3000, og returnerer "C".
Bemærk: dette er et mere avanceret emne. Hvis du lige er kommet i gang med VLOOKUP, skal du starte her.
Introduktion
En nøglebegrænsning af VLOOKUP er, at den kun kan slå værdier op til højre. Med andre ord skal kolonnen med opslagsværdier være til venstre for de værdier, du vil hente med VLOOKUP. Som et resultat er der med standardkonfiguration ingen måde at bruge VLOOKUP til at "se til venstre" og vende den originale opslag.
Fra VLOOKUP's synspunkt kan vi visualisere problemet sådan:
Løsningen, der er forklaret nedenfor, bruger CHOOSE-funktionen til at omarrangere bordet inde i VLOOKUP.
Forklaring
Fra begyndelsen er formlen i H5 en normal VLOOKUP-formel:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Ved hjælp af G5 som opslagsværdi ("C") og dataene i B5: D8 som tabelarray udfører VLOOKUP et opslag på værdier i kolonne B og returnerer den tilsvarende værdi fra kolonne 3 (kolonne D), 3000. Bemærk nul (0) er angivet som det sidste argument for at tvinge et nøjagtigt match.
Formlen i G10 trækker simpelthen resultatet fra H5:
=H5 // 3000
For at udføre en omvendt opslag er formlen i H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Den vanskelige bit er CHOOSE-funktionen, som bruges til at omarrangere tabelarrayet, så Cost er den første kolonne, og Option er den sidste:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
VÆLG-funktionen er designet til at vælge en værdi baseret på et numerisk indeks. I dette tilfælde leverer vi tre indeksværdier i en matrixkonstant:
(3,2,1) // array constant
Med andre ord beder vi om kolonne 3, derefter kolonne 2 og derefter kolonne 1. Dette efterfølges af de tre områder, der repræsenterer hver kolonne i tabellen i den rækkefølge, de vises på regnearket.
Med denne konfiguration returnerer VÆLG alle tre kolonner i et enkelt 2D-array som dette:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Hvis vi visualiserer denne matrix som en tabel på regnearket, har vi:
Bemærk: Overskrifterne er ikke en del af arrayet og vises kun her for at gøre klarheden.
Effektivt har vi byttet kolonne 1 og 3. Den reorganiserede tabel returneres direkte til VLOOKUP, som matcher 3000, og returnerer den tilsvarende værdi fra kolonne 3, "C".
Med INDEX og MATCH
Ovenstående løsning fungerer fint, men det er svært at anbefale, da de fleste brugere ikke forstår, hvordan formlen fungerer. En bedre løsning er INDEX og MATCH ved hjælp af en formel som denne:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Her finder MATCH-funktionen værdien 3000 i D5: D8 og returnerer sin position, 3:
MATCH(G10,D5:D8,0) // returns 3
Bemærk: MATCH er konfigureret til en nøjagtig matchning ved at indstille det sidste argument til nul (0).
MATCH returnerer et resultat direkte til INDEX som række nummer, så formlen bliver:
=INDEX(B5:B8,3) // returns "C"
og INDEX returnerer værdien fra den tredje række i B5: B8, "C".
Denne formel viser, hvordan INDEX og MATCH kan være mere fleksible end VLOOKUP.
Med XLOOKUP
XLOOKUP giver også en meget god løsning. Den ækvivalente formel er:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Med en opslagsværdi fra G10 (3000), al ogsåup array af D5: D8 (omkostninger) og et resultat array af B5: B8 (optioner), lokaliserer XLOOKUP 3000 i opslagsarray og returnerer det tilsvarende element fra resultat arrayet, "C". Da XLOOKUP som standard udfører et nøjagtigt match, er det ikke nødvendigt at indstille matchtilstand eksplicit.
