Based on location return a highest and least values and absolute, relative addresses of cells.

[mmr1]

Hi,

I required a formula help for return a values based on lookup location Highest and least values of amount and return a cell addresses of that absolute and relatives in column F, Cells F4 to F9.


Sample enclosed a for the reference.


Thanks,

Book2
	A	B	C	D	E	F
1
2
3	Items	Amount	Location		Lookup location	Area-3
4	abc	1250	Area-1		return a highest value of amount
5	cbc	1540	Area-2		return a least value of amount
6	vbc	1652	Area-3		Address of lookup item Highest Value(absolute)
7	dfg	1474	Area-1		Address of lookup item Highest Value (relative)
8	fgk	656	Area-2		Address of lookup item least Value(absolute)
9	qwe	-21	Area-3		Address of lookup item least Value (relative)
10	asd	65	Area-1
11	fdv	142	Area-2
12	sxz	656	Area-3
13	asw		Area-1
14	aqw	656	Area-2
15	poi	789	Area-3
Sheet1

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1
2
3	Items	Amount	Location		Lookup location	Area-3
4	abc	1250	Area-1		return a highest value of amount	1652
5	cbc	1540	Area-2		return a least value of amount	-21
6	vbc	1652	Area-3		Address of lookup item Highest Value(absolute)	$B$6
7	dfg	1474	Area-1		Address of lookup item Highest Value (relative)	B6
8	fgk	656	Area-2		Address of lookup item least Value(absolute)	$B$9
9	qwe	-21	Area-3		Address of lookup item least Value (relative)	B9
10	asd	65	Area-1
11	fdv	142	Area-2
12	sxz	656	Area-3
13	asw		Area-1
14	aqw	656	Area-2
15	poi	789	Area-3
16
Clubs

Cell Formulas
Range	Formula
F4	F4	=MAXIFS(B4:B15,C4:C15,F3)
F5	F5	=MINIFS(B4:B15,C4:C15,F3)
F6	F6	=ADDRESS(MATCH(F4,B1:B15,0),2,1)
F7	F7	=ADDRESS(MATCH(F4,B1:B15,0),2,4)
F8	F8	=ADDRESS(MATCH(F5,B1:B15,0),2,1)
F9	F9	=ADDRESS(MATCH(F5,B1:B15,0),2,4)

 

[mmr1]

Thanks a lot Fluff,

for your quick and prompt solution.

 

[Fluff]

You're welcome & thanks for the feedback.