# Array formula to return multiple values based on various (OR) criteria

1. ## Array formula to return multiple values based on various (OR) criteria

Ok, so I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....

My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)

I also have a list of engineers, with a column for their home postcode.

I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered.

So far I have this, which finds me all the engineers for just one postcode area.

So along with the unlined bit above, I want to add in if(or(list = G2, list = G3, list = G4....) etc. The list in G expands down from G2 to about G15, depending on the amount of postcodes that are returned.

2. ## Re: Array formula to return multiple values based on various (OR) criteria

Hi,

Unfortunately, without seeing the actual workbook (or at least a mocked-up, dummy version which llustrates the same query) it will be quite difficult to help you.

Regards

3. ## Re: Array formula to return multiple values based on various (OR) criteria

I don't think you can use things like AND()/OR() in array formulas, maybe try something like:
=IF(SUM(IF(list=G2:G15,1,0))>0,true,false)

4. ## Re: Array formula to return multiple values based on various (OR) criteria

Attached dummy file. I know the format is a bit messy - for now I just want to get it to work before I make it presentable.

Postcode finder.xlsx

5. ## Re: Array formula to return multiple values based on various (OR) criteria

Hi,

Change your array formula in B6 to:

=IFERROR(INDEX('Engineer Data & Referencing'!\$A\$2:\$A\$312,SMALL(IF(ISNUMBER(MATCH('Engineer Data & Referencing'!\$D\$2:\$D\$312,\$G\$2:\$G\$10,0)),ROW('Engineer Data & Referencing'!\$A\$2:\$A\$312)-MIN(ROW('Engineer Data & Referencing'!\$A\$2:\$A\$312))+1),ROWS(\$1:1))),"")

Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1