Biff,
I've got a similar challenge with creating a report that compares year to
year data.
Can I forward you an example of the report I'm trying to create ?

GP

"Biff" wrote:

Hi!

So, if on sheet NOV 04 you have in cells:

A10 - branch X
B10 - customer A
C10 - product 1
D10 - sales 100

And in sheet DEC 04 you have in cells:

A3 - branch X
B3 - customer A
C3 - product 1

Then you want the NOV 04 sales data for branch X, customer
A and product 1.

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=INDEX('nov 04'!D2:D20,MATCH(1,('nov 04'!A2:A20="X")*('nov
04'!B2:B20="A")*('nov 04'!C2:C20=1),0))

Or possibly this formula entered normally:

=SUMPRODUCT(--('nov 04'!A2:A20="X"),--('nov 04'!
B2:B20="A"),--('nov 04'!C2:C20=1),'nov 04'!D2:D20)

If these don't "fit the bill" and you want to post an
email address, I'll contact you and if you'd like I can
take a look at your file.

Lookups are usually not difficult but it really helps if
you have the file in front of you.

Biff

-----Original Message-----
Biff, thanks but I need to give you more detail so that I
can decipher. I
have two worksheets: "Nov 04" and "Dec 04". In each of
the spreadsheets are
column listings: Branch (A1), Customer (B1), Product Type
(C1), and Sales
(D1); data is filled in underneath in cells A2:D20. When
comparing the two
spreadsheets, there may be customers and product types
added or deleted, so
the row information may not be the same. I want to match
existing branch,
customer, and product types from both spreadsheets and
pull the sales
information from the Nov 04 worksheet that matches the
corresponding branch,
customer, and product on the Dec 04 spreadsheet. For
those branch, customer
and product types that do not match because of additions
or deletions, I want
a return of "N/A". Hope you can decipher. Thanks for
your help


"Biff" wrote:

Hi!

The basic formula would be something like this:

=INDEX(SALES,MATCH(1,(RANGE=LOCATION)*(RANGE=CUSTO MER)*
(RANGE=PRODUCT),0))

Need more detail for a more specific answer.

Biff

-----Original Message-----
I am trying to use the vlookup function to compare
multiple columns in a data
array versus just the 1st column and return one data
if
all is true.
Example: I have two reports for two different time
periods. There is data
in common between the two reports, such as location,
customer, and product.
I want to pull the associated data, lets say sales,
from
one spreadsheet to
the current spreadsheet, only if the location,
customer,
and product are
identical between the two spreadsheets. How do I do
this?? I have tried
using the and worksheet function to no avail.
.


.