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.

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.
.

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.
.

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.
.


.

Thanks Biff...this is exactly what I needed.

David

"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.
.


.

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.
.


.

Can I forward you an example of the report I'm trying to create ?

Are you referring to the other post: Compare month to month data ?

Well,............yeah, sure!

Do you have my address? If not, you'll have to post yours!!!

Biff

"GP" wrote in message
...
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.
.


.

It is not my reply but my requirement
I have similar problem
I have a consolidated information of month-wise subscriptions in
Sheet1
A1 Sl.No
B1 GPF Accounts No
C1 Ledger No
D1 Name
E1 Designation
F1 Subscription for Jan-yy
G1 Subscription for Feb-yy
across upto Dec-yy
and in the next colomn Sum of Subscriptions from Jan-yy to Dec-yy using
Sum function
I fill data below the above labels from A2 across and downward

My problem is since the some of the employees subscriptions are not
recovered from some reason or the other during every month their
information is not recevied and the number of rows information is
reduced
For example we have 300 employees filled from D2 down D301 in the above
sheet and other basic data in other coloumn the first month Jan-yy is
filled
From February I get the information/subscriptions of 290 employees
jumbled of course against GPF account number in one coloumn without
knowing who those 10 employees' missing subscriptions. I want them to
be posted in Col G next to Col F against each employee GPF account
number wise without any problem by searing/comparing the account
numbers I have and the account numbers with subscriptions supplied to
me for Feb-yy which I take in a separate sheet (Sheet2) and struggling
addiing left over accounts numbers sorting and merging the new data
from Sheet2 to Sheet1

Any easy method to my problem

Thanks
Gandhi


--
gandhi318Posted from - http://www.officehelp.in