Need a formula to look up 2 values and return result from 3rd colu

[Guest]

I have 2 spreadsheets. Source spreadsheet has 3 column a = customer ID,
Column B = Item ID, Column C = Bill rate.

2nd spreadsheet has Columns a-u. Column C=Customer Id, Column J = Item ID,
which will match column A & B in the source spreadsheet. Then I have column P
= Billing rate. For this field I need to pull the billing rate from the
source spreadsheet. So in other words if column C & J in 2nd spreadsheet
matches column A & B in source spreadsheet then I it to return the value from
Column C in the source sheet into the 2nd spreadsheet in column P. They must
match exactly to return the customer billing rate. I used this formula but
it is not working:

=VLOOKUP(C2&"/"&J2,'[customer billing rates.xls]Sheet1'!$A$1:$C$62182,3). I
used VLookup before but only with one lookup value. I have played around
with Match and Index. Thanks for any help and I hope that I am very clear in
my explanation.

Example:

Source Spreadsheet:

Columns

A2 B2 C2
Cust. ID Item ID Billing rate
Joe Blow 2XR 50.00
Mandy Moore 2XR 40.00
Sandy Shore 2XR 30.00

Lookup Spreadsheet:


C2 J2 P2
Cust Id Item ID Billing Rate
Joe Blow 2XR ?????
Mandy Moore 2XR ??????
Sandy Shore 2XR ?????

Also, I get an error = not able to save due to resources - choose less data
or ...

 

[Guest]

=SUMPRODUCT(--(customer_ID=Sheet2!C2),--(Item_ID=Sheet2!J2),Bill_rate)

 

[Guest]

I guess I should have said instead of 2nd spreadsheet - 2nd workbooks. I am
using to different work books. So instead of Sheet2 - I need to reference
the source spreadsheet which is customer billing rates.xls. Both sheets are
in the same directory. C:\time ticket info\customer billing rates.xls. I
put in your formula but wasn't sure what the -- refers to??? I copied the
formula and then it prompts me for what I assume is the source spreadsheet
(customer billing rates.xls). When I choose that file then I get the error
#name? (invalid name error). Can you tell me what I did wrong or reference
my spreadsheet name so that ... I works right. You have the dashes and I am
not sure what I am supposed to replace them with. There is no sheet2.

Thanks for your help.

=SUMPRODUCT(--(customer_ID=Sheet2!C2),--(Item_ID=Sheet2!J2),Bill_rate)

I have 2 spreadsheets. Source spreadsheet has 3 column a = customer ID,
Column B = Item ID, Column C = Bill rate.

2nd spreadsheet has Columns a-u. Column C=Customer Id, Column J = Item ID,
which will match column A & B in the source spreadsheet. Then I have column P
= Billing rate. For this field I need to pull the billing rate from the
source spreadsheet. So in other words if column C & J in 2nd spreadsheet
matches column A & B in source spreadsheet then I it to return the value from
Column C in the source sheet into the 2nd spreadsheet in column P. They must
match exactly to return the customer billing rate. I used this formula but
it is not working:

=VLOOKUP(C2&"/"&J2,'[customer billing rates.xls]Sheet1'!$A$1:$C$62182,3). I
used VLookup before but only with one lookup value. I have played around
with Match and Index. Thanks for any help and I hope that I am very clear in
my explanation.

Example:

Source Spreadsheet:

Columns

A2 B2 C2
Cust. ID Item ID Billing rate
Joe Blow 2XR 50.00
Mandy Moore 2XR 40.00
Sandy Shore 2XR 30.00

Lookup Spreadsheet:


C2 J2 P2
Cust Id Item ID Billing Rate
Joe Blow 2XR ?????
Mandy Moore 2XR ??????
Sandy Shore 2XR ?????

Also, I get an error = not able to save due to resources - choose less data
or ...

 

[T. Valko]

With that much data this will only work *properly* with the source file
open:

=SUMPRODUCT(--('[Customer Billing
Rates.xls]Sheet1'!A2:A62182=C2),--('[Customer Billing
Rates.xls]Sheet1'!B2:B62182=J2),'[Customer Billing
Rates.xls]Sheet1'!C2:C62182)

If the source file is closed you might get this error message:

http://img391.imageshack.us/img391/3284/errormf5.jpg

This means your trying to reference too much data in a closed file. The
limit I run into is >16376 rows (Excel 2002)

--
Biff
Microsoft Excel MVP

I guess I should have said instead of 2nd spreadsheet - 2nd workbooks. I
am
using to different work books. So instead of Sheet2 - I need to reference
the source spreadsheet which is customer billing rates.xls. Both sheets
are
in the same directory. C:\time ticket info\customer billing rates.xls. I
put in your formula but wasn't sure what the -- refers to??? I copied the
formula and then it prompts me for what I assume is the source spreadsheet
(customer billing rates.xls). When I choose that file then I get the
error
#name? (invalid name error). Can you tell me what I did wrong or
reference
my spreadsheet name so that ... I works right. You have the dashes and I
am
not sure what I am supposed to replace them with. There is no sheet2.

Thanks for your help.

=SUMPRODUCT(--(customer_ID=Sheet2!C2),--(Item_ID=Sheet2!J2),Bill_rate)

I have 2 spreadsheets. Source spreadsheet has 3 column a = customer
ID,
Column B = Item ID, Column C = Bill rate.

2nd spreadsheet has Columns a-u. Column C=Customer Id, Column J =
Item ID,
which will match column A & B in the source spreadsheet. Then I have
column P
= Billing rate. For this field I need to pull the billing rate from
the
source spreadsheet. So in other words if column C & J in 2nd
spreadsheet
matches column A & B in source spreadsheet then I it to return the
value from
Column C in the source sheet into the 2nd spreadsheet in column P.
They must
match exactly to return the customer billing rate. I used this formula
but
it is not working:

=VLOOKUP(C2&"/"&J2,'[customer billing
rates.xls]Sheet1'!$A$1:$C$62182,3). I
used VLookup before but only with one lookup value. I have played
around
with Match and Index. Thanks for any help and I hope that I am very
clear in
my explanation.

Example:

Source Spreadsheet:

Columns

A2 B2 C2
Cust. ID Item ID Billing rate
Joe Blow 2XR 50.00
Mandy Moore 2XR 40.00
Sandy Shore 2XR 30.00

Lookup Spreadsheet:


C2 J2 P2
Cust Id Item ID Billing Rate
Joe Blow 2XR ?????
Mandy Moore 2XR ??????
Sandy Shore 2XR ?????

Also, I get an error = not able to save due to resources - choose less
data
or ...