IFMATCH formula working perfectly, suddenly not working any more

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? Actually expert member on this forum NBVC from Mississauga Ontario had helped me immensely last time about two months back to start off with this formula. I also must say, even now, this formula is working perfectly for less number of rows, say 200 rows. But with the actual report it is not working.

In that formula you show, the Index range and the the Match lookup range don't match in size. Is that a typo here or is that the formula you have. If so, fix it and report back. The formula should work for as many rows as you want.

Hi NBVC,

Thank you for your reply. Actually the index range and match look up range gets changed every week and yes, they do not match. The formula is correct and now it is working for all the 6500 rows. The error was in a typo in the contenated column AG and I could catch it after some time. Thank you for your response once more, have a lovely day ahead!

Ajoy

You should make sure that the index and match ranges match though... to avoid possible errors.

You are right, but the number of records change with each report, so it is having different ranges. So for the newly added records, the G column would be blank till it is manually filled up looking at certain information in a separate system. What you say is right, getting an error is probable if the ranges of Index and Match lookup are different. What would you suggest in such a case where number of records increases each week or sometimes even gets reduced?

You can try creating Dynamic Named Ranges.

MyTable:

=OFFSET(Old!$G$1,,counta(Old!$G:$G),COLUMNS(Old!$G$1:$AG$1))

then formula might be:

=IFERROR(INDEX(INDEX(MyTable,0,1),MATCH(A2&"^^"&E2&"^^"&F2&"^^"&H2,INDEX(MyTable,0,27),0)),"")