MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### VLOOKUP to return Cell Address

• Follow

```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
```
 0

```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
```
 0

```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" <SteveHaack@discussions.microsoft.com> wrote in message
news:45162502-E85B-48C6-8072-7D8E073DD906@microsoft.com...
> 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

```
 0

```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" <SteveHaack@discussions.microsoft.com> wrote in message
news:45162502-E85B-48C6-8072-7D8E073DD906@microsoft.com...
>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

```
 0

3 Replies
908 Views

Similiar Articles:

7/22/2012 5:14:20 AM