Forum Discussion
Using Index+Match with Or function to find nth Value
Hello everyone, I tried to find the solution but still cant.
I have an data list as below.
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!!
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,
3 Replies
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,
HansVogelaar 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.
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.For example:
=SUMIFS($D$2:$D$6, $C$2:$C$6, ">="&TODAY(), $C$2:$C$6, "<"&EDATE(TODAY(), 2), $B$2:$B$6, 50%)
