Offset formula

[poiu]

Hello,

The formula below returns the text "E8" (not the contents of cell E8).

=ADDRESS(MATCH("U-8",C:C,0),5,4)

Please could you tell me how to incorporate it into an offset formula to return the contents of cell G8? (i.e. move 2 columns to the right and return the value of the contents).

Thanks,

poiu

 

[Jonmo1]

Welcome to the board...

Try

=INDEX(E:E,MATCH("U-8",C:C,0))


Hope that helps.

 

[drsarao]

=INDEX(G:G,MATCH("U-8",C:C,0))

OR

If you insist on OFFSET:

=OFFSET(A1,MATCH("U-8",C:C,0)-1,6)

 

[jasonb75]

Or

=VLOOKUP("U-8",C:E,3,0)

 

[drsarao]

OP wants value from col G:

=VLOOKUP("U-8",C:G,5,0)

 

[markmzz]

Hello,

The formula below returns the text "E8" (not the contents of cell E8).

=ADDRESS(MATCH("U-8",C:C,0),5,4)

Please could you tell me how to incorporate it into an offset formula to return the contents of cell G8? (i.e. move 2 columns to the right and return the value of the contents).

Thanks,

poiu

Try this (two ways):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">U-8</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Value</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Value</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">Value</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I8</th><td style="text-align:left">=OFFSET(<font color="Blue">INDIRECT(<font color="Red">ADDRESS(<font color="Green">MATCH(<font color="Purple">"U-8",C:C,0</font>),5,4</font>)</font>),,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I9</th><td style="text-align:left">=OFFSET(<font color="Blue">G1,MATCH(<font color="Red">"U-8",C:C,0</font>)-1,</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz