XLETAUPP hitta matchande värde i ett intervall

I följande exempel skall vi jobba vidare med formeln XLETAUPP som är ny version av LETARAD. I följande exempel skall vi titta mer på den nya funktionens valfria argument som ”match_mode”.

Vår data ser ut som nedan där vi har en blå tabell med en rabattmodell. Kunder som köper för ett visst värde får en rabatt baserat på hur mycket det handlat för. För att ta reda på hur mycket det slutliga priset blir så matar vi först in värdet i cell A2. I Cell B2 hämtas rabatten med en XLETAUPP-formel och slutligen i cell C2 så räknas det rabatterade priset ut.

I fallet nedan så är värdet 49 000 kr. När vi tittar i tabellen så är blir det en rabatt på 20 %. För att få 30 % rabatt så hade det behövts handlas för minst 50 000 kr.

Vi skall nu titta djupare på hur vi kan använda oss av funktionen XLETAUPP för att hämta rabattsatsen i den blå tabellen ifrån värdet i cell A2. Vår utmaning är att vi inte söker efter ett exakt värde utan vi skall hitta ett värde baserat på intervall.

Vår XLETAUPP funktion består i följande fall av sex olika argument.

Det första argumentet är värdet vi skall leta upp, vårt lookup_value, dvs cell A2 där vi har vårat värde.

Det andra argumentet är i vilket område vi skall leta i för att hitta en matchning med det första argumentet, vårt lookup_array. (Array = Område). Det blir området B5 till B8 där vi har andra pengavärden som vi skall få att fungera som intervall i senare argument.

Det tredje argumentet är i vilket område vi skall hämta data, vårt return_array. Detta område skall ligga jämsides vårat lookup_array så att de matchar. Det blir området A5 till A8 där vi har våra rabattsatser.

Det fjärde argumentet är valfritt, vårt If_not_found och är vad som skall returneras ifall det inte går att matcha ett värde. Alltså ungefär som OMSKANAS funktionen. Här anger vi bara 0 eftersom då ges ingen rabatt. Detta är nödvändigt ifall värdet understiger 20 000 då vi skulle få ett #SAKNAS-värde som det inte går att göra en beräkning på.

Det femte argumentet är vårt match_mode som bestämmer hur matchningen skall ske. Här finns totalt fyra val. Vi väljer valet ”-1” som står för ”Exakt matchning eller nästa mindre objekt”. Detta betyder att ifall det stämmer exakt så skall det värdet återges. Dvs om värdet är 40 000 kr så matchar det exakt mot cell B7 så då skall rabatten 20% returneras. ”… eller nästa mindre objekt” innebär att ifall det inte är en exakt matchning så skall den leta nedåt. Så är värdet 49 000 så ligger 50 000 närmre men vi anger att den skall söka efter ”nästa mindre objekt”, vilket i vårt fall blir 40 000 vilket är nästa mindre objekt i listan ifrån 49 000 kr. Hade vi angett alternativet 1 så hade ett värde på 49 000 kr gett oss en rabatt på 30% eftersom det alternativet ger oss ” Exakt matchning eller nästa större objekt”. Man kan se de två alternativen som att avrunda uppåt eller nedåt.

Det sjätte argumentet och sista så ställer vi in i vilken ordning som sökningen skall ske. Vi använder oss av standardinställningen 1, ”Sök första till sista”.

Nu skall formeln vara färdig och genom att skriva in beloppet i cell A2 så räknas rabatten och det slutliga priset ut. Den slutliga XLETAUPP-formeln som hämtar rabattsatsen blir:

   =XLETAUPP(A2;B5:B8;A5:A8;0;-1;1)

Traditionellt så hade man antagligen löst detta problem genom att göra en nästlad OM-formel. För den så bvheövs en ny OM-formel för varje intervall. I vårat fall så blir det:

   =OM(A2<B5;0;OM(A2<B6;A5;OM(A2<B7;A6;OM(A2<B8;A7;A8))))

OM-formeln blir betydligt krångligare och man måste skapa en för varje rad i tabellen. Om vi har flera nivåer/rader och man måste göra det manuellt så tar det mycket tid och det är lätt hänt att man begår ett slarvfel. Med XLETAUPP så räcker det med endast en formel oavsett antalet intervall.