other than if formula

RAJESH NATH

Board Regular
Joined
May 19, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
I have below sheet, and I need result as mention below where I used if formula only. Is there any other way to solve it.

abcderesult
1​
b
3​
a
2​
c
1​
d
4​
e
5​
c

=IF(A9>0,"a",IF(B9>0,"b",IF(C9>0,"c",IF(D9>0,"d",IF(E9>0,"e")))))
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi & welcome to MrExcel.
How about
+Fluff New.xlsm
ABCDEF
1abcderesult
21b
33a
42c
51d
64e
75c
Master
Cell Formulas
RangeFormula
F2:F7F2=INDEX($A$1:$E$1,MATCH(TRUE,A2:E2>0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
In addition to Fluff's array formula, the following shows 2 alternatives.

T202008a.xlsm
ABCDEFGHI
1abcderesult
21bbbb
33aaaa
42cccc
51dddd
64eeee
75cccc
2a
Cell Formulas
RangeFormula
F2:F7F2=INDEX($A$1:$E$1,MATCH(TRUE,A2:E2>0,0))
G2:G7G2=INDEX({"a","b","c","d","e"},MATCH(0.001,A2:E2,-1))
H2:H7H2=INDEX($A$1:$E$1,MATCH(0.001,A2:E2,-1))
I2:I7I2=INDEX($A$1:$E$1,AGGREGATE(15,6,COLUMN($A2:$E2)/($A2:$E2>0),1))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Each formula has it's flaws.
+Fluff New.xlsm
ABCDEFGHI
1abcDaveeresult
2 1abba
330.01abba
42cccc
51DavedDaveDave
64eeee
705c#N/A#N/Ac
Master
Cell Formulas
RangeFormula
A2A2=""
F2:F7F2=INDEX($A$1:$E$1,MATCH(TRUE,A2:E2>0,0))
G2:G7G2=INDEX({"a","b","c","d","e"},MATCH(0.001,A2:E2,-1))
H2:H7H2=INDEX($A$1:$E$1,MATCH(0.001,A2:E2,-1))
I2:I7I2=INDEX($A$1:$E$1,AGGREGATE(15,6,COLUMN($A2:$E2)/($A2:$E2>0),1))
Press CTRL+SHIFT+ENTER to enter array formulas.


Green cells are expected results
 
Upvote 0
Also the formula in col I can break if a new column is inserted in col A
+Fluff New.xlsm
ABCDEFGHIJ
1abcDaveeresult
2 1abbb
330.01abbb
42cccDave
51DavedDavee
64eee#REF!
705c#N/A#N/ADave
Master
Cell Formulas
RangeFormula
B2B2=""
G2:G7G2=INDEX($B$1:$F$1,MATCH(TRUE,B2:F2>0,0))
H2:H7H2=INDEX({"a","b","c","d","e"},MATCH(0.001,B2:F2,-1))
I2:I7I2=INDEX($B$1:$F$1,MATCH(0.001,B2:F2,-1))
J2:J7J2=INDEX($B$1:$F$1,AGGREGATE(15,6,COLUMN($B2:$F2)/($B2:$F2>0),1))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
More robust versions of two of the formulae
+Fluff New.xlsm
ABCDEFG
1abcDaveeresult
2 1 bb
330.01aa
42 cc
51DaveDave
64ee
705cc
Master
Cell Formulas
RangeFormula
A2,D4,D2A2=""
F2:F7F2=INDEX($A$1:$E$1,MATCH(1,(A2:E2>0)*(A2:E2<>""),0))
G2:G7G2=INDEX($A$1:$E$1,AGGREGATE(15,6,(COLUMN($A$2:$E$2)-COLUMN($A$2)+1)/($A2:$E2>0)/(A2:E2<>""),1))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top