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


    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.

  • 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


    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

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!