Array Formula Question

jamilm

Well-known Member
Joined
Jul 21, 2011
Messages
740
Gurus,

based one what of the array formula, i learned from Aladin. i had a criteria as part of my mega formula which was --ISNUMBER(FIND("America",CountryRange)))
Now, i want to change this to seach also for text "Kingdom" so i changed it to --ISNUMBER(FIND({"America","Kingdom"},CountryRange))) but it did not work. gives me value error.


am i missing something in the formula?

any help is appreciated.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
What about: =IsNumber(Match({"America","Kingdom"},CountryRange,0))
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
It works for me, can you post the entire formula that it's being used in?
 

jamilm

Well-known Member
Joined
Jul 21, 2011
Messages
740
What about: =IsNumber(Match({"America","Kingdom"},CountryRange,0))


match does not help on this case. becuase part of the string in the cell is "America" or "Kingdom" the cell value is like UnitedStatesofAmerica or UnitedKingdom
 

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341

ADVERTISEMENT

try "*America*","*Kingdom*"
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Without seeing the 'entire' formula, this is only a stab in the dark...

But try

--ISNUMBER(FIND({"America";"Kingdom"},CountryRange)))
 

jamilm

Well-known Member
Joined
Jul 21, 2011
Messages
740
Hi Jonmo1

thanks for looking into this. appreciated.

the complete formula was =SUMPRODUCT(AMOUNT,--ISNUMBER(MATCH(FISCAL_YEAR,{2012,2011,2010,2009,2008,2007,2006,2005,2004},0)),1-ISNUMBER(MATCH(ACCOUNTING_PERIOD,{0,221,222},0)),1-(FUND="BBB"),--ISNUMBER(MATCH(LEFT(ACCOUNT&"#####",5),{"50505"},0)),1-ISNUMBER(FIND("America",CountryRange)))

the last criteria to exclude/ignore the AMOUNT if "America" text was found as part of the cell in the CountryRange . i wanted to put one more condition that if either "America" or "Kingdom" was found then exclude/ignore the amount.

i managed to get it work with adding one more criteria at the end like this the one in red =SUMPRODUCT(AMOUNT,--ISNUMBER(MATCH(FISCAL_YEAR,{2012,2011,2010,2009,2008,2007,2006,2005,2004},0)),1-ISNUMBER(MATCH(ACCOUNTING_PERIOD,{0,221,222},0)),1-(FUND="BBB"),--ISNUMBER(MATCH(LEFT(ACCOUNT&"#####",5),{"50505"},0)),1-ISNUMBER(FIND("America",CountryRange)),1-ISNUMBER(FIND("Kingdom",CountryRange)))

and this one works for me. but i was wondering how to incorporate it like in simplified version 1-ISNUMBER(FIND({"America","Kingdom"},CountryRange))) perhaps this means it triggers like AND not OR.

any idea?




It works for me, can you post the entire formula that it's being used in?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,811
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top