Multi Lookup Problem

Kbroccardo

New Member
Joined
Feb 19, 2004
Messages
11
Hi,

Relatively new to the group but having a major problem with this formula. I've got half of it working but not the rest. I looked for solutions on the message board but couldn't find the exact solution.

Anyhow, I'm creating a form that is going to have two data validation lists that are customer inputed. I need to create a formula that will look up both data points the customer has input and locate the pricing for that item.

I've been using the index match formula and have gotten 1 half of the equation to work. It would be where the customer has input a product and it located the price but not both points.

The formula I have right now is:
=INDEX(price!$I$10:$I$347,MATCH(A17,price!$A$10:$A$347,0)) that finds the product and price
but I need this formula to look at column d (another data validation list) then match the product and machine classification to come up with the price.

I don't know how to add my spreadsheet so you can look at it so I'll do the best at explaining.

a17 is the location of the product drop down validation list
Price! I is the pricing of the product
d17 will be where the customer inputs the machine classification and will also look to price! I to get the pricing.

I have tried to do 2 matches in the same equation and I either get a #ref error or a #n/a error.

Please help, I'm going crazy!!

Karen
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
If this --

=INDEX(price!$I$10:$I$347,MATCH(A17,price!$A$10:$A$347,0))

fetches the 1st instance, then this --

=INDEX(price!$I$10:$I$347,MATCH(A17,price!$A$10:$A$347))

should fetch the last instance.
 
Upvote 0
Kbroccardo said:
Hi,

Relatively new to the group but having a major problem with this formula. I've got half of it working but not the rest. I looked for solutions on the message board but couldn't find the exact solution.

Anyhow, I'm creating a form that is going to have two data validation lists that are customer inputed. I need to create a formula that will look up both data points the customer has input and locate the pricing for that item.

I've been using the index match formula and have gotten 1 half of the equation to work. It would be where the customer has input a product and it located the price but not both points.

The formula I have right now is:
=INDEX(price!$I$10:$I$347,MATCH(A17,price!$A$10:$A$347,0)) that finds the product and price
but I need this formula to look at column d (another data validation list) then match the product and machine classification to come up with the price.

I don't know how to add my spreadsheet so you can look at it so I'll do the best at explaining.

a17 is the location of the product drop down validation list
Price! I is the pricing of the product
d17 will be where the customer inputs the machine classification and will also look to price! I to get the pricing.

I have tried to do 2 matches in the same equation and I either get a #ref error or a #n/a error.

Please help, I'm going crazy!!

Karen

You will need to create new column on your lookup table. The new column needs to combine the 2 values needed in the index/match.
=B1 & " " & C1

Then change your formula
=INDEX(price!$J$10:$J$347,MATCH(A17&" "&D17,price!$A$10:$A$347,0))

You may need to adjust lookup ranges to match the new values with the extra column inserted.

Hope this helps.
 
Upvote 0
Not sure I follow. Both formulas look the same.

I have tried:=INDEX(price!$I$10:$I$347,MATCH(A17,price!$A$10:$A$347,0),MATCH(D17,price!$B10:B$347,0)) and am getting a #ref error.

Hard to explain but once a customer inputs a product from a drop down list and enters a machine classification from a drop down list, I want a formula to go and find a and b and come up with the right pricing on that product and machine classification.

Again a17 is the product drop down list and
d17 is the machine class drop down list
the pricing is found on my price spreadsheet located in column I
 
Upvote 0
Sorry -- totally mis-read your post; sounds oike what you need to do is concatenate the 2 dropdown values and match them to 2 columns. On sheet price, which coolumns hold prioduct/machine?
 
Upvote 0
Kbroccardo said:
Not sure I follow. Both formulas look the same.

I have tried:=INDEX(price!$I$10:$I$347,MATCH(A17,price!$A$10:$A$347,0),MATCH(D17,price!$B10:B$347,0)) and am getting a #ref error.

Hard to explain but once a customer inputs a product from a drop down list and enters a machine classification from a drop down list, I want a formula to go and find a and b and come up with the right pricing on that product and machine classification.

Again a17 is the product drop down list and
d17 is the machine class drop down list
the pricing is found on my price spreadsheet located in column I

I think you need multi-key lookup where the lookup table has an additional column that concatenates product and calssification. See Cbrine's reply in this thread and do a search on this board with multi-key as search term.
 
Upvote 0
column A = product
column b = machine classification
colunm I = pricing

There are several other columns but I am only concerned with these 3 right now.

Can I have 2 drop down lists? Again, I'm not the best in coming up with these formulas but try my best to figure them out.
 
Upvote 0
Kbroccardo said:
column A = product
column b = machine classification
colunm I = pricing

There are several other columns but I am only concerned with these 3 right now.

Can I have 2 drop down lists? Again, I'm not the best in coming up with these formulas but try my best to figure them out.

Did you do a search with multi-key as keyword on this site?
 
Upvote 0
Not yet. I will do so and see if I can come up with a solution. I'm closer though to solving the problem.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top