Search code examples
excelexcel-formula

Get the position of smallest Offset value from a Index Match

I have a list with prices with MoQ (minimum order quantity) next to them. Some suppliers however like to make it extra complicated by having an additional price-list with possible cheaper prices for a same MoQ.

To get the right result, I need the smallest price but since we have different prices with different MoQ's already in the database, I need to link them correctly. So if one reference with MoQ 2 has new prices, I need to check for the lowest price with said MoQ.

Here's a test table:

MoQ Reference Which price? Price1 MoQ1 Price2 MoQ2 ExtraPrice1 ExtraMoQ1
2 189 1 182 2 176.89 1
1 .75 5 1 1

The preferred outcome would be the name (for clarity to my supervisor to see which price was chosen) aka "ExtraPrice1" for B2 but I'm ok with just the price (176.89), I can work from there. As for B3, it should also be "ExtraPrice1" since ExtraMoQ1 is the only corresponding MoQ for A3.

I tried the following in B2:

=INDEX(C$1:H$1, MATCH(1,INDEX(C2:H2=A2)*(MOD(COLUMN(C2:H2),2)=1),0)-1)))

Gotten result: Price 1
The reason for the MOD() is because I don't want to accidentally match with a price instead of an MoQ (see row 3). I tried it with MIN(C2:H2) in there as well but got an error #NA iirc. The index gives an array of 0's and 1's so I don't know how to get the lowest price which is to the left of said 1's in the array.

Also tried a different formula I found but it got the same result (the first found match)

=INDEX(C$1:H$1, SMALL(IF((C2:H2=A2),ROW(1:1)),1))

Don't fully understand this one with ROW(1:1) there but thought I'd give it a try regardless.

Then I tried using something I didn't find online but thought could work

=INDEX(C$1:H$1,MIN(OFFSET(FILTER(C2:H2,C2:H2=A2),,-1)))

but alas, I get an error; probably since Filter just returns the MoQ's found equal to A2 instead of the locations..

If I understood the functions better, I could figure out to get the position of the lowest value next to the found positions from the inner INDEX of the first formula but clearly I'm not there yet. There's always VBA I could fall back to but didn't think I'd learn the functions if I didn't bother with them once in a while.

Solution

  • You could try:

    enter image description here

    Formula in B2:

    =BYROW(2:3,LAMBDA(x,LET(y,WRAPROWS(TOROW(DROP(x,,2),3),2),MIN(FILTER(TAKE(y,,1),DROP(y,,1)<=@+x)))))

    The idea here is that we can loop complete rows, therefor account for whatever amount of columns lay ahead as long as Price/MoQ is written in pairs. So:

    • Loop each row, using variable 'x'. Note, here 'x' is still a range object which is important in the next few steps;
    • Create a nested variable 'y' which is a wrapped array of two columns after we remove the 1st two cells and used TOROW() to remove empty cells from our range object 'x';
    • Now we can FILTER() the 1st column of our wrapped variable 'y' based on the 2nd column where the values of this 2nd column are '<=' to @+x. Using implicit intersection to return the 1st value in an array, hence the use of '+x' to turn the range object into an array first.