Using SMALL function to get the smallest value in a range, trying to combine with OFFSET to get the value to the left
I am using the SMALL formula in H2 to find the lowest price in a row, which works great.
=IF(ISERROR(SMALL(A2:F2,COUNTIF(A2:F2,0)+1)),"",SMALL(A2:F2,COUNTIF(A2:F2,0)+1))
I'm having trouble retrieving the value to the left of it (its corresponding # element) using OFFSET.
A | B | C | D | E | F | G | H
item# | price | item# | price | item# | price | lowest value item# | lowest value
123 | 70 | 456 | 80 | 789 | 67.89 | ? | 67.89
Also, I don't know which column will have the lowest value, AF might change. I spent a few hours searching and tried to use the original formula as the reference part for OFFSET:
=OFFSET(IF(ISERROR(SMALL(A2:F2,COUNTIF(A2:F2,0)+1)),"",SMALL(A2:F2,COUNTIF(A2:F2,0)+1)),0,-1,1,1)
and its variations. This returns #Value!
Am I on the right track? Is this OFFSET correct? Thanks to
source to share
I will also remove the H2 dependency by replacing it this way by simply adding the function MIN
=INDEX(A2:F2;MATCH(MIN(A2:F2);A2:F2;0)-1)
however, you always need to distinguish between item number and price. Sometimes the value of an item may be lower than the price, and then your formula will not work. It will return the item's value as the lowest value and then return the price for the previous item. It would be a mess.
To solve this problem, you need to add two conditions MATCHto find an exact match:
here is the formula LOW VALUE #: / :
=INDEX(A2:F2;(MATCH(MIN(IF(A1:F1="Price";A2:F2));A2:F2;0)-1)*(MATCH("Item#";A1:F1;0)))
and the formula to find the PRICE VALUES LOEWST
=MIN(IF(A1:F1="Price";A2:F2))
For example, in the following: 
The value in C2 is the lowest, but it is not the lowest price value , which itself is in F2.
That is why you need to add these match conditions to find the value above which element is # or above which is Price . Therefore, for the Price I used MIN (IF ) and for Item # I used the MATCH condition .
here is an example excel sheet downloaded from Dropbox
PS
DO NOT forget to adapt the forms to the regional settings, replacing ";" from ","
Tell me if it works.
source to share
Is OFFSET the correct way to do this? - Not
OFFSET
expects a range reference, not a value, as its first parameter.
If your prices are unique then use this (if not, I'm not sure what result you expect)
=INDEX(A2:F2,MATCH(H2,A2:F2,0)-1)
However, your formula SMALL
looks suspicious. Looks like you want a refund ""
if all prices are 0
. But your formula will return the smallest item#
in this case. Can you confirm or explain?
source to share