Combination of if and index

[nskatoch]

Here is the formula I used which contains combination of IF and INDEX

=IF(STREAM!E5:E54>0,INDEX(STREAM!F5:F54,P1,1),INDEX(STREAM!E5:E54,P1,1))

In this, I have faced the problem that only one condition returns output, other returns 0.

If STREAM!E5:E54 is true then it returns the value of INDEX(STREAM!E5:E54,P1,1)

If STREAM!E5:E54 is false then it returns the value 0.

 

[Special-K99]

It would be better to descrive in words what you're trying to achieve and your expected output format.

This is an array formula - there is no mention you have entered this as an array formula

Why don't you shorten it to

=INDEX(E5:F54,P1,IF(STREAM!E5:E54>0,2,1))
Array formula, use Ctrl-Shift-Enter

 

[nskatoch]

Thanks sir
But this formula not works.

I want if STREAM!E5:E54>0 then it returns its own value i.e. value of STREAM!E5:E54 othrwise it returns the value of STREAM!F5:F54 in current sheet with condition value of P1 changing 1 to 54.
but the formula returns the value of STREAM!E5:E54 in first condition and when the cell of STREAM!E5:E54 blanks it returns zero not the value of STREAM!F5:F54.

 

[Special-K99]

Please supply some sample data and expected output.

 

[nskatoch]

ok sir

SHEET NAME: STREAM

	E	F	G	H	I
5	ENGLISH
6		FRENCH
‘’
53	ENGLISH
54		GERMAN

<tbody>
</tbody>

CURRENT SHEET NAME: RESULT
[TABLE="align: left"]
<tbody>[TR]
[TD]
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD] ROLL NO.:
[/TD]
[TD] 1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]FORMULA=??
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]








IN RESULT SHEET WHEN ROLL NO.1 i.e. (P1=1) STREAM!E5:E54>0 then it returns "ENGLISH"
when ROLL NO.2 i.e. (P1=2) STREAM!E5:E54>0 (Now STREAM!E6 is blank) then it should return STREAM!F5:F54 i.e. "FRENCH".

 

[Special-K99]

Based on your data the value in P1 is irrelevant as the data is either in coluimn E or column F and never both.
If E is blank return the value in F, if F is blank then return E.
It doesnt matter what the value is in P1

Try this

in M5
=IF(INDEX(Stream!E$5:F$54,ROW()-4,1)="",INDEX(Stream!E$5:F$54,ROW()-4,2)&"",INDEX(Stream!E$5:F$54,ROW()-4,1)&"")
and copy down the column

 

[nskatoch]

Thanks sir, but it not works.

When value of P1 changing from 1 to 50, it returns only values on P1=1 ,but when P1 changed to 2 then returns the same value.

 

[Special-K99]

Try

IF(INDEX(STREAM!E1:E54,P1,1)<>"",INDEX(STREAM!E1:E54,P1,1),INDEX(STREAM!F1:F54,P1,2))

 

[nskatoch]

Thanks sir but it not works

When the value of P1=1 ,3,5.. it returns the value of STREAM!E5:E54 BUT
when the value of P2=2,4,6 .. it returns value #REF !

 

[Special-K99]

What the hell is P2 ?????

You have mentioned P2 before AT ALL!!
Am I supposed to guess what cells you are using?