Array Formula Issue

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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
 

Some videos you may like

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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
Its because AND doesnt produce an array. If you need AND use multiplication.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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
Joined
Jun 12, 2014
Messages
42,903
Office Version
365
Platform
Windows
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
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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 :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,903
Office Version
365
Platform
Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top