Forum Discussion
Autopopulate from series of Drop Down Menus
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.
