Lookup against pivot table with multiple instances
 

HELP!
I'm putting together a spreadsheet that summarises devices removed. On one
tab I have my pivot table which lists all the device types and a count
against the reference number they were removed against. On my second tab I
have a list of each device type and am trying to look up for type the total
number removed. I tried a VLOOKUP but as the same device type occurs for
multiple reference numbers it only grabs the first occurrence.
Anyone able to offer any help ?

Lookup against pivot table with multiple instances
 

Hi AW,
This defeats a lot of people so you are not alone.
Just a pity that you have had to wait 6 weeks for your answer.

The answer is concatenation.
Join (concatenate) device type with rego number , using & (ampersand)
and use that value for vlookup purposes
For example
Device1 Regnum1 becomes Device1Regnum1
Device1 Regnum2 becomes Device1Regnum2
Device1 Regnum3 becomes Device1Regnum3

Thus the new data table will look like this
Device1Regnum1 Device1 Regnum1 Count33
Device1Regnum2 Device1 Regnum2 Count5
Device1Regnum3 Device1 Regnum3 Count87

The formula =Vlookup("Device1Regnum1",DeviceTable,4,FALSE)
will produce the answer "Count33"

Regards Mick Barry
I was so so ugly as a kid,
that whenever I played in the sandpit,
the cat used to try and cover me up.


--
mickbarry
------------------------------------------------------------------------
mickbarry's Profile: http://www.excelforum.com/member.php...o&userid=31028
View this thread: http://www.excelforum.com/showthread...hreadid=493705