Index Match producing zeros and #N/A errors

I have the following equation.

=INDEX('Fill in Data'!$S$2:$S$62458,MATCH(1,(('Fill in Data'!$j$2:$j$62458=$A$5)*('Fill in Data'!$S$2:$S$62458=MAX(IF('Fill in Data'!$M$2:$M$62458=$A$5,'Fill in Data'!$S$2:$S$62458)))),0))

It produces a #N/A error when it encounters a M and zeros when it encounters a blank cell. I need this equation to produce a blank in both situations.

Any ideas on how I can this?

Jeff

Re: Index Match producing zeros and #N/A errors

Quote from azumi;732351

I think because you using MAX function, it just calculate numbers. Maybe more clear if you try to upload your small sample workbook and switch sensitive data with fake data but enough explain the problem.

I simplified the equation to...

=INDEX($B$5:$B$15,MATCH(1,(($A$5:$A$15=E5)*($B$5:$B$15=MAX(IF($A$5:$A$15=E5,$B$5:$B$15)))),0))

I also added a sample spread sheet. I need the M (missing) and Blanks to be blanks.

Thanks for any help!

Jeff

Re: Index Match producing zeros and #N/A errors

Quote from holycow;732374

How about

=IFERROR(INDEX($B$5:$B$15,MATCH(1,(($A$5:$A$15=$E5)*(ISNUMBER($B$5:$B$15))*($B$5:$B$15=MAX(IF($A$5:$A$15=$E5,$B$5:$B$15)))),0)),"")

CSE entered

Thanks for the help again! I greatly appreciate it. Almost done with this project.

Jeff