Excel database lookup

[Guest]

I have a database in Excel that I am trying to look up a number using two references. The database is set up in three columns with customer number, part number and price. Each customer has several part numbers in the database and each part number has a unique price for that customer. I want to build a formula in another spreadsheet that looks up the price if I know the customer number and the part number. The database is large and is a downloaded set of data from our mainframe computer and has all the cusotmers, part numbers and price in one long file. I have no problem with lookup functions, but cannot find a way to lookup a value using two references. Can anybody help me do this in Excel?

 

[Guest]

There might be a more elegant way that I am not aware of, but here's a way that should work for you:
First, sort the list first by the first column (customer number), and then by the second column (part number), both ascending.

Add a new column to the right of part number, and in this column concatenate customer number and part number.

Also add a new column to the right of the customer number and part number that you want to look up the price for, and concatenate those fields here. Now just use that concatenation as the lookup value, and the lookup range is just the long list of concatentations and price columns

 

[Biff]

Hi Jim!

Try a simple SUMPRODUCT() formula:

Cust ID's in A2:A1000
Part #'s in B2:B1000
Prices in C2:C1000

A1 = Cust ID to "lookup"
B1 = Part # to "lookup"

=SUMPRODUCT((A2:A1000=A1)*(B2:B1000=B1)*C2:C1000)

Biff

-----Original Message-----
I have a database in Excel that I am trying to look up a

number using two references. The database is set up in
three columns with customer number, part number and
price. Each customer has several part numbers in the
database and each part number has a unique price for that
customer. I want to build a formula in another
spreadsheet that looks up the price if I know the customer
number and the part number. The database is large and is
a downloaded set of data from our mainframe computer and
has all the cusotmers, part numbers and price in one long
file. I have no problem with lookup functions, but cannot
find a way to lookup a value using two references. Can
anybody help me do this in Excel?

 

[Guest]

Hi,

Assuming that the customer number, item number and price are in column A, B and C respectively. Do the following:

Highlight the cells which read "Customer number" and "Item number"
Go to Data > Filter > Auto Filter

Now click on the drop down arrow in the column "Customer number" and select A or B.
Likewise select the part number the drop down menu of "Item Number"

Regards,

Ashish Mathur