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:

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.

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel