Why the match returns #NA ?

[aamirsq]

Why my match is returning #N/A in this formula ?

INDEX('Project'!B:B,MATCH(IF(LEN(A5)=1,A5,left(A5)),'Project'!A:A,1))

 

[Somendra Misra]

Hi Aamirsq,

Without having a look of data it is difficult to tell. But any how try changing the third argument of MATCH function to 0 as show below.

INDEX('Project'!B:B,MATCH(IF(LEN(A5)=1,A5,left(A5)),'Project'!A:A,0))

Regards,

 

[Debraj]

Hi Aamir!

If your Project Sheet, is like below.. then your formula will work fine..

• Data should be sorted A - Z,
• And only one character should be in Column A..

 

[aamirsq]

https://docs.google.com/spreadsheets/d/146SSjJQLOHt---YSh-5d0WER55yk_9J89TYaual_HpE/edit?usp=sharing

 

[Somendra Misra]

Try below formula and enter with Just Enter.

=INDEX(Project!B:B,MATCH(IF(LEN(A3)=1,A3,LEFT(A3))+0,Project!A:A,0))

Regards,

 

[aamirsq]

What's the reason? why adding +0 and how did you figure it out

 

[Somendra Misra]

In all the cases the lenth was more than 1 so IF function was returning the result of LEFT function which was converting the number to text say "1" and in your lookup sheet they are number so #N/A error, so adding 0 will convert back the string number to number and MATCH can do the job.

Just on note the function will not work for heading 2.02. Just check it.

Regards,

 

[aamirsq]

Thanks