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

  • Thread starter Thread starter jbyrd
  • Start date Start date
J

jbyrd

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
 
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))
 
Another play is to try INDIRECT() ?

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