# 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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### 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
3
Views
75
Replies
17
Views
397
Replies
0
Views
149
Replies
6
Views
226
Replies
3
Views
142

1,129,498
Messages
5,636,672
Members
416,935
Latest member
Atulcp

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