VLOOKUP to return Cell Address
 

I have a table, and I want to do a Vlookup on Column1, and when I find what I
am looking for, I want to sum column 2 from that row down to the bottom of
the table.

For example,
Item 1 2345
Item 2 3478
Item 3 1298
Item 4 123
Item 5 1233

I want to find "Item 3" and then I want to be able to return the sum of
(1298+123+1233).

How would I do this? My thoughts were that I would do a lookup to get the
row numner of "Item 3" and then do a sum() with the cell range starting from
the row where I found "Item 3" but I'm not sure how to do that.

Thanks,
Steve

VLOOKUP to return Cell Address
 

Hi,

I may have overdone this, i'm sure there's a simpler solution but in the
meantime try this

=SUM(INDIRECT("B" & MATCH("item 3",A:A) &":B"& MATCH(LOOKUP(10^99,B:B),B:B)))

Mike
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Steve Haack" wrote:

I have a table, and I want to do a Vlookup on Column1, and when I find what I
am looking for, I want to sum column 2 from that row down to the bottom of
the table.

For example,
Item 1 2345
Item 2 3478
Item 3 1298
Item 4 123
Item 5 1233

I want to find "Item 3" and then I want to be able to return the sum of
(1298+123+1233).

How would I do this? My thoughts were that I would do a lookup to get the
row numner of "Item 3" and then do a sum() with the cell range starting from
the row where I found "Item 3" but I'm not sure how to do that.

Thanks,
Steve

VLOOKUP to return Cell Address
 

I put you data in A1:B5
In G1 I type: Item3
In H1 I used formula: =ADDRESS(MATCH(G1,A:A,0),2) which returned $B$3
In I1 I used formula: =SUM(INDIRECT(H1&":B100")) which retuned 2654 as
required

It would be easy to combine H1 and I1
What if your data began in row 3, for example? Formula in H1 returns $B$5
and all is well
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Steve Haack" wrote in message
...
I have a table, and I want to do a Vlookup on Column1, and when I find
what I
am looking for, I want to sum column 2 from that row down to the bottom of
the table.

For example,
Item 1 2345
Item 2 3478
Item 3 1298
Item 4 123
Item 5 1233

I want to find "Item 3" and then I want to be able to return the sum of
(1298+123+1233).

How would I do this? My thoughts were that I would do a lookup to get the
row numner of "Item 3" and then do a sum() with the cell range starting
from
the row where I found "Item 3" but I'm not sure how to do that.

Thanks,
Steve

VLOOKUP to return Cell Address
 

Try this...

Data in the range A2:B6

D2 = lookup value = Item 3

=SUM(B6:INDEX(B2:B6,MATCH(D2,A2:A6,0)))

--
Biff
Microsoft Excel MVP


"Steve Haack" wrote in message
...
I have a table, and I want to do a Vlookup on Column1, and when I find what
I
am looking for, I want to sum column 2 from that row down to the bottom of
the table.

For example,
Item 1 2345
Item 2 3478
Item 3 1298
Item 4 123
Item 5 1233

I want to find "Item 3" and then I want to be able to return the sum of
(1298+123+1233).

How would I do this? My thoughts were that I would do a lookup to get the
row numner of "Item 3" and then do a sum() with the cell range starting
from
the row where I found "Item 3" but I'm not sure how to do that.

Thanks,
Steve