Generieke formule
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Samenvatting
Om de beste overeenkomst in numerieke gegevens te vinden, kunt u INDEX en MATCH gebruiken, met behulp van de ABS- en MIN-functies. In het getoonde voorbeeld is de formule in F5, naar beneden gekopieerd,:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
waarbij reis (B5: B14) en kosten (C5: C14) bereiken worden genoemd.
In F5, F6 en F7 retourneert de formule de rit die qua kosten het dichtst bij respectievelijk 500, 1000 en 1500 ligt.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter, behalve in Excel 365.
Uitleg
In de kern is dit een INDEX- en MATCH-formule: MATCH lokaliseert de positie van de dichtstbijzijnde overeenkomst, stuurt de positie naar INDEX en INDEX retourneert de waarde op die positie in de Trip-kolom. Het harde werk wordt gedaan met de MATCH-functie, die zorgvuldig is geconfigureerd om het "minimumverschil" als volgt te matchen:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Door de zaken stap voor stap te nemen, wordt de opzoekwaarde als volgt berekend met MIN en ABS:
MIN(ABS(cost-E5)
Ten eerste wordt de waarde in E5 afgetrokken van de genoemde bereikkosten (C5: C14). Dit is een arraybewerking, en aangezien er 10 waarden in het bereik zijn, is het resultaat een array met 10 waarden zoals deze:
(899;199;250;-201;495;1000;450;-101;500;795)
Deze getallen vertegenwoordigen het verschil tussen elke kostprijs in C5: C15 en de kosten in cel E5, 700. Sommige waarden zijn negatief omdat de kosten lager zijn dan het aantal in E5. Om negatieve waarden om te zetten in positieve waarden, gebruiken we de ABS-functie:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
die retourneert:
(899;199;250;201;495;1000;450;101;500;795)
We zoeken naar de beste overeenkomst, dus gebruiken we de MIN-functie om het kleinste verschil te vinden, namelijk 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Dit wordt de opzoekwaarde binnen MATCH. De lookup-array wordt gegenereerd zoals eerder:
ABS(cost-E5) // generate lookup array
die dezelfde array retourneert die we eerder zagen:
(899;199;250;201;495;1000;450;101;500;795)
We hebben nu wat we nodig hebben om de positie van de dichtstbijzijnde overeenkomst (kleinste verschil) te vinden, en we kunnen het MATCH-gedeelte van de formule als volgt herschrijven:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Met 101 als de opzoekwaarde geeft VERGELIJKEN 8 terug, aangezien 101 op de 8e positie in de array staat. Ten slotte wordt deze positie in INDEX ingevoerd als het rijargument, met de benoemde bereikreis als de array:
=INDEX(trip,8)
en INDEX retourneert de 8e reis in het bereik, "Spanje". Wanneer de formule naar de cellen F6 en F7 wordt gekopieerd, vindt deze de beste overeenkomst tussen 1000 en 1500, "Frankrijk" en "Thailand", zoals weergegeven.
Opmerking: als er een gelijkspel is, retourneert deze formule de eerste overeenkomst.
Met XLOOKUP
De functie XLOOKUP biedt een interessante manier om dit probleem op te lossen, omdat voor een overeenkomsttype van 1 (exacte overeenkomst of op één na grootste) of -1 (exacte overeenkomst of volgende kleinste) geen gegevens hoeven te worden gesorteerd. Dit betekent dat we een formule als deze kunnen schrijven:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Zoals hierboven gebruiken we de absolute waarde van (cost-E5) om een opzoekmatrix te maken:
(899;199;250;201;495;1000;450;101;500;795)
Vervolgens configureren we XLOOKUP om te zoeken naar nul, met het zoektype ingesteld op 1, voor exacte overeenkomst of op een na grootste. We leveren de genoemde bereikreis als de retourmatrix, dus het resultaat is zoals eerder "Spanje".
