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?

 

[duggie33]

Hi *vttrian,

You could try using the FILTER function. I believe it would be "=FILTER(A1:A12,B1:B12=MIN(B1:B12))".

I hope that helps,

Doug

 

[vttrian]

Hi *vttrian,

You could try using the FILTER function. I believe it would be "=FILTER(A1:A12,B1:B12=MIN(B1:B12))".

I hope that helps,

Doug

Thank you very much duggie33! That almost does the trick. I actually need the names of the 3 products with the lowest prices. They may all 3 have the same price, or the 2 of them, or none of them. Your proposal brought me closer to the solution but it returns the products with the single lowest price. So I changed the MIN function to SMALL. The results were the same. Do you have any other suggestions?

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E
1	Product	Price		Product	Price
2	Prod1	12		Prod4	5
3	Prod2	11		Prod6	5
4	Prod3	9		Prod7	6
5	Prod4	5
6	Prod5	12
7	Prod6	5
8	Prod7	6
9	Prod8	13
10	Prod9	16
11	Prod10	9
12	Prod11	12
Sheet2

Cell Formulas
Range	Formula
D2:E4	D2	=FILTER($A$2:$B$12,($B$2:$B$12<=SMALL($B$2:$B$12,3)))
Dynamic array formulas.

 

[Peter_SSs]

Welcome to the MrExcel board!

What do you want to happen if the 4th lowest price is the same as the 3rd lowest etc? See my sample data below.
Perhaps you want all the 'equal' products listed like @AhoyNC's formula would (though I have added a SORT so they are in order from lowest), otherwise if you definitely only want 3 listed I have given 2 options depending on whether you have all the latest functions or not.

23 02 25.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Price		Product	Price		Product	Price		Product	Price
2	Prod1	6		Prod4	5		Prod4	5		Prod4	5
3	Prod2	11		Prod6	5		Prod6	5		Prod6	5
4	Prod3	9		Prod1	6		Prod1	6		Prod1	6
5	Prod4	5		Prod7	6
6	Prod5	12		Prod8	6
7	Prod6	5
8	Prod7	6
9	Prod8	6
10	Prod9	16
11	Prod10	9
12	Prod11	12
3 Lowest

Cell Formulas
Range	Formula
D2:E6	D2	=SORT(FILTER(A2:B12,(B2:B12<=SMALL(B2:B12,3))),2)
G2:H4	G2	=TAKE(SORT(A2:B12,2),3)
J2:K4	J2	=INDEX(SORT(A2:B12,2),{1;2;3},{1,2})
Dynamic array formulas.

 

[vttrian]

Maybe something like this:

Cell Formulas
Range	Formula
D2:E4	D2	=FILTER($A$2:$B$12,($B$2:$B$12<=SMALL($B$2:$B$12,3)))
Dynamic array formulas.

Yup! That did it. Thank you very much. I really appreciate this. (I love this community!!!)

 

[vttrian]

Welcome to the MrExcel board!

What do you want to happen if the 4th lowest price is the same as the 3rd lowest etc? See my sample data below.
Perhaps you want all the 'equal' products listed like @AhoyNC's formula would (though I have added a SORT so they are in order from lowest), otherwise if you definitely only want 3 listed I have given 2 options depending on whether you have all the latest functions or not.

Cell Formulas
Range	Formula
D2:E6	D2	=SORT(FILTER(A2:B12,(B2:B12<=SMALL(B2:B12,3))),2)
G2:H4	G2	=TAKE(SORT(A2:B12,2),3)
J2:K4	J2	=INDEX(SORT(A2:B12,2),{1;2;3},{1,2})
Dynamic array formulas.

This works too. The first and last option at least. I am currently interested in the third option, so my problem is solved. The second option didn't work for me (I must be missing the function). Once again... I can't thank you enough guys!!

 

[Peter_SSs]

I am currently interested in the third option, so my problem is solved.

Good news!

The second option didn't work for me (I must be missing the function).

In that case it would have returned a #NAME? error ... and that is why I included the 3rd option.

Once again... I can't thank you enough guys!!

You just did.

 

[vttrian]

Hello guys.

The 3rd option mentioned above works like a charm. But unfortunately now I need to run this function every once in a while. I can manage to insert a button to run a sub in vba. Can the sort/index functions work in vba as a sub?

 

[Peter_SSs]

Is this what you mean?

Sub Low3()
  With Range("A2", Range("B" & Rows.Count).End(xlUp))
    .Offset(-1, 3).Resize(1).Value = .Rows(0).Value
    .Offset(0, 3).Cells(1, 1).Formula2 = "=INDEX(SORT(" & .Address & ",2),{1;2;3},{1,2})"
    With .Offset(, 3).CurrentRegion
      .Select
      .Value = .Value
      .Columns.AutoFit
    End With
  End With
End Sub

Before:

vttrian.xlsm
	A	B	C	D	E	F
1	Product	Price
2	Prod1	6
3	Prod2	11
4	Prod3	9
5	Prod4	5
6	Prod5	12
7	Prod6	5
8	Prod7	6
9	Prod8	6
10	Prod9	16
11	Prod10	9
12	Prod11	12
Sheet1

After:

vttrian.xlsm
	A	B	C	D	E	F
1	Product	Price		Product	Price
2	Prod1	6		Prod4	5
3	Prod2	11		Prod6	5
4	Prod3	9		Prod1	6
5	Prod4	5
6	Prod5	12
7	Prod6	5
8	Prod7	6
9	Prod8	6
10	Prod9	16
11	Prod10	9
12	Prod11	12
Sheet1