Referencing a cell where lookup value is not on same row

[Aaron]

Hi,

I need some help... Here is my data:
_____________________________________________
A B C D
1 ID Number Name Hours
2 10153 Joe 8
3 8
4 8
5 Total 24
6
7 10219 Bob 5
8 5
9 Total 10
10
11 10569 Tim 10
12 8
13 8
14 5
15 Total 31
___________________________________________

Basically, I want to copy the ID number, Name, and Total onto a separate
worksheet minimizing the need for manual data entry e.g.
10153 Joe 24
10569 Tim 31
etc.

Basically, I can copy the ID number onto the new worksheet and use a vlookup
to get the name. But I'm having difficultly referencing the Total since it's
not on the name row as the ID and Name. Any suggestions? Thanks!

 

[franciz]

As far as I know, I am afraid to say that Excel need to have flat database to
work on, meaning that the list should be fill up with values and there is no
blank column

 

[Shane Devenshire]

Hi,

It is unclear if you have the flexibility to clean the source data up or
not, but if you do could remove the blank & total rows, fill in the ID#'s and
Names and then you can run a SUMIF or SUMPRODUCT to get the totals to anothe
sheet.

Do you have that flexibility?

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Aaron]

Hi Shane,

Like I said, I want to minimize the amount of manual actions I would have to
perform, so i would say no, this couldn't work. Also, it's worth noting that
the values in the totals are already precalculated since the source data
comes from running a report in a different program.

Although, you might be on to something. Using the same data, let's say I
copy the ID Number from A2 to A6. If I do some kind of a vlookup on the ID
Number, is there a way to reference the last one, thus providing me with the
corresponding value in column D?

 

[muddan madhu]

Try this

Col A Id number
Col B Name
Col C Hours

Col D - cell D2 put this formula ( helping column )
=IF(A2<>A3,A2,D1) and drag it down

Now Copy the Col A and Paste it in Col E

delete the blank cells - ( select the Range in Col E - Click F5 |
click special | choose blanks | ok | right click on col E | delete |
shift cells up | ok | esc

Now using Vlookup u can get the names in Col F =VLOOKUP(E2,$A$2:$B
$200,2,0)

Now in cell G2 put this forumla =INDEX($C$2:$C$200,MATCH(E2,$D$2:$D
$200,0)+COUNTIF($D$2:$D$200,E2),0)

 

[Aaron]

All is good until the last step with the formula :
=INDEX($C$2:$C$200,MATCH(E2,$D$2:$D$200,0)+COUNTIF($D$2:$D$200,E2),0)

What value am I trying to get exactly?

 

[muddan madhu]

you will get the total hours

All is good until the last step with the formula :
=INDEX($C$2:$C$200,MATCH(E2,$D$2:$D$200,0)+COUNTIF($D$2:$D$200,E2),0)

What value am I trying to get exactly?