Index Collect with OR

JCLavoro

Hi all!

I would like my Index Formula to populate if the content of a referenced cell is A OR B. Otherwise I want IFERROR to populate 'Not found'.

I get the separate formulas to work but when I try to combine them with OR, I get #InvalidDataType and #InvalidValue. I understand why I am getting the latter, but can't riddle the former in order to get the formula to do what I want it to do.

These two formulas work separately:

=IFERROR(INDEX(COLLECT({DepositStatus}, {DepositStatus}, "N/A", {NonXXemail}, [Home institution email]@row), 1), "Not found")

=IFERROR(INDEX(COLLECT({DepositStatus}, {DepositStatus}, "Received", {NonXXemail}, [Home institution email]@row), 1), "Not found")

Can anyone help me get the syntax right for an OR formula if it's even possible? Many thanks!

KPH

Hi @JCLavoro

How about adding an OR function to the criterion:

Change this part in bold

=IFERROR(INDEX(COLLECT({DepositStatus}, {DepositStatus}, "N/A",
{NonXXemail}, [Home institution email]@row), 1), "Not found")

To this

=IFERROR(INDEX(COLLECT({DepositStatus}, {DepositStatus}, OR(@cell = "N/A", @cell = "Received"), 

{NonXXemail}, [Home institution email]@row), 1), "Not found")

(sorry is this is hard to read - the comments box messes up the formulas with @cell in them unless I make a code block and then the bold is hard to see).

Here is a screen shot

KPH

Hi @JCLavoro

How about adding an OR function to the criterion:

Change this part in bold

=IFERROR(INDEX(COLLECT({DepositStatus}, {DepositStatus}, "N/A",
{NonXXemail}, [Home institution email]@row), 1), "Not found")

To this

=IFERROR(INDEX(COLLECT({DepositStatus}, {DepositStatus}, OR(@cell = "N/A", @cell = "Received"), 

{NonXXemail}, [Home institution email]@row), 1), "Not found")

(sorry is this is hard to read - the comments box messes up the formulas with @cell in them unless I make a code block and then the bold is hard to see).

Here is a screen shot

JCLavoro

Dear @KPH, thanks so much! This worked exactly as I needed. I appreciate your help!

KPH

Great news!