Using textjoin and If function to search between two values

Skrej

Board Regular
Joined
May 31, 2013
Messages
159
Office Version
  1. 365
Platform
  1. 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.

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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
If you're using excel 2019 then
Excel Formula:
=TEXTJOIN(", ",TRUE,IF(O$4:O$15>0,IF(O$4:O$15<1,J$4:J$15,""),""))
 
Upvote 0
Solution
Another way would be
Excel Formula:
=TEXTJOIN(", ",TRUE,IF((O$4:O$15>0)*(O$4:O$15<1),J$4:J$15,""))
As it's an array, you cannot use AND you either need to nest them like Jason, or you can multiply them.
 
Upvote 0
It looks like I'm using 365. I marked Jason's solution simply because I tried it first, but Fluff's worked equally as well.

I did also update my settings.

Thanks you both.
 
Upvote 0
With office 365
Excel Formula:
=TEXTJOIN(", ",TRUE,FILTER(J$4:J$15,(O$4:O$15>0)*(O$4:O$15<1),""))
 
Upvote 0
I did also update my settings.
Thanks for that & glad we could help.

Also, as you are on 365, you no longer need to use CSE on array formulae.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top