Can a vlookup return data from a different row?

[GreenyMcDuff]

Hi guys,

I have a data sheet like this:

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384 x:str><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=64>Apples</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>a</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>b</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>c</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>d</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>e</TD></TR></TBODY></TABLE>

Using Apples as the reference I need to return the value in the 2nd row.

So ideally I would want my vlookup in the 3rd column to return b

Is this possible using a vlookup?

Thanks

 

[bebo021999]

Hi guys,
I have a data sheet like this:

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384 x:str><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=64>Apples</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=64 x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>a</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>b</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>c</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>d</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>e</TD></TR></TBODY></TABLE>

Using Apples as the reference I need to return the value in the 2nd row.
So ideally I would want my vlookup in the 3rd column to return b
Is this possible using a vlookup?
Thanks

In this case, the best solution is INDEX & MATCH
If Apples is within range A1:A99:

=INDEX(A1:F100,MATCH("Apples",A1:A100,0)+1,3)

 

[CharlesChuckieCharles]

VLOOKUP no....
VLOOKUP and HLOOKUP TOGETHER Yes

=HLOOKUP(VLOOKUP("Apples",A1:F2,4,FALSE),A1:F2,2,FALSE)
(which works in a similar way to Index and Match)

Index and Match yes

 

[GreenyMcDuff]

OK thanks guys, that's great.

I tried the formulas but I keep geting errors. I think it is because there are multiple set's fo data on the sheet.

So would it make any difference if my spreadsheet looks like this, for example:

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384 x:str><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" height=17 width=64>Apples</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22 width=64 x:num>1</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22 width=64 x:num>2</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22 width=64 x:num>3</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22 width=64 x:num>4</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22 width=64 x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" height=17></TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22>a</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22>b</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22>c</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22>d</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl22>e</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384 x:str><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=18 width=64>Bananas


</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl23 width=64 align=right x:num u1:num>5</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl23 width=64 align=right x:num u1:num>7</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl23 width=64 align=right x:num u1:num>9</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl23 width=64 align=right x:num u1:num>11</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl24 width=64 align=right x:num u1:num>13</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 height=18 width=64> </TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 width=64>z</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 width=64>x</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 width=64>y</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 width=64>w</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: #666699 1pt solid" class=xl27 width=64>v


</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=18></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=18 width=64>Apples


</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl23 width=64 align=right x:num u1:num>1</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl23 width=64 align=right x:num u1:num>2</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl23 width=64 align=right x:num u1:num>3</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: silver 1pt solid" class=xl23 width=64 align=right x:num u1:num>4</TD><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl24 width=64 align=right x:num u1:num>5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl25 height=18 width=64> </TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 width=64>a</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 width=64>b</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 width=64>c</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 1pt solid" class=xl26 width=64>d</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: #666699 1pt solid" class=xl27 width=64>e</TD></TR></TBODY></TABLE>

Thanks

 

[RoryA]

It shouldn't make any difference - what errors are you getting?

 

[GreenyMcDuff]

The vba inserts the formula correctly in the sheet. The problem is the result is an #N/A

This is how the formula is written into the cells after the vba code has run:

=INDEX('C:\ Path Name\[File Name.xls]Summary (Net of Fees)'!$A:$M, MATCH($B9, 'C:\ Path Name\[File Name.xls]Summary (Net of Fees)'!$A:$M,0)+3,7)

or, if i use the h/vlookup method:

=HLOOKUP(VLOOKUP($B9,'C:\ Path Name\[File Name.xls]Summary (Net of Fees)'!$A:$M,3,0),'C:\ Path Name\[File Name.xls]Summary (Net of Fees)'!$A:$M,4,0)

 

[GreenyMcDuff]

It seems as though the problem is with the hlookup part.

I tried writing the formula in 2 steps on the same sheet (so there was no need for the path name)

=VLOOKUP($U167,$A:$S,7,0)

returned -8.29 as expected (in cell U168)

=HLOOKUP($U168,$A:$S,4,0)

returned the #N/A

 

[RoryA]

Your formulas are wrong. The INDEX MATCH should be:
=INDEX('C:\ Path Name\[File Name.xls]Summary (Net of Fees)'!$A:$M, MATCH($B9, 'C:\ Path Name\[File Name.xls]Summary (Net of Fees)'!$A:$A,0)+3,7)
Match requires a 1 column range.

 

[GreenyMcDuff]

Legend. Thanks Rory.