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

[mjaeger1]

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?

 

[jasonb75]

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]

Jason,

That worked GREAT. Thank you for your help!

Sorry for the blurry picture