# Find "part number" in data table by selecting (3) dropdowns with Index/Match function

#### mjaeger1

##### New Member
Hi All,

I am using a separate 'quick reference' sheet and looking to use (3) DropDown menus in order to triangulate a "Part Number" in a table on another sheet.

The vital information needed to find this "part number" is:
1. Length
2. Color
3. Packaging Type (i.e. SIngles OR Bulk Bundles)

My data table begins at B6 and goes to K42

My original formula to find the "part number" with (2) dropdown menus was:

=INDEX('Copper Patch-Assemblies'!E7:K42,MATCH(Index!C42,'Copper Patch-Assemblies'!C7:C42,0),MATCH(Index!D42,Colors,0))

the first picture below is of the data table where i am pulling data from. The second is the 'index sheet' where the (3) dropdown options are selected in green and "part number" formula in yellow.

How can this be done Where the "Part Number" is found only by selecting the (3) attributes listed above?

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### jasonb75

##### Well-known Member
Your screen caps are blurred and illegible, it looks like column D alternates between single and double, so the best that you can do would be something on the lines of

=INDEX('Copper Patch-Assemblies'!E7:K42,MATCH(Index!C42,'Copper Patch-Assemblies'!C7:C42,0)+(X1="Bulk"),MATCH(Index!D42,Colors,0))

Where X1 refers to the cell with the packaging type dropdown, and "Bulk" refers to the actual text in that cell for a bulk package. Anything not 'Bulk' is automatically assumed single.

If that is not the case, or if column D does not always alternate between Single and Bulk then you will need to 'fix' the sheet in the top image by un-merging the cells in columns A and B, then filling in the blanks. The existing format is not compatible with a 3 way match.

#### mjaeger1

##### New Member
Jason,

That worked GREAT. Thank you for your help!

Sorry for the blurry picture

Replies
17
Views
375
Replies
6
Views
220
Replies
3
Views
137
Replies
3
Views
506
Replies
3
Views
221

1,126,978
Messages
5,621,942
Members
415,868
Latest member
Pita

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