SOLVED
Home

Index Match help for multiple cells

%3CLINGO-SUB%20id%3D%22lingo-sub-869940%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20help%20for%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869940%22%20slang%3D%22en-US%22%3ECould%20you%20please%20post%20a%20sample%20Excel%20file%20and%20describe%20which%20information%20you%20want%20to%20pull%20from%20where%20in%20that%20file%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869997%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20help%20for%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869997%22%20slang%3D%22en-US%22%3E%3CP%3ESure%2C%26nbsp%3BI%20copied%20the%20information%20that%20is%20needed.%20I%20want%20to%20pull%20the%20%22code%22%20in%20column%20A%20based%20on%20the%20name%20in%20column%20E%20of%20%22SM05%22%20and%20based%20on%20the%20number%20in%20column%20T%20(being%20less%20than%2020).%20All%20this%20information%20is%20on%20sheet%201%20named%20Product%20List.%20I%20want%20the%20name%20from%20Column%20A%20Sheet%201%20to%20be%20placed%20in%20Sheet%202(Production%20Planner)%20in%20column%20Q.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-870079%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20help%20for%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-870079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413473%22%20target%3D%22_blank%22%3E%40MrMarkSwartz23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20%3CSTRONG%3EArray%20Formula%3C%2FSTRONG%3E%20which%20requires%20confirmation%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20instead%20of%20Enter%20alone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Production%20Planner%20Sheet%3C%2FP%3E%3CP%3EIn%20Q2%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(INDEX('Product%20List'!%24A%242%3A%24A%24195%2CSMALL(IF('Product%20List'!%24E%242%3A%24E%24195%3DQ%241%2CIF('Product%20List'!%24T%242%3A%24T%24195%26lt%3B20%2CROW('Product%20List'!%24A%242%3A%24A%24195)-ROW('Product%20List'!%24A%242)%2B1))%2CROWS(Q%242%3AQ2)))%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EConfirm%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20and%20then%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20this%20is%20what%20you%20were%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20access%20to%20Dynamic%20Array%20Formulas%2C%20you%20don't%20need%20special%20keystroke%20Ctrl%2BShift%2BEnter%20to%20confirm%20an%20array%20formula%20and%20in%20that%20case%20Enter%20is%20enough%20to%20confirm%20it.%3C%2FP%3E%3CP%3EAlso%20if%20you%20have%20access%20to%20the%20Dynamic%20Array%20Formulas%2C%20you%20may%20also%20try%20the%20Filter%20function%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Q2%3C%2FP%3E%3CP%3E%3DFILTER('Product%20List'!%24A%242%3A%24A%24195%2C('Product%20List'!%24E%242%3A%24E%24195%3DQ%241)*('Product%20List'!%24T%242%3A%24T%24195%26lt%3B20))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871590%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20help%20for%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871590%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%26nbsp%3Bthis%26nbsp%3Bdid%26nbsp%3Bwork.%26nbsp%3BThanks%20for%20the%20help.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869913%22%20slang%3D%22en-US%22%3EIndex%20Match%20help%20for%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869913%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20use%20Index%20%26amp%3B%20Match%20to%20pull%20two%20different%20cells%20data.%20The%20formula%20seems%20to%20be%20working%2C%20but%20I%20don't%20want%20it%20to%20keep%20pulling%20the%201st%20item%20it%20finds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20formula%3A%3C%2FP%3E%3CP%3E%3DIF('Product%20List'!T2%3AT259%26lt%3B20%2C(INDEX(Code%2CMATCH(%22SM05%22%2C'Product%20List'!E3%3AE38%2C0)%2C1)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20modify%20this%20so%20it%20doesn't%20keep%20pulling%20the%20first%20item%20it%20finds%20in%20T2%3AT259%20that%20is%20less%20than%2020%20and%20matches%20%22SM05%22.%20I%20realize%20I'll%20have%20to%20have%20a%20formula%20in%20multiple%20cells%20for%20each%20result.%20I've%20attempted%20an%20Index%20Aggregate%20formula%20as%20well%20as%20an%20Index%20small%20formula.%20I%20haven't%20made%20them%20correctly%20thus%20far.%20Am%20I%20on%20the%20right%20path%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20any%20advice%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-869913%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871655%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20help%20for%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871655%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413473%22%20target%3D%22_blank%22%3E%40MrMarkSwartz23%3C%2FA%3E!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
MrMarkSwartz23
New Contributor

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

 

 

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

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 

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

Yes, this did work. Thanks for the help.

@Subodh_Tiwari_sktneer 

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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies