Lookup

  • Thread starter Thread starter Daniel Bonallack
  • Start date Start date
D

Daniel Bonallack

In column A I have a person's name repeated anywhere
between 5-15 times. In column B, I have the sales for
that person. At the end of the set, the word "Total"
appears in column A, and in column B, the total figure.

Then, after a gap of one row, the next set of names
appears in column A, sales in B etc.

In column D I have the unique list of people's names. In
column E I want a formula that looks up that total value.
(I don't want to sumif the B figures, as the "total" is an
adjusted figure - I need to look it up, with some sort of
lookup/offset)

Can you help?

Thanks
Daniel
 
Daniel,

For the name in D2, enter this in E2:

=SUMPRODUCT(($B$2:$B$1000)*($A$1:$A$999=D2)*($A$2:$A$1000="Total"))

Note that the ranges are the same size (in this example, 999 rows) but are
offset by one row, so if you need to make adjustments, keep that in mind.

HTH,
Bernie
MS Excel MVP
 
Hi Again
I should have used absolute references for the ranges

=OFFSET($A$1,MAX(($A$1:$A$1000=D1)*ROW($A$1:$A$1000)),1,1,1

And just for a little variety, you could also use this array formula

=INDEX($B$1:$B$1000,MAX(($A$1:$A$1000=D1)*ROW($A$1:$A$1000))+1

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Mark Graesser wrote: ----

Hi Daniel
The following array entered formula should do what you need

=OFFSET($A$1,MAX((A1:A1002=D1)*ROW(A1:A1002)),1,1,1

This would be entered using control-shift-enter instead of just enter. The formula will appear in the formula bar with { } around it

Basically it breaks down to

Offset from A
By qty of rows: the max row number where name appears
By qty of columns: 1
Range height:
Range width:

Good Luck
Mark Graesse
(e-mail address removed)
Boston M


----- Daniel Bonallack wrote: ----

In column A I have a person's name repeated anywhere
between 5-15 times. In column B, I have the sales for
that person. At the end of the set, the word "Total"
appears in column A, and in column B, the total figure

Then, after a gap of one row, the next set of names
appears in column A, sales in B etc

In column D I have the unique list of people's names. In
column E I want a formula that looks up that total value.
(I don't want to sumif the B figures, as the "total" is an
adjusted figure - I need to look it up, with some sort of
lookup/offset

Can you help

Thank
Danie
 
Back
Top