 SOLVED

New Contributor

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

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

# Re: Using Index+Match with Or function to find nth Value

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,

# Re: Using Index+Match with Or function to find nth Value

@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. 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.

# Re: Using Index+Match with Or function to find nth Value

For example:

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