Vlookup reference the row below

[Guest]

Using a vlookup formula, instead of referencing the value in the same row,
the 3rd column over, how can I reference the value in the row below the
lookup value, still in the 3rd column of the table.

In the example below, if I look up "Jason" (in A1), I want to return the
value 4 (C2). If this helps, the value I want to return is always in the same
row as "Record Count" in column B.


A B C
Jason Sum of Amt 8,000
Record Count 4
Mark Sum of Amt 5,000
Record Count 5
Sarah Sum of Amt 6,500
Record Count 3
Glen Sum of Amt 7,888
Record Count 4
Tom Sum of Amt 2,500
Record Count 2
Becky Sum of Amt 4,700
Record Count 5


thanks!

 

[Peo Sjoblom]

=INDEX(C1:C100,MATCH("Jason",A1:A100,0)+1)

 

[Dave Peterson]

Maybe something like:

=index(sheet2!c:c,match(a1,sheet2!a:a,0)+1)

You may want to make the output of the pivottable more like a pivottable.

See Debra Dalgleish's site for some nice pictures:
http://contextures.com/xlPivot02.html

 

[Guest]

Works great, thank you!

I also used IF(ISNA(formulamentionedbelow),0,formulamentionedbelow) to
replace the #n/a values with 0.

 

[Guest]

Similar to this formula, I now need to be able to sum values in the same row.
Using the example I posted earlier above, instead of returning the value 4
from column C, I need to be able to add up the values in column C, D, E and F.

Let me know if you need an example.

Thanks!

 

[Harlan Grove]

Similar to this formula, I now need to be able to sum values in the same
row. Using the example I posted earlier above, instead of returning the
value 4 from column C, I need to be able to add up the values in column
C, D, E and F. ....

If this INDEX(..,MATCH(..)) formula works for looking up the column C value
you want, then either

=INDEX(C1:C100,MATCH("Jason",A1:A100,0)+1)
+INDEX(D1100,MATCH("Jason",A1:A100,0)+1)
+INDEX(E1:E100,MATCH("Jason",A1:A100,0)+1)
+INDEX(F1:F100,MATCH("Jason",A1:A100,0)+1)

or

=SUMPRODUCT((MATCH("Jason",A1:A100,0)+1=ROW(A1:A100))*C2:F101)