Re: Can I create a multiple LOOKUP formula with 'then' function???

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I have not fully understood the structure of your data but you will
most certainly need OFFSET. The expression:

=INDEX(A:A,MATCH(custA,A:A,0))

will locate the cell that contains custA in column A:A. From there you
can use OFFSET. For example, a customer's total is 4 rows further down
and 4 columns further to the right. Thus, to retrieve the total of
custA:

=OFFSET(INDEX(A:A,MATCH(custA,A:A,0)),4,4)

Does this help?
Kostis Vezerides

On Oct 17, 4:41 pm, Matthew Cardiff
<MatthewCard...@xxxxxxxxxxxxxxxxxxxxxxxxx> 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


.

Relevant Pages

  • RE: Using SUMIFS with date range
    ... I assume you enter the customer name in A1, start day in B1 and finish day ... entered into a specific cell and 3. ... those dates to recalculate the totals. ...
    (microsoft.public.excel.misc)
  • Re: Using SUMIFS with date range
    ... "Fred Smith" wrote: ... to specific customer name, 2. ... entered into a specific cell and 3. ... those dates to recalculate the totals. ...
    (microsoft.public.excel.misc)
  • Re: Using SUMIFS with date range
    ... "Catrina" wrote in message ... to specific customer name, 2. ... entered into a specific cell and 3. ... those dates to recalculate the totals. ...
    (microsoft.public.excel.misc)
  • Help with SPROC from .asp please...
    ... retrieve information on overdue invoices depending upon the payment type for ... I then only show one record per customer in the datagrid ... with the totals for total amount invoiced, ... line which allows the totalled rows to have a data field showing the total ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: product date stamp file save
    ... reference to only saving an individual order form rather than all 3 in one go. ... "Robert Loxley" wrote: ... You can change the A1 to the proper cell. ... the user will be prompted for the customer name. ...
    (microsoft.public.excel.misc)