Forum Discussion

MrMarkSwartz23's avatar
MrMarkSwartz23
Copper Contributor
Sep 23, 2019
Solved

Index Match help for multiple cells

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:...
excel
Formulas and Functions
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Sep 23, 2019

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

JKPieterse's avatar
JKPieterse
Silver Contributor
Sep 23, 2019
Could you please post a sample Excel file and describe which information you want to pull from where in that file?
MrMarkSwartz23's avatar
MrMarkSwartz23
Copper Contributor
Sep 23, 2019

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.

 
 

JKPieterse 

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Sep 23, 2019

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

Resources