Home

Index MATCH formula returns the next value in an array

%3CLINGO-SUB%20id%3D%22lingo-sub-831836%22%20slang%3D%22en-US%22%3EIndex%20MATCH%20formula%20returns%20the%20next%20value%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831836%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EWould%20appreciate%20if%20I%20can%20get%20some%20help%20on%20this%20problem.%3C%2FP%3E%3CP%3EIf%20you%20please%20look%20at%20the%20attached%20excel%20file%2C%20I%20am%20trying%20to%20lookup%20value%20from%20one%20data%20set%2C%20into%20another.%20However%2C%20the%20index%20match%20formula%20is%20only%20giving%20the%20lowest%20value%2010%20(or%20even%20if%20I%20don't%20write%200%2C%20the%20index%20match%20formula%20will%20give%20me%20the%20highest%20value%20i.e.%2030).%20Is%20there%20a%20way%20to%20write%20a%20formula%20so%20that%20if%20one%20value%20is%20selected%20from%20the%20array%2C%20the%20index%20match%20formula%20does%20not%20select%20it%20again%3F%20e.g.%20if%2010%20is%20selected%20then%20the%20next%20value%20that%20the%20index%20match%20formula%20picks%20up%20is%2030%20(and%20not%2010)%3C%2FP%3E%3CP%3EWould%20appreciate%20any%20help!%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-831836%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-831885%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20MATCH%20formula%20returns%20the%20next%20value%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401258%22%20target%3D%22_blank%22%3E%40Umair_Yousuf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20the%20following%20%3CSTRONG%3EArray%20Formula%3C%2FSTRONG%3Ewhich%20requires%20confirmation%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3Einstead%20of%20Enter%20alone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20C3%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24G%243%3A%24G%245%2CSMALL(IF(%24E%243%3A%24E%245%3DA3%2CIF(%24F%243%3A%24F%245%3DB3%2CROW(%24E%243%3A%24E%245)-ROW(%24E%243)%2B1))%2CROWS(C%243%3AC3)))%2C%22Record%20not%20in%20File%20B%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EConfirm%20it%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3Eand%20then%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831916%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20MATCH%20formula%20returns%20the%20next%20value%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831916%22%20slang%3D%22en-US%22%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%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%20It%20worked!%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831917%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20MATCH%20formula%20returns%20the%20next%20value%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401258%22%20target%3D%22_blank%22%3E%40Umair_Yousuf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3EPlease%20take%20a%20minute%20to%20accept%20the%20solution%20posted%20as%20an%20Answer%2C%20that%20would%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831944%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20MATCH%20formula%20returns%20the%20next%20value%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831944%22%20slang%3D%22en-US%22%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%3CP%3E%3CSPAN%3Eapologies%20I%20might%20have%20jumped%20the%20gun%20there!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20a%20follow-up%20question%20please.%20If%20you%20looked%20at%20the%20excel%20file%2C%20I%20changed%20name%20of%20person%20from%20Jamie%20to%20josh%20and%20I%20excepted%20the%20formula%20to%20pick%20the%20value%20'12'%20in%20the%20third%20row.%20But%20that's%20not%20what%20the%20formula%20is%20doing%20-%20any%20idea%20why%20this%20is%20happening%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831958%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20MATCH%20formula%20returns%20the%20next%20value%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831958%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401258%22%20target%3D%22_blank%22%3E%40Umair_Yousuf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENever%20mind.%3C%2FP%3E%3CP%3EPlease%20try%20the%20following%20one...%3C%2FP%3E%3CP%3EIn%20C3%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24G%243%3A%24G%245%2CSMALL(IF(%24E%243%3A%24E%245%3DA3%2CIF(%24F%243%3A%24F%245%3DB3%2CROW(%24E%243%3A%24E%245)-ROW(%24E%243)%2B1))%2CCOUNTIFS(%24E%243%3AE3%2CA3%2C%24F%243%3AF3%2CB3)))%2C%22Record%20not%20in%20File%20B%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Umair_Yousuf
New Contributor

Hello

Would appreciate if I can get some help on this problem.

If you please look at the attached excel file, I am trying to lookup value from one data set, into another. However, the index match formula is only giving the lowest value 10 (or even if I don't write 0, the index match formula will give me the highest value i.e. 30). Is there a way to write a formula so that if one value is selected from the array, the index match formula does not select it again? e.g. if 10 is selected then the next value that the index match formula picks up is 30 (and not 10)

Would appreciate any help!

 

 

5 Replies

@Umair_Yousuf 

You may try the following Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

 

In C3

=IFERROR(INDEX($G$3:$G$5,SMALL(IF($E$3:$E$5=A3,IF($F$3:$F$5=B3,ROW($E$3:$E$5)-ROW($E$3)+1)),ROWS(C$3:C3))),"Record not in File B")

Confirm it with Ctrl+Shift+Enter and then copy it down.

 

@Subodh_Tiwari_sktneer 

 

Thank you! It worked!

Cheers

@Umair_Yousuf 

You're welcome! Glad it worked as desired.

Please take a minute to accept the solution posted as an Answer, that would mark your question as Solved.

@Subodh_Tiwari_sktneer 

apologies I might have jumped the gun there!

I have a follow-up question please. If you looked at the excel file, I changed name of person from Jamie to josh and I excepted the formula to pick the value '12' in the third row. But that's not what the formula is doing - any idea why this is happening?

Thank you

@Umair_Yousuf 

Never mind.

Please try the following one...

In C3

=IFERROR(INDEX($G$3:$G$5,SMALL(IF($E$3:$E$5=A3,IF($F$3:$F$5=B3,ROW($E$3:$E$5)-ROW($E$3)+1)),COUNTIFS($E$3:E3,A3,$F$3:F3,B3))),"Record not in File B")

 

 

 

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies