Index Match with IF statements

[matthewlouis]

Hi All, trying to do nested Index and Match with IF statements . . . 6 nested IF's to be exact.

When I put =INDEX(IF(C8="Confirmed Sideways",'D1'!$Q$1:$Q$100),MATCH(F8,'D1'!$S$1:$S$100,0)), it works great, it returns what I need.

However, when I add the next IF statement:

=INDEX(IF(C8="Confirmed Sideways",'D1'!$Q$1:$Q$100),MATCH(F8,'D1'!$S$1:$S$100,0)), INDEX(IF(C8="Suspect Sideways",'D1'!$Q$1:$Q$100),MATCH(F8,'D1'!$S$1:$S$100,0))

I get #VALUE . I know it must have something to do with brackets, etc...

Can anyone see what I am doing wrong when I add the next IF statement?

Thanks in advance!

 

[arthurbr]

Perhaps
=INDEX(IF(OR(C8="Confirmed Sideways",C8="Suspect Sideways")'D1'!$Q$1:$Q$100),MATCH(F8,'D1'!$S$1:$S$100,0))

Wouldn't the following be easier?

=IF(OR(C8="Confirmed Sideways",C8="Suspect Sideways"),INDEX('D1'!$Q$1:$Q$100,MATCH(F8,'D1'!$S$1:$S$100,0)),"")

 

[matthewlouis]

Yes, but what I failed to mention (sorry) is the data from Confirmed Sideways and Suspect Sideways are in different columns.

What I have is . . .

=IF(C31="Confirmed Sideways",INDEX('D1'!$Q$1:$Q$100,MATCH(F31,'D1'!$S$1:$S$100,0)),IF(C31="Suspect Sideways",INDEX('D1'!$L$1:$L$100,MATCH(F31,'D1'!$N$1:$N$100,0)),IF(C31="Confirmed Bearish",INDEX('D1'!$G$1:$G$100,MATCH(F31,'D1'!$I$1:$I$100,0)),IF(C31="Suspect Bearish",INDEX('D1'!$B$1:$B$100,MATCH(F31,'D1'!$D$1:$D$100,0)),IF(C31="Confirmed Bullish",INDEX('D1'!$AA$1:$AA$100,MATCH(F31,'D1'!$AC$1:$AC$100,0)),IF(C31="Suspect Bullish",INDEX('D1'!$V$1:$V$100,MATCH(F31,'D1'!$X$1:$X$100,0))))))))

This works . . . but NOW here is the problem . . . I cannot understand why I am getting the right number returned in one cell but not others (I get #N/A) when the exact same column of data is being used.

[TABLE="width: 598"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Sector[/TD]
[TD]XLRE[/TD]
[TD]Suspect Bullish[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Sector[/TD]
[TD]XLU[/TD]
[TD]Suspect Bullish[/TD]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]


Same exact formula for Suspect Bullish, same column of numbers it's searching . . . and it gives #N/A while 18 is correct. I have checked and the cells are data, not text, or it wouldn't give the #N/A error.

Any idea?

Thanks for your help!

 

[arthurbr]

Any trailing or preceding spaces in some strings? perhaps add the TRIM function in your lookup cell in the MATCH function?

 

[matthewlouis]

Thanks, arthurb, that worked!!