Index Match help for multiple cells

[MrMarkSwartz23]

Hi everyone,

I'm trying to use Index & Match to pull two different cells data. The formula seems to be working, but I don't want it to keep pulling the 1st item it finds.

Here is the formula:

=IF('Product List'!T2:T259<20,(INDEX(Code,MATCH("SM05",'Product List'!E3:E38,0),1)))

How can I modify this so it doesn't keep pulling the first item it finds in T2:T259 that is less than 20 and matches "SM05". I realize I'll have to have a formula in multiple cells for each result. I've attempted an Index Aggregate formula as well as an Index small formula. I haven't made them correctly thus far. Am I on the right path?

Appreciate any advice

[#2]

Could you please post a sample Excel file and describe which information you want to pull from where in that file?

[#3]

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.

@Jan Karel Pieterse 

[#4 · Accepted solution]

@MrMarkSwartz23 

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))

[#5]

Yes, this did work. Thanks for the help.

@Subodh_Tiwari_sktneer 

[#6]

You're welcome @MrMarkSwartz23! Glad it worked as desired.