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.

 

[nskatoch]

Sorry sir it is P1 not P2

I mean it does not returns the value of STREAM!F1:f54 when P1=2 or 4..

 

[Special-K99]

My mistake
it should be

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

see end of formula 1 not 2 as previously stated.

 

[nskatoch]

Sir I tried it, but it not works.
Same problem

 

[Special-K99]

With your data as you suggested earlier (ENGLISH in E5) this works

=IF(INDEX(Stream!E5:E54,P1,1)<>"",INDEX(Stream!E5:E54,P1,1),INDEX(Stream!F5:FI54,P1,1))

 

[nskatoch]

Thanks a lot.
It runs perfectly