I created an alternative way for the user to capture the data. I may well return to trying to resolve the multiple criteria, index, match but for now I am afraid the time has passed.
Original post
I am attempting to create a index match across multiple sheets in a single workbook, where there are 3 criteria to match based on a source document and the return value is a cell reference:
I can get it to work when I write the formula for the single sheet look up and is is as follows;
=IF(ISERROR(MATCH($A16,'Day1'!$A:$A,0)),"no",INDEX('Day1'!$M:$M,MATCH(All!$C16,'Day1'!$D:$D,0)))
This works I want to reference this across all the sheets at one time to return the the values as is, so I created a list with the tab headings on and called it mysheet and then have tried to insert it into the formula using the indirect function but am failing to identify the exact reason it fails;
=IF(ISERROR(MATCH($A17,indirect(""'&Mysheets&"'!$A:$A,0)),"no",INDEX(Indirect(""'&Mysheets"'!$M:$M,MATCH(All!$C17,indirect(""'&Mysheets&"!$D:$D,0)))
the error reference highlights the " " '
I am now starting to doubt if this is possible.
Thanks in advance to any replies.
Bookmarks