SOLVED

Using Index+Match with Or function to find nth Value

%3CLINGO-SUB%20id%3D%22lingo-sub-3191917%22%20slang%3D%22en-US%22%3EUsing%20Index%2BMatch%20with%20Or%20function%20to%20find%20nth%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3191917%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%20I%20tried%20to%20find%20the%20solution%20but%20still%20cant.%3CBR%20%2F%3EI%20have%20an%20data%20list%20as%20below.%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20229px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F349912iDB3ED77C380120D7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3EI%20want%20to%20get%20the%20nth%20value%20which%20match%20below%26nbsp%3Bcriteria%3A%3CBR%20%2F%3Eif%20A%20%3D%20%22Ongoing%22%20and%20B%20%3D%20%22QU%22%20or%20%22TN%22%2C%20result%20as%20C2%2C%20otherwise%20%22%22%3CBR%20%2F%3E%3CBR%20%2F%3EI%20got%20a%20formula%20which%20can%20use%20index%20%2B%20match%20with%20or%20function%2C%20but%20how%20to%20find%20nth%20value%20with%20those%20criteria%3F%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20are%20the%20formula%20I'm%20using%20now%3A%3CBR%20%2F%3E%7B%3DINDEX(%24C%3A%24C%2CMATCH(1%2C(((%22QU%22%3D%24B%3A%24B)%2B(%22TN%22%3D%24B%3A%24B))%26gt%3B0)*(%22Ongoing%22%3D%24A%3A%24A)%2C0)%2C1)%7D%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20help%20on%20this%2C%20thank%20you%20very%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3191917%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3192063%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Index%2BMatch%20with%20Or%20function%20to%20find%20nth%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3192063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313285%22%20target%3D%22_blank%22%3E%40Jess_Ora%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20the%20value%20of%20n%20in%20(for%20example)%20E2.%3C%2FP%3E%0A%3CP%3EIn%20F2%3A%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(%24C%242%3A%24C%246%2CSMALL(IF((%24A%242%3A%24A%246%3D%22Ongoing%22)*((%24B%242%3A%24B%246%3D%22QU%22)%2B(%24B%242%3A%24B%246%3D%22TN%22))%2CROW(%24C%242%3A%24C%246)-ROW(%24C%242)%2B1)%2CE2))%2C%22%22)%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20have%20Microsoft%20365%20or%20Office%2020221%2C%20confirm%20the%20formula%20with%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3196051%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Index%2BMatch%20with%20Or%20function%20to%20find%20nth%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3196051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BMillion%20thanks%20for%20your%20help!!!%20It%20really%20helps%20on%20my%20daily%20job...%3CBR%20%2F%3E%3CBR%20%2F%3EBesides%2C%20may%20I%20ask%20one%20more%20question%3A%3CBR%20%2F%3EI%20have%20a%20data%20list%20as%20column%20A%20to%20column%20D.%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture2.PNG%22%20style%3D%22width%3A%20982px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350144iBCC1F07583105EFE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture2.PNG%22%20alt%3D%22Capture2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3EIs%20it%20possible%20to%20sum%20the%20total%20value%20base%20on%20column%20B%20(%25)%20%26amp%3B%20column%20C%20(Date)%3F%3CBR%20%2F%3EFor%20example%2C%20I%20want%20to%20know%20total%20value%20from%20today%20(F1)%20to%202%20months%20later%20(i.e.%3A%2021%20Apr%202022)%20with%2050%25%3CBR%20%2F%3E(and%20it%20should%20comes%20a%20total%20value%2010002%2B10003%20%3D%2020005)%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3198470%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Index%2BMatch%20with%20Or%20function%20to%20find%20nth%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3198470%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313285%22%20target%3D%22_blank%22%3E%40Jess_Ora%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS(%24D%242%3A%24D%246%2C%20%24C%242%3A%24C%246%2C%20%22%26gt%3B%3D%22%26amp%3BTODAY()%2C%20%24C%242%3A%24C%246%2C%20%22%26lt%3B%22%26amp%3BEDATE(TODAY()%2C%202)%2C%20%24B%242%3A%24B%246%2C%2050%25)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone, I tried to find the solution but still cant.
I have an data list as below.
Capture.PNG
I want to get the nth value which match below criteria:
if A = "Ongoing" and B = "QU" or "TN", result as C2, otherwise ""

I got a formula which can use index + match with or function, but how to find nth value with those criteria?

Here are the formula I'm using now:
{=INDEX($C:$C,MATCH(1,((("QU"=$B:$B)+("TN"=$B:$B))>0)*("Ongoing"=$A:$A),0),1)}

Please help on this, thank you very much!!

3 Replies
best response confirmed by Jess_Ora (New Contributor)
Solution

@Jess_Ora 

Enter the value of n in (for example) E2.

In F2:

=IFERROR(INDEX($C$2:$C$6,SMALL(IF(($A$2:$A$6="Ongoing")*(($B$2:$B$6="QU")+($B$2:$B$6="TN")),ROW($C$2:$C$6)-ROW($C$2)+1),E2)),"")

If you don't have Microsoft 365 or Office 20221, confirm the formula with Ctrl+Shift+Enter.

See the attached sample workbook,

@Hans Vogelaar Million thanks for your help!!! It really helps on my daily job...

Besides, may I ask one more question:
I have a data list as column A to column D.
Capture2.PNG
Is it possible to sum the total value base on column B (%) & column C (Date)?
For example, I want to know total value from today (F1) to 2 months later (i.e.: 21 Apr 2022) with 50%
(and it should comes a total value 10002+10003 = 20005)

Thanks again.

@Jess_Ora 

 

For example:

 

=SUMIFS($D$2:$D$6, $C$2:$C$6, ">="&TODAY(), $C$2:$C$6, "<"&EDATE(TODAY(), 2), $B$2:$B$6, 50%)