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!

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



--
Regards,

Peo Sjoblom



"jtoy" wrote in message
...
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!

Works great, thank you!

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


"Peo Sjoblom" wrote:

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



--
Regards,

Peo Sjoblom



"jtoy" wrote in message
...
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!

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!

"Peo Sjoblom" wrote:

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



--
Regards,

Peo Sjoblom



"jtoy" wrote in message
...
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!

"jtoy" wrote...
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.
....
"Peo Sjoblom" wrote:
=INDEX(C1:C100,MATCH("Jason",A1:A100,0)+1)

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(D1:D100,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:A10 0))*C2:F101)

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

jtoy wrote:

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!

--

Dave Peterson