+ Reply to Thread
Results 1 to 3 of 3

Troubles with ROW

  1. #1
    Registered User
    Join Date
    07-18-2007
    Location
    Seattle, WA, USA
    MS-Off Ver
    2016
    Posts
    42

    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.
    -Aikorei

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824

    Re: Troubles with ROW

    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!

  3. #3
    Registered User
    Join Date
    07-18-2007
    Location
    Seattle, WA, USA
    MS-Off Ver
    2016
    Posts
    42

    Re: Troubles with ROW

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1