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

mjaeger1

New Member
Joined
Jul 1, 2018
Messages
9
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?

1588603613241.png



1588603938068.png
 

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
Joined
Dec 30, 2008
Messages
12,612
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 1, 2018
Messages
9
Jason,

That worked GREAT. Thank you for your help!

Sorry for the blurry picture :)
 

Watch MrExcel Video

Forum statistics

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