Hi John - Thanks for the assitance.

I am feeding in 2 criteria to return 1 such as Customer name and product to
return Total.

The main issue is that the data is at various and ever changing intervals
because some customers only order 1 product and others order 7 or 8. The
customer name is always in the first row of that set but which product and
how many products vary, therefore it is impossible to calculate which cell
the total will be in. There is no option to use OFFSET as the data is updated
weekly and row positions may change.

It is likely that there are multiple occasions where a customer bought the
same product. Most likey 90% of customers bought product A then 50% of them
will have bought A and B and 25% of them will have bought A,B&C so they will
all have identical row titles with the product names under each customer
title.

Hope this helps.

--
Matthew Cardiff


"John Bundy" wrote:

Just to make sure we have a few things straight:
Are you feeding in 2 criteria and returning 2? such as Customer Name,
product name and returning Sales/revenue and Total
Is the data always in 5 row increments?
Is customer name always in the first, product always in the second, and
total always in the 5th?
Can there be multiple occasions where a customer bought the same product?

That should help us answer much quicker and more accurately.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Matthew Cardiff" wrote:

Hello there - I hope you can help. I am fast losing my hair on this one!

I have a data sheet in Excel 2000 that I cannot alter or sort. As per my
example below it contains numerous rows that contain a list of customers.
Under each customer it contains product by product data that is then sorted
by column into monthly sales and then totals. My problem is that I need a
LOOKUP formula that is able to say "Lookup row with Customer A, THEN LOOKUP
the next row that contains 'product B', THEN find the column in that row that
gives TOTAL".

Ambitous maybe. I have bought 3 Excel books that do not give the solution so
I hope it is something that comes with experience not books! Each customer
has the same list of products so I am struggling to locate a multiple formula
that finds the NEXT "product A" after a particular customer.

Example data

AXIS / col a / col b / colc / col d / col z

row 1 / customer a / blank / blank / blank / col z
row 2 / product a / blank / blank / blank / col z
row 3 / blank / blank / blank / blank / col z
row 4 / blank / blank / jan / feb / col z
row 5 / blank / sales / 100 / 200 / TOTAL

row 23 / customer g / blank / blank / blank / col z
row 24 / product a / blank / blank / blank / col z
row 25 / blank / blank / blank / blank / col z
row 26 / blank / blank / jan / feb / col z
row 27 / blank / sales / 200 / 300 / TOTAL

Summary: Is there a formula I can use that ignores the empty cells and can
locate on a data sheet a particular customer name in column A, then the next
particular product also in column A, then a particular attribute (sales or
revenue) which is in column B, then return from that row the value in column
Z (TOTAL).

Thank you in advance for your help on this problem. I have seen how much
advice has been given on the other topics and think this community is
fantastic.
--
Matthew Cardiff