Index-Match-Match: One Match Returning N/A

[PipBoy808]

Hi all. I'm using an Index-Match-Match and one of the Match functions consistently returns an N/A for me. In the attached file I've highlighted the Match values that should in theory return '1' for the row reference, but is returning an N/A instead.

Can anyone see why this is happening? Thanks in advance!

 

[bosco_yip]

Try,

=INDEX($B$26:$N$28,MATCH(0+LEFT($A18,4),$A$26:$A$28,0),MATCH(B$14,$B$25:$N$25,0))

 

[PipBoy808]

That works! But why? Is the '0' telling Excel that the LEFT value is a number?

 

[bosco_yip]

That works! But why? Is the '0' telling Excel that the LEFT value is a number?

The Left function return a Text value

So, 0+LEFT(....) to convert Text value to Numeric value.

 

[pecoflyer]

That works! But why? Is the '0' telling Excel that the LEFT value is a number?

Any arithmetic operation performed on a number that is a text string will coerce it to be a number
="2"+"3" will return 5
="2" remains a text string