# 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

## Files

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

=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

=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!