Thread: Error with Match and Index formulas via a validation cell

excelproblem.xlsx

Hi all,

I am having trouble with my workbook (attached).
I have set out my problem in the workbook.

For some reason I can only lookup with a match and index reference with the first 4 dates of my data validation.
Every later date from 11/11/06 is showing up #N/A errors.

I hope I have explained enough in the example workbook.
I cannot put my finger on the problem. Help would be much appreciated.

Cheers Ollie

Try changing the formula in Column 8 to return a 0 instead of a blank if error occurs...

=IF(ISERROR(IF($E$8>0,$E$8,IF(INDEX(Readings!$E$12:$GT$500,MATCH($E$4,Readings!$B$12:$B$500,0),MATCH(Grazing[[#This Row],[Column2]],Readings!$E$11:$GT$11,0))<=0,$E$7,(INDEX(Readings!$E$12:$GT$500,MATCH($E$4,Readings!$B$12:$B$15,0),MATCH(Grazing[[#This Row],[Column2]],Readings!$E$11:$GT$11,0)))))),0,IF($E$8>0,$E$8,IF(INDEX(Readings!$E$12:$GT$15,MATCH($E$4,Readings!$B$12:$B$15,0),MATCH(Grazing[[#This Row],[Column2]],Readings!$E$11:$GT$11,0))<=0,$E$7,(INDEX(Readings!$E$12:$GT$15,MATCH($E$4,Readings!$B$12:$B$500,0),MATCH(Grazing[[#This Row],[Column2]],Readings!$E$11:$GT$11,0))))))

also, if you are using Excel2007 or later, you can shorten it to:

=IFERROR(IF($E$8>0,$E$8,IF(INDEX(Readings!$E$12:$GT$500,MATCH($E$4,Readings!$B$12:$B$500,0),MATCH(Grazing[[#This Row],[Column2]],Readings!$E$11:$GT$11,0))<=0,$E$7,(INDEX(Readings!$E$12:$GT$500,MATCH($E$4,Readings!$B$12:$B$15,0),MATCH(Grazing[[#This Row],[Column2]],Readings!$E$11:$GT$11,0))))),0)

You can also fix the formula in Column 5 to give blank with Column 7 is blank instead of error:

=IF(Grazing[[#This Row],[Column7]]="","",IF($I$14>Grazing[[#This Row],[Column11]],$E$13+(Grazing[[#This Row],[Column7]]*Grazing[[#This Row],[Column8]]),IF(Grazing[[#This Row],[Column11]]=$I$14,$E$13,IF((INDEX(Readings!$E$12:$GT$500,MATCH(Grazings!$E$4,Readings!$B$12:$B$500,0),MATCH(Grazing[[#This Row],[Column1]],Readings!$E$11:$GT$11,0)))+(($I$14-$E$4)*Grazing[[#This Row],[Column8]])<500,0,(INDEX(Readings!$E$12:$GT$500,MATCH(Grazings!$E$4,Readings!$B$12:$B$500,0),MATCH(Grazing[[#This Row],[Column1]],Readings!$E$11:$GT$11,0)))+(($I$14-$E$4)*Grazing[[#This Row],[Column8]])))))

Thank you NBVC your middle piece of coding has cleaned a bit up.

..but..

There still seems to be an issue where, when the date is set to 5/12/06 or higher, the respective cells in column8 do not pick up the individual figures from the "Readings" sheet that are matched and indexed with the date in E4 in the "Grazings" sheet and the respective cells in column 2.
Cells with a reading in column 8 on higher date selections seem to pickup the average reading when they have there own individual readings that should be in that place??

I hope this makes sense..

added fresh sheet with NBVC code:
excelproblem.xlsx

Do you mean date 12/5/2006 because your Readings sheet doesn't have a 5/12/06.... and with 12/5/2006 I get results.

And you can change formula in I17 also for Excel 2007 to:

=IFERROR(IF($E$8>0,$E$8,IF(INDEX(Readings!$E$12:$GT$500,MATCH($E$4,Readings!$B$12:$B$500,0),MATCH(Grazing[[#This Row],[Column2]],Readings!$E$11:$GT$11,0))<=0,$E$7,(INDEX(Readings!$E$12:$GT$500,MATCH($E$4,Readings!$B$12:$B$15,0),MATCH(Grazing[[#This Row],[Column2]],Readings!$E$11:$GT$11,0))))),"")

Attached is what I get when I enter December 5th, 2006