Formula to return store name with highest sales (subject to restrictions)

[JamesonMH]

Hi there,

To simplify, I have 4 stores and I want to return the store name with the highest sales*. But I want to exclude store names with a numbered suffix (i.e. only store names that end with a letter suffix should be included). So in the table below, I'd want the formula to return Store C, since Store 6 doesn't meet my requirement of ending with a letter suffix.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store [/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]$50,000[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]29,000[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]72,000[/TD]
[/TR]
[TR]
[TD]Store 6[/TD]
[TD]81,000[/TD]
[/TR]
</tbody>[/TABLE]

I tried INDEX/MATCH/MAX variations to no avail. I don't want to rely on filtering or any VBA - a formula is desired here. Excel 2013.

Many thanks.

James

 

[sheetspread]

You can write a max if formula with istext() and right() functions.

 

[Aladin Akyurek]

Book1
	A	B	C	D
1	Store	Sales		highest sales
2	Store A	50,000		Store C
3	Store B	29,000		Store F
4	Store C	72,000
5	Store 6	81,000
6	Store F	72,000
7	Store 100	72,000
Sheet1

In D2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$7,SMALL(IF(1-ISNUMBER(RIGHT($A$2:$A$7)+0),IF($B$2:$B$7=MAX(IF(1-ISNUMBER(RIGHT($A$2:$A$7)+0),$B$2:$B$7)),ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($D$2:D2))),"")

 

[JamesonMH]

Thanks very much Aladin, it works.

It's quite the formula, I definitely wouldn't have gotten there on my own!

 

[JamesonMH]

Thanks sheetspread. I used your suggestion to make an array formula that incorporated the above. I appreciate your help!

 

[Aladin Akyurek]

Thanks very much Aladin, it works.

It's quite the formula, I definitely wouldn't have gotten there on my own!

Glad to help.