How can I have an other lookup_array

0
votes

I want to also look up the sheets:

  • Decl. M2, 17
  • Decl. M3, 17
  • Decl. M4, 17
  • Decl. M5, 17
  • Decl. M6, 17
  • Decl. M7, 17
  • Decl. M8, 17
  • Untill.... M12 (same for the sheets TV M1, 2, 3 etc.)

  1. First Formula which is in cell J11 of the sheet called "Decl. M1, 17":

    =IF(ISERROR(MATCH(""&MID(B11;FIND("-";B11;1)+1;11)&"";'TV M1, 17'!B:B;0));"Not Found";"Match"))

How can I have another lookup_array with the sheetname: 'TV M2, 17'!B:B;0, M3, M4, etc., until M12.

  1. Second Formula which is in cell O11 of the sheet called "TV M1, 17":

    =IF(ISERROR(MATCH(""&MID(B11;FIND("-";B11;1)+1;11)&"";'Decl. M1, 17'!B:B;0));0;1))

The same here, how can I have another lookup_array with the sheetname: 'Decl. M2, 17'!B:B;0, M3, M4, etc., until M12.

BONUS: Also LookUp one other workbook from workbook 2017 lookup the whole workbook of 2018 OR if possible exactly the same sheets (but then in workbook 2018) as described above.

1
excelexcel-formula

1 Answers

0
votes

If I understand your question directly, you can use INDIRECT for this.

Replace this text in your formula:

'TV M1, 17'!B:B

With something like this (just an example):

INDIRECT("'TV M"&ROW()&", 17'!B:B")

This returns 'TV M1, 17'!B:B in row 1, 'TV M2, 17'!B:B in row 2, etc...

Of course, doesn't have to be based off rows, can be based of anything you want.

WARNING!!! INDIRECT is useful for cases like this but it is also dangerous because if you ever change the names of the sheets, it won't automatically update like you would expect, you would have to manually change the names inside INDIRECT as well.