Return Value from range based on lowest score

[RJB78]

Hello - looking for a formula that will return a value based on 2 variables. Example below. When I type orange, I want the formula to look through the data and return the store name with the lowest price oranges. If I type apple, I want the store with the lowest price apples, etc. Thank you in advance.

Find:	Orange
Store:	FORMULA HERE

Store 1	Orange	$1
Store 2	Orange	$2
Store 3	Orange	$3
Store 1	Apple	$6
Store 2	Apple	$7
Store 3	Apple	$4
Store 1	Banana	$10
Store 2	Banana	$11
Store 3	Banana	$9

 

[Holger]

This should work, although I'm sure there are even ways.....

Duplicate.xlsx
	AC	AD	AE
30	Find:	Apple
31	Store:	Store 3
32
33	Store 1	Orange	$1
34	Store 2	Orange	$2
35	Store 3	Orange	$3
36	Store 1	Apple	$6
37	Store 2	Apple	$7
38	Store 3	Apple	$4
39	Store 1	Banana	$10
40	Store 2	Banana	$11
41	Store 3	Banana	$9
Sheet1

Cell Formulas
Range	Formula
AD31	AD31	=INDEX(AC33:AE41,MATCH(MIN(FILTER((AC33:AE41),(AD33:AD41)=AD30)),AE33:AE41),1)

 

[Holger]

@RJB78 here is an update with a second option:

Duplicate.xlsx
	AC	AD	AE	AF
30	Find:	Apple
31	Option1:	Store 3	Option2:	Store 3
32
33	Store 1	Orange	$1
34	Store 2	Orange	$2
35	Store 3	Orange	$3
36	Store 1	Apple	$6
37	Store 2	Apple	$7
38	Store 3	Apple	$4
39	Store 1	Banana	$10
40	Store 2	Banana	$11
41	Store 3	Banana	$9
Sheet1

Cell Formulas
Range	Formula
AD31	AD31	=INDEX(AC33:AE41,MATCH(MIN(FILTER((AC33:AE41),(AD33:AD41)=AD30)),AE33:AE41),1)
AF31	AF31	=LET( input1, AC33:AE41, input2, AD33:AD41, input3, AE33:AE41, field, AD30, input4, FILTER(input1, input2 = field), input5, SMALL(input4,1), input6, FILTER(input1, input3 = input5), Result, VSTACK(HSTACK(DROP(input6,,-2))), Result )

 

[Cubist]

Another option:

Book1
	A	B	C
1	Find:	Apple
2	Store:	Store 3
3
4
5	Store 1	Orange	1
6	Store 2	Orange	2
7	Store 3	Orange	3
8	Store 1	Apple	6
9	Store 2	Apple	7
10	Store 3	Apple	4
11	Store 1	Banana	10
12	Store 2	Banana	11
13	Store 3	Banana	9
Sheet1

Cell Formulas
Range	Formula
B2	B2	=FILTER(A5:A13,(B5:B13=B1)*(C5:C13=MINIFS(C5:C13,B5:B13,B1)),"")

 

[Eric W]

Another option:

Book1
	A	B	C
1	Find:	Apple
2	Store:	Store 3
3
4	Store 1	Orange	$1
5	Store 2	Orange	$2
6	Store 3	Orange	$3
7	Store 1	Apple	$6
8	Store 2	Apple	$7
9	Store 3	Apple	$4
10	Store 1	Banana	$10
11	Store 2	Banana	$11
12	Store 3	Banana	$9
13
14
15
Sheet2

Cell Formulas
Range	Formula
B2	B2	=INDEX(SORT(FILTER(A4:C15,B4:B15=B1),3),1,1)

If you change it like this:

=INDEX(SORT(FILTER(A4:C15,B4:B15=B1),3),1,{1,3})

it will also show the amount.