Hi,

Thanks to RD, Toppers and Daddylonglegs for posting solutions.

l am extremely sorry, but I forgot to add one very crucial piece of
information. (I wouldnt protest if I get any brickbats from you)

The data I have in my Z1:AA10 table has repeating values. For example
Z1 and Z6 might have same value (both of which might be equal to P27).
In that case I want the "lookup" to return both AA1 and AA6 and each of
the cells of SumProduct should be checked with both AA1 and AA6.

Why do I have data like this?

Basically, in cells P27 (and Column Z) the data I have is of PRODUCTS
at an aggregate level. On the other hand, Column AA (and Column D) the
data is of products at an Atomic level (broken down or granular level).

Just to give an example, I can have the value "Microsoft Office" in P27
while, Col D and Col AA will always have granular values like "Office
97", "Office 2000", "Office XP" and "Office 2003" etc. Now, sometimes I
might be interested in finding out number of users of Office 2000
(granular) and sometimes the consolidated product like MS office. So, I
created a lookup table (Z1:AA10) in which the Consolidated products
were listed as many times along with the corresponding granular
products and even the granular products were listed with the same
granualr value in AA.

Please assist me in finding a solution to the same.

Regards,
HP
India