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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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.
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
also check out DGET w/ multiple criterias.
 

Kbroccardo

New Member
Joined
Feb 19, 2004
Messages
11

ADVERTISEMENT

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
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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.
 

Kbroccardo

New Member
Joined
Feb 19, 2004
Messages
11
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Kbroccardo

New Member
Joined
Feb 19, 2004
Messages
11
Not yet. I will do so and see if I can come up with a solution. I'm closer though to solving the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,884
Members
414,029
Latest member
mrwilker

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
Top