Finding Value

[Ian1712]

Morning,

Long time reader, first time poster.

Looking for some help with a formula, or If it is even possible.

I have a sheet that is a summary overview screen. It pullsdata using drop down list from other sheets to display financial performance. The summary screen shows the 11 key indicatorson how well the store is performing.
As part of the summary I have a section that displays thearea of success (Highest value (using =Max) and then 3 opportunities areas(using=Small).
While this formula works perfect for finding the 4 values, Ineed to display in a cell next to it what heading/table heading it is from. Forexample, if cell C21 returns 10,000 I need cell C22 to return what line that isfrom?
Any suggestions

 

[Yongle]

Perhaps Index & Match
Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
9​
10​
11​	Sales	Values
12​	Shop1	5000​
13​	Shop2	4000​
14​	Shop3	10000​
15​	Shop4	7000​
16​	Shop5	4250​
17​
18​
19​
20​
21​			10000​	=MAX(B12:B16)
22​			Shop3	=INDEX(A12:A16,MATCH(MAX(B12:B16),B12:B16,0))
23​

Sheet: Sheet5

 

[Ian1712]

Perhaps Index & Match
Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​	Sales	Values
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​	Shop1	5000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​	Shop2	4000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​	Shop3	10000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​	Shop4	7000​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​	Shop5	4250​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​			10000​	=MAX(B12:B16)
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​			Shop3	=INDEX(A12:A16,MATCH(MAX(B12:B16),B12:B16,0))
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​

<tbody>
</tbody>

Sheet: Sheet5

<tbody>
</tbody>

Thanks, that works a treat. Assuming you just replace 'Max' with 'Small' to find the lowest number?

Thank again.

 

[Yongle]

The principle is the same

use appropriate FORMULA to get the value required
use that FORMULA as first attribute inside MATCH to get its position in the range
use that position as the row attribute inside INDEX

ensure that range sizes are identical

 

[Ian1712]

The principle is the same

use appropriate FORMULA to get the value required
use that FORMULA as first attribute inside MATCH to get its position in the range
use that position as the row attribute inside INDEX

ensure that range sizes are identical

Thanks, Struggling with it a little. Can get the MAX function to work no problem. With the small function it says too few arguments made. The Development sections needs the lowest 3 numbers and then the heading they fall under. Does that make sense. Been looking at this all day so think im just missing a " or ' somewhere.

Thanks

Store 1
Success
Sales	1,571
Development
	618
	44
	65

<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <tbody> </tbody>