Multiple Vlookup within a Pivot Tabel

[hartwell]

I am trying to create a VLookup which will look for the date then move onto the next coloum and look for an ext number then bring that row back, i have tried putting a Vlookup within a lookup but no luck, this is how the pivot is structured

Date EXT Data1 Data2 ETC
01/06 101 10 20 30
102 11 21 31
103 12 22 32
01/07 101 13 23 33
102 14 24 34

 

[GlennUK]

What if the ext number doesn't exist for the date? Or will it always exist for every date?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[hartwell]

might not exist

 

[GlennUK]

Not so easy with lookups then. Put an extra column in front of the pivottable, to propogate the dates for every row ( using a formula like =IF(B2="",A1,B2) copied all the way down. )

The use SUMPRODUCT to fetch the values.
=SUMPRODUCT(($A$2:$A$5=$A$8)*($C$2:$C$5=$B$8)*(D25))
where col A is date ( compared to comparion cell in A8 ), C is EXT ( compared to comparison cell in B8 ) and D25 is the first column of "data".

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

It would be easiest to do in the PIVOT TABLE SOURCE DATA rather than the Pivot Table.

Use the techniques explained in

http://www.contextures.com/tiptech.html

dynamic ranges.



Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[hartwell]

excellent, i kind of understand this and have modifications to it,

makes the spreadsheet a little slow when calculating fields but does the job