Generisk formel
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Sammanfattning
För att vända en VLOOKUP - dvs för att hitta det ursprungliga uppslagsvärdet med ett VLOOKUP-formelresultat - kan du använda en knepig formel baserad på CHOOSE-funktionen eller mer enkla formler baserade på INDEX och MATCH eller XLOOKUP som förklaras nedan. I exemplet som visas är formeln i H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Med denna inställning hittar VLOOKUP alternativet som är associerat med en kostnad på 3000 och returnerar "C".
Obs: detta är ett mer avancerat ämne. Om du precis har börjat med VLOOKUP, börja här.
Introduktion
En nyckelbegränsning för VLOOKUP är att den bara kan slå värden till höger. Med andra ord måste kolumnen med uppslagsvärden vara till vänster om de värden du vill hämta med VLOOKUP. Som ett resultat, med standardkonfiguration finns det inget sätt att använda VLOOKUP för att "titta åt vänster" och vända den ursprungliga sökningen.
Ur VLOOKUP-synpunkt kan vi visualisera problemet så här:
Lösningen som förklaras nedan använder CHOOSE-funktionen för att ordna om bordet inuti VLOOKUP.
Förklaring
Från och med början är formeln i H5 en normal VLOOKUP-formel:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Med hjälp av G5 som uppslagsvärde ("C") och data i B5: D8 som tabellmatris utför VLOOKUP en uppslagning av värden i kolumn B och returnerar motsvarande värde från kolumn 3 (kolumn D), 3000. Observera noll (0) tillhandahålls som det sista argumentet för att tvinga en exakt matchning.
Formeln i G10 hämtar helt enkelt resultatet från H5:
=H5 // 3000
För att utföra en omvänd uppslagning är formeln i H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Den knepiga biten är CHOOSE-funktionen, som används för att ordna om tabellmatrisen så att Cost är den första kolumnen, och Option är den sista:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
VÄLJ-funktionen är utformad för att välja ett värde baserat på ett numeriskt index. I det här fallet levererar vi tre indexvärden i en arraykonstant:
(3,2,1) // array constant
Med andra ord ber vi om kolumn 3, sedan kolumn 2 och sedan kolumn 1. Detta följs av de tre områden som representerar varje kolumn i tabellen i den ordning de visas på kalkylbladet.
Med den här konfigurationen returnerar VÄLJ alla tre kolumner i en enda 2D-array så här:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Om vi visualiserar denna matris som en tabell på kalkylbladet har vi:
Obs! Rubrikerna ingår inte i matrisen och visas här endast för tydlighetens skull.
Effektivt har vi bytt kolumner 1 och 3. Den omorganiserade tabellen returneras direkt till VLOOKUP, som matchar 3000, och returnerar motsvarande värde från kolumn 3, "C".
Med INDEX och MATCH
Ovanstående lösning fungerar bra, men det är svårt att rekommendera eftersom de flesta användare inte förstår hur formeln fungerar. En bättre lösning är INDEX och MATCH, med en sådan formel:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Här hittar MATCH-funktionen värdet 3000 i D5: D8 och returnerar sin position, 3:
MATCH(G10,D5:D8,0) // returns 3
Obs: MATCH konfigureras för en exakt matchning genom att sätta det sista argumentet till noll (0).
MATCH returnerar ett resultat direkt till INDEX som radnummer, så formeln blir:
=INDEX(B5:B8,3) // returns "C"
och INDEX returnerar värdet från den tredje raden i B5: B8, "C".
Denna formel visar hur INDEX och MATCH kan vara mer flexibla än VLOOKUP.
Med XLOOKUP
XLOOKUP ger också en mycket bra lösning. Motsvarande formel är:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Med ett uppslagsvärde från G10 (3000), al ochup-array av D5: D8 (kostnad) och en resultatmatris av B5: B8 (optioner), lokaliserar XLOOKUP 3000 i lookup-array och returnerar motsvarande artikel från resultatmatrisen, "C". Eftersom XLOOKUP utför en exakt matchning som standard behöver du inte ställa in matchningsläget uttryckligen.
