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

 

[T. Valko]

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

You would offset based on the location of where the lookup value is found.

Need more detail. Post your lookup formula.

 

[JoeMo]

One way:

Excel Workbook
	F	G	H	I	J	K	L
7						Lkup Table
8						11	115
9	lookup value -->	8				1	Oc6
10		7				9	J7n
11	value one row above -->	Ht6				13	7MF
12						6	0Ec
13						5	Ht6
14						8	03h
15						4	434
16						12	8Ti
17						3	CKc
18						1	qDF
19						7	d3X
20						2	Kvs
Sheet13

 

[Marcelo Branco]

Maybe this can work

=INDEX($L$8:$L$20,MATCH(G10,$L$8:$L$20,0)-1))

M.

 

[markmzz]

Another way:

<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 /></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></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></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></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Name07</td><td style="text-align: center;;">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;;">data4</td><td style="text-align: center;;">Name04</td><td style="text-align: center;;">data5</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></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;;">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;;">Name07</td><td style="text-align: center;;">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></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></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></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></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></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></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></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></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">G10,L8:L20,0</font>)-2,</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Ltorkels]

Thanks, Marcello. This looks like it is going to work.

 

[Marcelo Branco]

Thanks, Marcello. This looks like it is going to work.

You are welcome and tks for the feedback

M.

 

[Ltorkels]

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.

 

[Marcelo Branco]

VLOOKUP will also get the first instance...

Could you post a sample of your data?

Edit

Disregard this post. Now i understood your problem

M.

 

[Marcelo Branco]

I didnt know you have duplicates in column L. If so i think Markmzz's solution should work

M.