I have a list of Chinese names in column A, in column B I pasted a formula which will look at the first character A1 and then index match to come up with the corresponding pinyin, then it looks for the 2nd character and index matches and returns the corresponding pinyin, then it looks for the third character, then the fourth but if there are only 2 or 3 characters in the name I get a #N/A error so I need this formula which is set up for the maximum of four characters to work even if there are only two or three characters. 
=INDEX('[Ch Name index.xls]Family Names'!$A$1:$C$13017,MATCH(LEFT(B6),'[Ch Name index.xls]Family Names'!$A$1:$A$13017,0),2)&" "&INDEX('[Ch Name index.xls]Family Names'!$A$1:$C$13017,MATCH(MID(B6,2,1),'[Ch Name index.xls]Family Names'!$A$1:$A$13017,0),2)&" "&INDEX('[Ch Name index.xls]Family Names'!$A$1:$C$13017,MATCH(MID(B6,3,1),'[Ch Name index.xls]Family Names'!$A$1:$A$13017,0),2)&" "&INDEX('[Ch Name index.xls]Family Names'!$A$1:$C$13017,MATCH(MID(B6,4,1),'[Ch Name index.xls]Family Names'!$A$1:$A$13017,0),2)
I tried to add "IF(ISERROR" (I'm using EXCEL 97 so I can't use the IFERROR function) but it said there was an error in the formula.
I have attached a greatly simplified workbook that demonstrates the problem
concatenate problem.xls