Constructing a general indicator function (without using nes

[RET79]

I would like to construct an indicator function, however, preferably not with millions of nested if functions. I need it for situations like this:

YV VA OE TM Indicator
9 YV
4 YV
5 YV
6 YV
7 YV
6 YV
7 YV
23 VA
3 VA
5 VA
5 VA
3 YV
6 OE
6 OE
3 OE
2 VA
2 TM
1 YV
2 TM
2 TM
1 TM



A nested IF function works here for me to create the indicator. However, if I had 40 fields instead of 4 then I couldn't use this method. I am guessing that using a correct lookup function would do it but I am not sure how to go about it. Thanks.

[RET79]

YV VA OE TM Indicator
3 0 0 0 YV
4 0 0 0 YV
5 0 0 0 YV
6 0 0 0 YV
7 0 0 0 YV
6 0 0 0 YV
7 0 0 0 YV
0 23 0 0 VA
0 3 0 0 VA
0 5 0 0 VA
0 5 0 0 VA
3 0 0 0 YV
0 0 6 0 OE
0 0 6 0 OE
0 0 3 0 OE
0 2 0 0 VA
0 0 0 2 TM
1 0 0 0 YV
0 0 0 2 TM
0 0 0 2 TM
0 0 0 1 TM

[RET79]

Sorry about this, they are supposed to be 5 columns with YV, VA, OE, TM, and Indicator being the column headings.

[Mark W.]

With your data entered into cells A1:E22 enter the array formula...

{=INDEX($A$1:$D$1,1,MAX(($A2:$D2<>0)*COLUMN($A:$D)))}

...into cell E2 and copy down to cell E22.

Note: Array formulas must be entered using the Shift+Control+Enter key combination. For more on array formulas consult the Excel Help topic for "About array formulas and how to enter them".

[ This Message was edited by: Mark W. on 2002-03-21 09:17 ]

[Aladin Akyurek]

On 2002-03-21 09:07, RET79 wrote:
YV VA OE TM Indicator
3 0 0 0 YV
4 0 0 0 YV
5 0 0 0 YV
6 0 0 0 YV
7 0 0 0 YV
6 0 0 0 YV
7 0 0 0 YV
0 23 0 0 VA
0 3 0 0 VA
0 5 0 0 VA
0 5 0 0 VA
3 0 0 0 YV
0 0 6 0 OE
0 0 6 0 OE
0 0 3 0 OE
0 2 0 0 VA
0 0 0 2 TM
1 0 0 0 YV
0 0 0 2 TM
0 0 0 2 TM
0 0 0 1 TM

Supposing that the above data start in A1,

in E2 enter and drag down:

=IF(MAX(A2:D2),INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)),"")

Is this what you are asking for?

Aladin