Vlookup with minimum value match

[canderson41]

I'm trying to come up with a solution for using a preference cell mixed with a vlookup match. I want the result to be the value cell for each unique item with the lowest preference value. I'm not sure if Vlookup is the best function or not, so any suggestions would be helpful.

Item	Preference (Lowest is Best)	Value		Item	Value
Apples	1​	6​		Apples	6​
Bananas	2​	7​		Bananas	7​
Pineapple	4​	5​		Pineapple	5​
Oranges	1​	3​		Oranges	3​
Apples	2​	10​
Bananas	3​	7​
Pineapple	9​	10​
Oranges	2​	5​
Apples	3​	4​
Apples	4​	9​
Pineapple	11​	1​
Oranges	3​	8​

 

[gargamalebarbosa]

 

[canderson41]

View attachment 71586

I tried that, and I'm not getting the same result.

=INDEX($C$2:$C$13,MATCH(1,($A$2:$A$13=E2)*(MIN($B$2:$B$13)),0))

 

[AhoyNC]

Maybe.

Book2
	A	B	C	D	E	F
1	tem	Preference (Lowest is Best)	Value		Item	Value
2	Apples	1	6		Apples	6
3	Bananas	2	7		Bananas	7
4	Pineapple	4	5		Oranges	3
5	Oranges	1	3		Pineapple	5
6	Apples	2	10
7	Bananas	3	7
8	Pineapple	9	10
9	Oranges	2	5
10	Apples	3	4
11	Apples	4	9
12	Pineapple	11	1
13	Oranges	3	8
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=SORT(UNIQUE(A2:A13))
F2:F5	F2	=FILTER($C$2:$C$13,($A$2:$A$13=E2)*($B$2:$B$13=MINIFS($B$2:$B$13,$A$2:$A$13,E2)))
Dynamic array formulas.

 

[canderson41]

Maybe.

Book2
	A	B	C	D	E	F
1	tem	Preference (Lowest is Best)	Value		Item	Value
2	Apples	1	6		Apples	6
3	Bananas	2	7		Bananas	7
4	Pineapple	4	5		Oranges	3
5	Oranges	1	3		Pineapple	5
6	Apples	2	10
7	Bananas	3	7
8	Pineapple	9	10
9	Oranges	2	5
10	Apples	3	4
11	Apples	4	9
12	Pineapple	11	1
13	Oranges	3	8
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=SORT(UNIQUE(A2:A13))
F2:F5	F2	=FILTER($C$2:$C$13,($A$2:$A$13=E2)*($B$2:$B$13=MINIFS($B$2:$B$13,$A$2:$A$13,E2)))
Dynamic array formulas.

This is strange.... If I copy your entire sheet and paste it, it works. If I just copy the formulas I get a !CALC error. In either case, I believe this solves my issue. Thank you!

 

[gargamalebarbosa]

I tried that, and I'm not getting the same result.

=INDEX($C$2:$C$13,MATCH(1,($A$2:$A$13=E2)*(MIN($B$2:$B$13)),0))

It worked for me. But i saw your problem solved with other solution.

 

[canderson41]

It worked for me. But i saw your problem solved with other solution.

It looks like I was having some other excel issue... it looks like it works for me after restarting excel

 

[AhoyNC]

@gargamalebarbosa ,
I think your formula should work if you add the MATCH type to equal 0.
INDEX($C$2:$C$13,MATCH(1,($A$2:$A$13=E2)*MIN($B$2:$B$13),0),0)

 

[gargamalebarbosa]

@gargamalebarbosa ,
I think your formula should work if you add the MATCH type to equal 0.
INDEX($C$2:$C$13,MATCH(1,($A$2:$A$13=E2)*MIN($B$2:$B$13),0),0)

Last 0 is for match function parameter. It worked for me with only one zero. But thanks you for your information.