Forum Discussion
Autopopulate from series of Drop Down Menus
Hi there!
I'm trying to do something relatively complex and I'm very new to Excel...
I've created a total of 4 drop down menus,
Menu One has 3 options, Menu Two has 3 options, Menu Three has 3 options, and Menu Four has 3 options.
Each and every one of these combinations lead to a different part number.
I need to get Excel to autopopulate that part number after the four drop down menus have been manually selected.
Example
System: Length: Color: Alarm: Part #
System Two 200 Ft Purple 5-10 XXXXXXXX
Sounds like you are wanting to do a multi-criteria lookup. You'd have 3*3*3*3=81 possible combinations, so you should have a table (perhaps in another worksheet?) with at least 5 columns (System, Length, Color, Alarm, Part #).
The following array formula is an example of a multi-criteria lookup with the data table in range A1:E81 (column E being the part number) and the drop-down menus are in cells G1, H1, I1, J1. This works by concatenating the data prior to performing the match.
{=INDEX(E1:E81, MATCH( G1&H1&I1&J1, A1:A81&B1:B81&C1:C81&D1:D81, 0) )}
Thank you so much for your reply.
So if I were to use that formula, my drop down menus and look up are on sheet1,
while the table and part numbers are on sheet3.
The drop down menus are A2, B2, C2 and D2.
The table is A14:A94,B14:B94, C14:C94, and D14:D94
So should the formula look like...
{=INDEXSheet3!(F14:94, MATCH(Sheet1!A2&B2&C2&D2, Sheet3!A14:A94&B14:B94&C14:C94&D14:D94, 0) )}You placed "Sheet3!" on the wrong side of the "(", but without actually testing the following formula, it looks correct, assuming your part numbers are in column F of Sheet3.
{=INDEX(Sheet3!F14:94, MATCH(Sheet1!A2&B2&C2&D2, Sheet3!A14:A94&B14:B94&C14:C94&D14:D94, 0) )}
Note that the curly brackets {...} are applied by using CTRL+SHIFT+ENTER to enter the formula as an array formula.
