IFMATCH formula suddenly stopped working

[AJExcelStar]

I have been using one IFMATCH formula to generate a report since a month with complete success, but suddenly it has stopped working. I am completely puzzled as nothing has changed between the last report and this one I am trying to generate now.

There are two separate worksheets in two different tabs in the same Excel file. One tab is called Old and the formula is used in the other tab on column G.

The logic necessary to populate G column on the report is - "If the respective cells for column A, E, F and H in Old report and new report ARE THE VERY SAME, what ever was there in the Old report in the Column G in that row should get populated in the new report Column G. Otherwise it should be populated with blank."

However, 100s of new rows are added each week. So the Old report will not be having those new rows actually. This means all newly added rows should be populated with blank in the G Column of the new report.


Formula I was using till now and that was working perfectly till now is =IFERROR(INDEX(Old!$G$1:$G$6498,MATCH(A2&"^^"&E2&"^^"&F2&"^^"&H2,Old!$AG$1:$AG$6500,0)),"")

Old has 6498 rows and columns up to AG. The new report, two new rows have been added and so rows are 6500 and columns are same that is up to AG.

I am getting blanks in all the rows in column G whereas earlier it would populate with either blank, or Yes, or No as per the logic.


Please can you suggest what is going wrong now?

 

[Faseeh]

Hi AJExcelStar,


I think you can find the error if you break down the formula:


Firstly unwrap the IFERROR, it would be like:


=INDEX(Old!$G$1:$G$6498,MATCH(A2&"^^"&E2&"^^"&F2&"^^"&H2,Old!$AG$1:$AG$6500,0))


Now the MATCH() portion first:


MATCH(A2&"^^"&E2&"^^"&F2&"^^"&H2,Old!$AG$1:$AG$6500,0)


Here you are looking for a criteria in the Range AG1:AG6500, this will generate a number between 1 to 6500 that will be feed to INDEX() i.e:


=INDEX(Old!$G$1:$G$6498,[Any Number between 1-6500])


But here your index function can only look between G1:G6498 i.e it has an array of of 6498 element, when looked for 6499th & 6500th element, it will give Error for , hence IFERROR will give you a Blank Cell!!


Suggestion: Just use this corrected one:


`=IFERROR(INDEX(Old!$G$1:$G$6500,MATCH(A2&"^^"&E2&"^^"&F2&"^^"&H2,Old!$AG$1:$AG$6500,0)),"")


Hope that helps,


Faseeh

 

[AJExcelStar]

Hey Faseeh,


That was an excellent suggestion, I could pinpoint my error that way and now it is working perfectly! Thanks a ton for your help!


Best regards


AJ