## Advanced Filter with array

agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

### Advanced Filter with array

Hello. (2003)
Is it possible to use an array formula as a calculated criteria for Advanced Filter? I've array entered this formula as a criteria:
=SUM(IF(VLOOKUP(\$A6,staff3,COLUMN(B6:J6),0)<>B6:J6,1,0))
'staff3' is a table on a separate worksheet.
The formula returns TRUE if a value in column B to J differs to the corresponding value in table 'staff3', and it works if I drag it down a column.

(This follows on from a recent question I've asked, but I now just need to know if the Advanced Filter can understand an array formula.)
Thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

HansV
Posts: 78629
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Advanced Filter with array

Your formula doesn't return TRUE or FALSE, it returns the number of differences. If you enter a column heading (field name) above the formulas and use the same column heading in the criteria range, you can filter for a specific number of differences, for example 0 if you want to see only the records with no differences, or >0 if you want to see only the records with differences.
But since the formula doesn't return TRUE or FALSE, you can't use it in a calculated condition in the Criteria range without a column heading.
Best wishes,
Hans

agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

### Re: Advanced Filter with array

Thank you. I thought that any numerical value other than zero was 'true'? Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

HansV
Posts: 78629
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Advanced Filter with array

That's true, but it doesn't work for calculated criteria as far as I can tell, I think you'll need to add a calculated column to your table.
Best wishes,
Hans

agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

### Re: Advanced Filter with array

Hi.
I think you're right. I tried wrapping an IF function to return a value of TRUE or FALSE, But I'm getting the impression that Advanced Filter cannot handle an
array formula. Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.