Offset from cell returned from vlookup

[Ltorkels]

Say in cell G10 I used a vlookup to return the appropriate value found in range L8:L20

Say it returns the value found in L14.

Next, in cell G11 I want to get the value in L13.

I thought of using OFFSET since I just want -1 row in same column.

But I can't figure out how cell G11 knows that what I'm offseting is L14.

Thanks,
Linda

 

[Marcelo Branco]

No, i think you need

=OFFSET(L8,MATCH(F10,K8:K20,0)-2,)

or

=INDEX(L8:L20,MATCH(F10,K8:K20,0)-1)

M.

 

[markmzz]

The formula depends on the VLOOKUP function used. Look at this:

<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 /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th><th>G</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Name01</td><td style="text-align: center;;">data9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: center;;">data9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Name01</td><td style="text-align: center;;">data9</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;;">Name02</td><td style="text-align: center;;">data3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">20</td><td style="text-align: center;;">data3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Name02</td><td style="text-align: center;;">data3</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;background-color: #FFFF00;;">Name07</td><td style="text-align: center;background-color: #FFFF00;;">data8</td><td style="text-align: center;;">Name03</td><td style="text-align: center;;">data7</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">75</td><td style="text-align: center;background-color: #FFFF00;;">data8</td><td style="text-align: right;;">30</td><td style="text-align: center;;">data7</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">Name071</td><td style="text-align: center;background-color: #FFFF00;;">data8</td><td style="text-align: center;;">Name03</td><td style="text-align: center;;">data7</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">data4</td><td style="text-align: center;;">Name04</td><td style="text-align: center;;">data8</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">data4</td><td style="text-align: right;;">40</td><td style="text-align: center;;">data8</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">data4</td><td style="text-align: center;;">Name04</td><td style="text-align: center;;">data8</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #C5D9F1;;">data4</td><td style="text-align: center;;">Name05</td><td style="text-align: center;;">data6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/D</td><td style="text-align: right;;">50</td><td style="text-align: center;;">data6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">Name05</td><td style="text-align: center;;">data6</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Name06</td><td style="text-align: center;background-color: #C5D9F1;;">data4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/D</td><td style="text-align: right;;">60</td><td style="text-align: center;background-color: #C5D9F1;;">data4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">#N/D</td><td style="text-align: center;;">Name06</td><td style="text-align: center;background-color: #C5D9F1;;">data4</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">Name07</td><td style="text-align: center;background-color: #FFFF00;;">data8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">70</td><td style="text-align: center;background-color: #FFFF00;;">data8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">Name07</td><td style="text-align: center;background-color: #FFFF00;;">data8</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Name08</td><td style="text-align: center;;">data2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">80</td><td style="text-align: center;;">data2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Name08</td><td style="text-align: center;;">data2</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Name09</td><td style="text-align: center;;">data11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">90</td><td style="text-align: center;;">data11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Name09</td><td style="text-align: center;;">data11</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Name10</td><td style="text-align: center;;">data12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td><td style="text-align: center;;">data12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Name10</td><td style="text-align: center;;">data12</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Name11</td><td style="text-align: center;;">data13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">110</td><td style="text-align: center;;">data13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Name11</td><td style="text-align: center;;">data13</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Name12</td><td style="text-align: center;;">data10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">120</td><td style="text-align: center;;">data10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Name12</td><td style="text-align: center;;">data10</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Name13</td><td style="text-align: center;;">data1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">130</td><td style="text-align: center;;">data1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">Name13</td><td style="text-align: center;;">data1</td></tr><tr ><td style="color: #161120;text-align: center;">21</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><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;">22</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><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">G10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">F10,K8:L20,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G11</th><td style="text-align:left">=OFFSET(<font color="Blue">L8,MATCH(<font color="Red">F10,K8:K20,0</font>)-2,</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G12</th><td style="text-align:left">=INDEX(<font color="Blue">L8:L20,MATCH(<font color="Red">F10,K8:K20,0</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">N10,P8:Q20,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O11</th><td style="text-align:left">=LOOKUP(<font color="Blue">N10,P8:P20,Q7:Q19</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O12</th><td style="text-align:left">=OFFSET(<font color="Blue">Q8,MATCH(<font color="Red">N10,P8:P20,0</font>)-2,</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O13</th><td style="text-align:left">=INDEX(<font color="Blue">Q8:Q20,MATCH(<font color="Red">N10,P8:P20,0</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">S10,U8:V20,2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T11</th><td style="text-align:left">=LOOKUP(<font color="Blue">S10,U8:U20,V7:V19</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T12</th><td style="text-align:left">=OFFSET(<font color="Blue">V8,MATCH(<font color="Red">S10,U8:U20,0</font>)-2,</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T13</th><td style="text-align:left">=INDEX(<font color="Blue">V8:V20,MATCH(<font color="Red">S10,U8:U20,0</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[T. Valko]

Actually, I ran into a problem. The numbers in my column for the lookup are not unique. That is why I really need to offset from a cell location rather than from the value.

For example, with Markmzz's data assume there are two cells with data8 in the column, but we want the one associated with Name07.

I think your formula will offset from the first instance of data8 whether or not it is the correct one from the vlookup.

How about posting some sample data that includes duplicates and tell us what result you expect.

Knowing how your data is setup will help greatly otherwise, all you end up getting are guesses.

 

[Ltorkels]

Do you know how to handle if I have duplicates in column L? The match function finds the first one in the list rather than the one that corresponds to the cell the lookup function found.

For example, say that L9 is also Data8 then then offset function will return L8 or the value Data9 since that is the first match.

Thanks,
Linda

 

[Ltorkels]

I would provide a sample but I can't figure out how to do it. I can't cut and paste or attach.

Markmzz has good example. Just change cell L9 to be Data8 and now there is a duplicate. I need Data4 to be returned, not Data9.

Thanks.

 

[T. Valko]

I would provide a sample but I can't figure out how to do it. I can't cut and paste or attach.
Thanks.

Can you post up a small sample file?

You can use a free file host site if need be.

 

[markmzz]

I would provide a sample but I can't figure out how to do it. I can't cut and paste or attach.

Markmzz has good example. Just change cell L9 to be Data8 and now there is a duplicate. I need Data4 to be returned, not Data9.

Thanks.

Linda,

Look at posts #11 (Marcelo) and #12 (Markmzz).

I think that what you want was there.

Markmzz