# Multi Lookup Problem

#### Kbroccardo

##### New Member
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.

Karen

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### just_jon

##### Legend
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
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.

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

=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
also check out DGET w/ multiple criterias.

#### Kbroccardo

##### New Member

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
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?

##### MrExcel MVP

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

##### MrExcel MVP
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
Not yet. I will do so and see if I can come up with a solution. I'm closer though to solving the problem.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,025
Messages
5,856,905
Members
431,837
Latest member
megantang

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

### Which adblocker are you using?

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

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