# Array Formula Issue

#### tyija1995

##### Well-known Member
Hi all,

I have a field of integers residing in cells B3:B26 (randomly ranging from 2 to 22)

I have made an array formula to return a list of integers that are greater than or equal to 10 (in numerical ascending order)

However when I try to do the same but with an AND formula to get numbers between 10 and 15 inclusive, I get a #VALUE ! error.

Working formula (cell C3 - array for greater or equal to 10):
=SMALL(IF(\$B\$3:\$B\$26>=10,\$B\$3:\$B\$26,""),ROW()-2)
CSE and dragged down until I start getting #NUM errors - then I just remove.

Broken formula (cell D3 - array for between 10 and 15 inclusive):
=SMALL(IF(AND(\$B\$3:\$B\$26>=10,\$B\$3:\$B\$26<=15),\$B\$3:\$B\$26,""),ROW()-2)
CSE - #VALUE ! returned.

Any ideas? I'm not sure why the array formula doesn't like the AND statement.

Thanks

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### gaz_chops

##### Well-known Member
Try

=SMALL(IF((\$B\$3:\$B\$26>=10)*(\$B\$3:\$B\$26<=15),\$B\$3:\$B\$26),ROW()-2)

#### steve the fish

##### Well-known Member
Its because AND doesnt produce an array. If you need AND use multiplication.

#### tyija1995

##### Well-known Member
Thank you gaz - your array formula worked perfectly.

Thank you steve - I am quite new to using array formulas so this is good to know.

Have a good one guys.

#### Fluff

##### MrExcel MVP, Moderator
For what it's worth, if you ever need to use OR with an array you would do it the same way as the AND but use + rather than *

#### tyija1995

##### Well-known Member
Thanks Fluff, that did cross my mind and I assumed it would be an addition symbol - I tested by setting criteria as 10 - 15 OR 20+ for my array and it worked, so thanks for the clarification

You're welcome

1,102,778
Messages
5,488,823
Members
407,658
Latest member
Arias610

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...