Expert VLOOKUP

D

damberger

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

Biff

Hi!

The basic formula would be something like this:

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

Need more detail for a more specific answer.

Biff
 
D

damberger

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
 
B

Biff

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
 
G

GP

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
 
B

Biff

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
 
G

GP

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

Yes, I am. If you can decipher what I'm trying to do from that, perhaps I
don't need to send you the file. If not, my address is [email protected]

Rgds
GP
 
G

gandhi318

It is not my reply but my requirement
I have similar problem
I have a consolidated information of month-wise subscriptions i
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 usin
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 no
recovered from some reason or the other during every month thei
information is not recevied and the number of rows information i
reduced
For example we have 300 employees filled from D2 down D301 in the abov
sheet and other basic data in other coloumn the first month Jan-yy i
filled
From February I get the information/subscriptions of 290 employee
jumbled of course against GPF account number in one coloumn withou
knowing who those 10 employees' missing subscriptions. I want them t
be posted in Col G next to Col F against each employee GPF accoun
number wise without any problem by searing/comparing the accoun
numbers I have and the account numbers with subscriptions supplied t
me for Feb-yy which I take in a separate sheet (Sheet2) and strugglin
addiing left over accounts numbers sorting and merging the new dat
from Sheet2 to Sheet1

Any easy method to my problem

Thanks
Gandh
 
Top