Forum Discussion
Index Match help for multiple cells
Please try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
In Production Planner Sheet
In Q2
=IFERROR(INDEX('Product List'!$A$2:$A$195,SMALL(IF('Product List'!$E$2:$E$195=Q$1,IF('Product List'!$T$2:$T$195<20,ROW('Product List'!$A$2:$A$195)-ROW('Product List'!$A$2)+1)),ROWS(Q$2:Q2))),"")
Confirm with Ctrl+Shift+Enter and then copy it down.
Let me know if this is what you were trying to achieve.
If you have access to Dynamic Array Formulas, you don't need special keystroke Ctrl+Shift+Enter to confirm an array formula and in that case Enter is enough to confirm it.
Also if you have access to the Dynamic Array Formulas, you may also try the Filter function like this...
In Q2
=FILTER('Product List'!$A$2:$A$195,('Product List'!$E$2:$E$195=Q$1)*('Product List'!$T$2:$T$195<20))
Sure, I copied the information that is needed. I want to pull the "code" in column A based on the name in column E of "SM05" and based on the number in column T (being less than 20). All this information is on sheet 1 named Product List. I want the name from Column A Sheet 1 to be placed in Sheet 2(Production Planner) in column Q.
Please try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
In Production Planner Sheet
In Q2
=IFERROR(INDEX('Product List'!$A$2:$A$195,SMALL(IF('Product List'!$E$2:$E$195=Q$1,IF('Product List'!$T$2:$T$195<20,ROW('Product List'!$A$2:$A$195)-ROW('Product List'!$A$2)+1)),ROWS(Q$2:Q2))),"")
Confirm with Ctrl+Shift+Enter and then copy it down.
Let me know if this is what you were trying to achieve.
If you have access to Dynamic Array Formulas, you don't need special keystroke Ctrl+Shift+Enter to confirm an array formula and in that case Enter is enough to confirm it.
Also if you have access to the Dynamic Array Formulas, you may also try the Filter function like this...
In Q2
=FILTER('Product List'!$A$2:$A$195,('Product List'!$E$2:$E$195=Q$1)*('Product List'!$T$2:$T$195<20))
Yes, this did work. Thanks for the help.
