Troubles with ROW

I've got a formula that works just fine as follows:

Please Login or Register  to view this content.

It works beautifully and returns exactly the value on the other page I want. However, what I actually need here is the row of this cell, but I get an error when I try to add the ROW() function in (as follows):

Please Login or Register  to view this content.

It's not just a simple error displayed in the cell, it won't even let me input the formula and move on. In other words, I'm required to correct the formula so it can calculate properly. The only difference here is the addition of the ROW() function.

Am I missing something about how this function operates?

(I can provide a file if necessary)

Thanks.

Hi Aikorei,

The ROW() function returns the row number of a cell reference, e.g.

=ROW(A7) returns 7.

=ROW() returns the row that the formula itself resides in.

Because VLOOKUP returns a value, your ROW() function is simply trying to determine the row of a cell value, not the cell where the value resides. Instead, this may work:

=MATCH(A2,INDIRECT(C2&"!$"&VLOOKUP(D2,$J$2:$N$6,5,FALSE)&"$4:$"&VLOOKUP(D2,$J$2:$N$6,5,FALSE)&"$34"),0)

Note: Your INDIRECT function is looking at rows 4:34, so this MATCH function will return the relative position within that range. A result of 1 means the match was found in the first row of the range, row 4. If you want the result to be the actual spreadsheet row of the matched value, add "+3" to the end of the function I provided.

=MATCH(A2,INDIRECT(C2&"!$"&VLOOKUP(D2,$J$2:$N$6,5,FALSE)&"$4:$"&VLOOKUP(D2,$J$2:$N$6,5,FALSE)&"$34"),0)+3

This will change a result of 1 (first row in range 4:34 match) to 4 (physical row where match was found).

Hope that helps!

That does help very much, actually.Thank you for the response!