+ Reply to Thread
Results 1 to 4 of 4

inserting data from a row to a cell, when the row number is specified by a formula in a cell

  1. #1
    jbyrd@burkhartdental.com
    Guest

    inserting data from a row to a cell, when the row number is specified by a formula in a cell

    in cell H3, I have a match formula that searches my spreadsheet for a
    string of information. when it finds the row with the matching string
    it displays the row number in cell H3 example '63'. In cell C3, I want
    to display the full text that displays in the row specified in cell H3
    '63'. I can do that by simply entering =r63 in the cell C3. I want
    the info to display, what I would like to do is to automate this so I
    do not manually have to enter =R63,for each cell. example of my
    formulas

    in Cell H3 the formula is =MATCH(I2,(Q$1:Q$30001),0) the result is 63
    meaning row 63. in row 63 the text reads 'Byrd Brush'. I want cell C3
    to display the text 'Byrd Brush' with out having to enter =R63, is
    there a formula the will display the description of row 63
    automatically? Thanks, JB


  2. #2
    Forum Contributor
    Join Date
    09-05-2004
    Location
    Melbourne
    Posts
    193
    I assume from your example that you should be able to use a VLOOKUP to accomplish this since your MATCH type is '0'.

    =VLOOKUP(I2,Q$1:R$30001,2,FALSE)

    Cheers!

  3. #3
    Max
    Guest

    Re: inserting data from a row to a cell, when the row number is specified by a formula in a cell

    One way is to use INDEX()

    Put in C3: =INDEX(R:R,H3)
    where in H3 is: =MATCH(I2,(Q$1:Q$30001),0)

    Perhaps directly,

    Put in C3: =INDEX(R:R,MATCH(I2,(Q$1:Q$30001),0))

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    <jbyrd@burkhartdental.com> wrote in message
    news:1104970181.674161.77450@f14g2000cwb.googlegroups.com...
    > in cell H3, I have a match formula that searches my spreadsheet for a
    > string of information. when it finds the row with the matching string
    > it displays the row number in cell H3 example '63'. In cell C3, I want
    > to display the full text that displays in the row specified in cell H3
    > '63'. I can do that by simply entering =r63 in the cell C3. I want
    > the info to display, what I would like to do is to automate this so I
    > do not manually have to enter =R63,for each cell. example of my
    > formulas
    >
    > in Cell H3 the formula is =MATCH(I2,(Q$1:Q$30001),0) the result is 63
    > meaning row 63. in row 63 the text reads 'Byrd Brush'. I want cell C3
    > to display the text 'Byrd Brush' with out having to enter =R63, is
    > there a formula the will display the description of row 63
    > automatically? Thanks, JB
    >




  4. #4
    Max
    Guest

    Re: inserting data from a row to a cell, when the row number is specified by a formula in a cell

    Another play is to try INDIRECT() ?

    Put in C3: =INDIRECT("R"&H3)
    where in H3 is: =MATCH(I2,(Q$1:Q$30001),0)

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



+ 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