Hi Everyone.
I have struggled for about a day now to solve this but have not found the correct answer yet.
I think I am close however.
I have this array formula:
=IF(ISERROR(MATCH(1,([TIMS.xlsx]Sheet1!$A$453:$A$7292=$H84)*([TIMS.xlsx]Sheet1!$B$453:$B$7292=J$83),0)),"",MATCH(1,([TIMS.xlsx]Sheet1!$A$453:$A$7292=$H84)*([TIMS.xlsx]Sheet1!$B$453:$B$7292=J$83),0))
which works fine.
The row numbers in red above, however, need to be dynamic.
I have tried referring to cells with a changeable reference with the indirect function but they do not seem to operate properly inside the array.
Such as:
IF(ISERROR(MATCH(1,(INDIRECT(F78&F79&H77)&":"&INDIRECT(F78&F79&I77)=$H84)*(INDIRECT(F78&F79&H78)&":"&INDIRECT(F78&F79&I78)=I$83),0)),"",MATCH(1,(INDIRECT(F78&F79&H77)&":"&INDIRECT(F78&F79&I77)=$H84)*(INDIRECT(F78&F79&H78)&":"&INDIRECT(F78&F79&I78)=I$83),0))
I have also generated a dynamic cell with the address function and then used indirect to refer it.
Somehow this looks more hopeful as the array formula behaves differently.
Such as:
=IF(ISERROR(MATCH(1,(INDIRECT(F76)....
Am I on the right track?
Bookmarks