Generieke formule
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Samenvatting
Om een VERT.ZOEKEN om te keren - dat wil zeggen om de originele opzoekwaarde te vinden met behulp van het resultaat van een VERT.ZOEKEN-formule - kunt u een lastige formule gebruiken op basis van de functie KIEZEN, of eenvoudiger formules op basis van INDEX en VERGELIJKEN of XLOOKUP, zoals hieronder wordt uitgelegd. In het getoonde voorbeeld is de formule in H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Met deze instelling zoekt VERT.ZOEKEN de optie die is gekoppeld aan een kostprijs van 3000, en retourneert "C".
Let op: dit is een meer geavanceerd onderwerp. Als je net begint met VERT.ZOEKEN, begin dan hier.
Invoering
Een belangrijke beperking van VERT.ZOEKEN is dat het alleen waarden aan de rechterkant kan opzoeken. Met andere woorden, de kolom met opzoekwaarden moet links van de waarden staan die u met VERT.ZOEKEN wilt ophalen. Als gevolg hiervan is er met de standaardconfiguratie geen manier om VERT.ZOEKEN te gebruiken om "naar links te kijken" en de oorspronkelijke zoekactie om te keren.
Vanuit het standpunt van VERT.ZOEKEN kunnen we het probleem als volgt visualiseren:
De oplossing die hieronder wordt uitgelegd, gebruikt de functie KIEZEN om de tabel in VERT.ZOEKEN opnieuw in te delen.
Uitleg
Beginnend bij het begin, is de formule in H5 een normale VERT.ZOEKEN-formule:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Door G5 te gebruiken als de opzoekwaarde ("C") en de gegevens in B5: D8 als de tabelmatrix, voert VERT.ZOEKEN een zoekactie uit op waarden in kolom B en retourneert de overeenkomstige waarde uit kolom 3 (kolom D), 3000. Opmerking nul (0) wordt opgegeven als het laatste argument om een exacte overeenkomst te forceren.
De formule in G10 haalt eenvoudig het resultaat uit H5:
=H5 // 3000
Om een reverse lookup uit te voeren, is de formule in H10:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Het lastige is de functie CHOOSE, die wordt gebruikt om de tabelmatrix opnieuw te rangschikken, zodat Cost de eerste kolom is en Option de laatste:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
De CHOOSE-functie is ontworpen om een waarde te selecteren op basis van een numerieke index. In dit geval leveren we drie indexwaarden in een matrixconstante:
(3,2,1) // array constant
Met andere woorden, we vragen om kolom 3, dan kolom 2 en dan kolom 1. Dit wordt gevolgd door de drie bereiken die elke kolom van de tabel vertegenwoordigen in de volgorde waarin ze op het werkblad verschijnen.
Met deze configuratie retourneert CHOOSE alle drie de kolommen in een enkele 2D-array als volgt:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Als we deze array als een tabel op het werkblad visualiseren, hebben we:
Opmerking: de koppen maken geen deel uit van de array en worden hier alleen voor de duidelijkheid weergegeven.
In feite hebben we de kolommen 1 en 3 omgewisseld. De gereorganiseerde tabel wordt rechtstreeks teruggestuurd naar VERT.ZOEKEN, die overeenkomt met 3000, en retourneert de overeenkomstige waarde uit kolom 3, "C".
Met INDEX en MATCH
De bovenstaande oplossing werkt prima, maar is moeilijk aan te bevelen omdat de meeste gebruikers niet zullen begrijpen hoe de formule werkt. Een betere oplossing is INDEX en MATCH, met een formule als deze:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Hier vindt de MATCH-functie de waarde 3000 in D5: D8, en retourneert zijn positie, 3:
MATCH(G10,D5:D8,0) // returns 3
Opmerking: MATCH is geconfigureerd voor een exacte overeenkomst door het laatste argument in te stellen op nul (0).
VERGELIJKEN retourneert een resultaat rechtstreeks naar INDEX als het rijnummer, dus de formule wordt:
=INDEX(B5:B8,3) // returns "C"
en INDEX retourneert de waarde van de derde rij van B5: B8, "C".
Deze formule laat zien hoe INDEX en MATCH flexibeler kunnen zijn dan VERT.ZOEKEN.
Met XLOOKUP
XLOOKUP biedt ook een zeer goede oplossing. De equivalente formule is:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Met een opzoekwaarde van G10 (3000), een opzoekmatrix van D5: D8 (kosten) en een resultatenmatrix van B5: B8 (opties), lokaliseert XLOOKUP de 3000 in de opzoekmatrix en retourneert het overeenkomstige item uit de resultatenmatrix, "C". Omdat XLOOKUP standaard een exacte overeenkomst uitvoert, is het niet nodig om de overeenkomstmodus expliciet in te stellen.
