# Finding Value

#### Ian1712

##### New Member
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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

Last edited:
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.

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

Last edited:
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>

Replies
1
Views
206
Replies
29
Views
1K
Replies
2
Views
203
Replies
8
Views
423
Replies
1
Views
980

1,206,718
Messages
6,074,505
Members
446,072
Latest member
OrangeYellow

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back