Using an Array of numbers to search within a cells contents

[cbarryb]

Hi All,

One quick question about an array of numbers within a search function - Can this be done?

I have this bunch of nested iferror(mid(seach functions looking for all types of invoice numbers in the cells on our statements, making them easier to find. One problem I face is when a number changes, i.e. goes from 91200000 to 91300000, I have to add another iferror to the macro that inserts this formula.

is there a way to have this insert the formula, but using an array:

This is the main formula

=IFERROR(MID($B16,SEARCH("??????PW",$B16),8),IFERROR(MID($B16,SEARCH("??????IW",$B16),8),IFERROR(MID($B16,SEARCH("??????PV",$B16),8),IFERROR(MID($B16,SEARCH("911?????",$B16),8),IFERROR(MID($B16,SEARCH("2001????",$B16),8),IFERROR(MID($B16,SEARCH("908?????",$B16),8),IFERROR(MID($B16,SEARCH("910?????",$B16),8),IFERROR(MID($B16,SEARCH("912?????",$B16),8),IFERROR(MID($B16,SEARCH("909?????",$B16),8),"")))))))))

Could I use an array like this, or am I just in a dream world?

=IFERROR(MID($B24,SEARCH("91{1,2,3,4,5,6,7,8,9}?????",$B24),8)

Many thanks

 

[jasonb75]

Something like this should do it, you might need to array confirm the formula using Ctrl Shift Enter if you're working with excel 2019 or older.

=IFERROR(LOOKUP("zzz",MID($B24,SEARCH("91"&{1,2,3,4,5,6,7,8,9}&"?????",$B24),8)),"")

 

[cbarryb]

Something like this should do it, you might need to array confirm the formula using Ctrl Shift Enter if you're working with excel 2019 or older.

=IFERROR(LOOKUP("zzz",MID($B24,SEARCH("91"&{1,2,3,4,5,6,7,8,9}&"?????",$B24),8)),"")

Thank you, that has worked for me, Top Person. I'll aslo update the version of Office I use, its 365