INDEX and MATCH confusing error (?)

[matthewlouis]

I have the following INDEX and MATCH formula . . .

=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))))))))

My problem is it works randomly. I get the correct number in some cells but others I get #N/A) when the exact same data in that same column is being accessed. For example:

[TABLE="class: cms_table"]
<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 . . . 18 is correct for XLU but I can't understand why XLRE returns the error. I have checked all cells and they are all numbers / data, not text.

Any ideas?

Thanks for your help!

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...ch-if-statements-post5239984.html#post5239984

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).