Skrej
Board Regular
- Joined
- May 31, 2013
- Messages
- 159
- Office Version
- 365
- Platform
- Windows
I have the following array formula to check a range of values in column 0 to list all corresponding name values in Col J that meet the requirement (being equal to a given number, in this example, zero). This works fine when I'm just looking for a single number in Col O, and gives me a list of comma separated names that match.
However, In some cases I need to search for values between two numbers. I tried to modify it as follows to show those names with values less than 1 but greater than zero. However, I don't have my syntax correct, because it just returns a single blank cell. I confirmed this by putting an X between those quote marks, and it did write a single X.
Anyone have some guidance in how to correctly write it so it still lists all matching values, but with a criteria of being between two values? Again, these are entered as array formulas. I suspect it's something obvious I'm overlooking.
Thanks in advance.
Excel Formula:
=TEXTJOIN(", ",TRUE,IF(O$4:O$15=0,J$4:J$15,""))
However, In some cases I need to search for values between two numbers. I tried to modify it as follows to show those names with values less than 1 but greater than zero. However, I don't have my syntax correct, because it just returns a single blank cell. I confirmed this by putting an X between those quote marks, and it did write a single X.
Excel Formula:
=TEXTJOIN(", ",TRUE,IF(AND(O$4:O$15>0,O$4:O$15<1),J$4:J$15,""))
Anyone have some guidance in how to correctly write it so it still lists all matching values, but with a criteria of being between two values? Again, these are entered as array formulas. I suspect it's something obvious I'm overlooking.
Thanks in advance.