Exclude a cell in MIN function, which is embedded in a XLOOKUP function

[vttrian]

Say that I have products in column A (A1:A12) and prices in column B (B1:B12). I use the MIN and the XLOOKUP functions to see the most cheap product.

This is done with =XLOOKUP(MIN(B1:B12);B1:B12;A1:A12)

Let's say the function above returns A3 as the cheapest product.

In case the lowest price corresponds to 2 or 3 products though, the function will ignore them and return only the first one. (A3 product's price is 3$, but also A5 product's price is 3$).

Is there a way to run the MIN function again, in another cell, excluding the price (and therefore the product) that returned in the first run, by excluding the cell of column B from the range?

 

[vttrian]

Hello again. Thank you, this seems to do the trick. But as more questions appear I would like to ask you two things.

Firstly I wonder what I should do to change the output address of the results. Let's say I want the results to be in the Worksheets("Sheet2!") and in the cells beginning from the Range("B3!"). How could I achieve this?

Secondly I would like to know why the mentioned above code won't run in my workplace pc where I have office 2013 installed. I thought of migrating to VBA in order to overcome compatibility problems. Now when I run the code (it's called from a button), I get the run-time error '438' and in the debug I get a yellow indicator in the line .Offset(0, 3).Cells(1, 1).Formula2 = "=INDEX(SORT(" & .Address & ",2),{1;2;3},{1,2})"

Once again I thank you all, very much.

 

[Peter_SSs]

Let's say I want the results to be in the Worksheets("Sheet2!") and in the cells beginning from the Range("B3!"). How could I achieve this?

Tell or show us what else is already in that worksheet/area. You will see that I have used CurrentRegion in my code and in my sample the area in and around the results was completely empty before the code. Would like to know what, if anything, is already in that result sheet in columns A:D

Secondly I would like to know why the mentioned above code won't run in my workplace pc where I have office 2013 installed.

Because the code uses the SORT() function which is only available in 365 and 2021.
To do the same thing for 2013 would be considerably more complex.