Hi there.

Im trying to use the Row function, but it's causing me some problems. My formula is pretty massive, so it'd just be cruel to post it up; but perhaps someone can help identify the problem at hand.


__


What I am trying to do is perform the equivalent of a lookup that has multiple criteria. In my case, it compares the value of one cell with all values in column F of a different worksheet, and then the value of another cell with all the values in column Z of that same other worksheet, and then return the value in column G of the row where both comparisons resulted in the same value.

__

Here is my rather scary code (please bare with me on this one):

=IF((INDEX(INDIRECT("'" & E1 & "'!" & CHAR(COLUMN() + 64) & "8:" & CHAR(COLUMN() +64) & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1)), MATCH(1, (INDIRECT("'" & E1 & "'!F8:F" & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1)  ) = INDIRECT("Z" & ROW())) * (INDIRECT("'" & E1 & "'!Z8:Z" & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1)  ) = (INDIRECT("F" & ROW() ))), 0))) = 0, "",(INDEX(INDIRECT("'" & E1 & "'!" & CHAR(COLUMN() + 64) & "8:" & CHAR(COLUMN() +64) & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1)), MATCH(1, (INDIRECT("'" & E1 & "'!F8:F" & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1)  ) = INDIRECT("Z" & ROW())) * (INDIRECT("'" & E1 & "'!Z8:Z" & ((LOOKUP(9.99999999999999E+307,INDIRECT("'" & E1 & "'!A8:A300"))) -1)  ) = (INDIRECT("F" & ROW() ))), 0))))
__

In this code E1 is a cell which contains string with the name of the sheet that is being referenced.
I have highlighted the 'seemingly problematic areas' in red. When I try to evaluate the formula step-by-step, the following happens:

When I evaluate the formula the program gets to the following stage (INDIRECT("Z" & ROW())). At this point, rather than return a numeric value of the row like it normally should, it returns the value inside of some curly-brackets. For example if it was in cell G9 it would turn into (INDIRECT("Z" & {9})). This is creating problems because when the program tries to evaluate the next step it returns the #VALUE error. Normally if i type out =INDIRECT("Z" & ROW()) there is no issue (and the ROW function doesnt evaluate into a value surrounded by curly brackets).

I assume there is a probem because my INDIRECT function is inside of a INDEX function.

If I don't use the Row function and just hard-code in a value, my formula works fine. Except I would rather have the formula automatically detect the row number (as there are hundreds of rows and columns that need to include this formula), and I will have to manually edit each one. Not to mention I am making a dashboard for a client, which if they need to add / delete rows, I will be in trouble.

Can anyone help? I've heard INDIRECT functions can't use dynamic ranges, but then how do I get around my problem?