Array Multiple criteria

[HWL]

I have an array that is returning the row number of a matched criteria but I need to be able to return the row number of multiple criteria.

Here is the code:
=IF(ROWS(C$2:C2)<=SUM(COUNTIF(A$2:A$21,{"x","d","a","n"})),SMALL(IF(A$2:A$21="a",ROW(A$2:A$21)-ROW(A$2)+2),ROWS(C$2:C2)),"") use CTRL+SHFT+ENTER

As you can see, the data is entered into column A. This array is dragged down column C. Right now it only returns if the data in column A is an "a".

I want it to return the row numbers of "x", "d", & also "n".

I tried an OR statement after the SMALL, like this:

SMALL(IF(or(A$2:A$21="a",A$2:A$21="x",A$2:A$21="d",A$2:A$21="n"),ROW(A$2:A$21)-ROW(A$2)+2),ROWS(C$2:C2))

But this does not work. Any ideas would be great! Thanks

 

[Aladin Akyurek]

So, I was excited to have this spiffy, compact formula but something is wrong because nowthe processing time drags so badly that it is impossible for OpenOffice users to even use this workbook. (Unfortunately my office has people using both Excel & OO so I have to design things to consider both).

RECAP:

I have a huge list (over 15000) rows of surcharges based on country-pair

I need to look at the list and match:

• country-to-country
• country-to-"all"
• "all"-to-country
• "all"-to-"all"

This is the reason I wanted the code to match 4 criteria and return the row numbers of the matches so I can use them to refer to data in columns of those rows. It does so beautifully but the process time is a killer.

Can anyone think of a waaaaaaay more efficient way to do this? Thanks

HWL,

CountIf is an expensive function. I'd like to suggest:

C1:

=SUM(COUNTIF(A$2:A$21,{"x","d","a","n"}))

C2, control+shift=enter, not just enter:

=IF(ROWS(C$2:C2)<=$C$1,
    SMALL(IF(ISNUMBER(MATCH(A$2:A$21,{"x","d","a","n"},0)),
      ROW(A$2:A$21)-ROW(A$2)+2),ROWS(C$2:C2)),"")

Before we try further improvements, care to post one of the formulas using the row numbers the above formula produces?

 

[HWL]

HWL,

CountIf is an expensive function. I'd like to suggest:

C1:

=SUM(COUNTIF(A$2:A$21,{"x","d","a","n"}))

C2, control+shift=enter, not just enter:

=IF(ROWS(C$2:C2)<=$C$1,
    SMALL(IF(ISNUMBER(MATCH(A$2:A$21,{"x","d","a","n"},0)),
      ROW(A$2:A$21)-ROW(A$2)+2),ROWS(C$2:C2)),"")

Before we try further improvements, care to post one of the formulas using the row numbers the above formula produces?

I'm not certain I'm going to be able to use this new code as I would need to do an ISERROR on it which would seem to be worse than the other code, and would be beyond the nesting levels. I'm not certain what you want me to post. The formulas are more complex than what I've actually asked for here. I then adapt it to what I'm actually doing. For example, the code you gave me above ultimately must look like this:

=IF($AQ$2="","", IF(ROWS(AU$2:AU3)<=$AU$1,SMALL(IF(ISNUMBER(MATCH(INDIRECT("'FCL-SURCHARGES-2'!$A$7:$A$"&$AQ$4),$AP$1:$AP$4,0)), ROW(INDIRECT("'FCL-SURCHARGES-2'!$A$7:$A$"&$AQ$4))-ROW(A$2)+2),ROWS(AU$2:AU3)),""))

Here is the currently used code:
=IF($AQ$2="","",IF(ROWS(AU$7:AU7)<=SUM(COUNTIF(INDIRECT("'FCL-SURCHARGES-2'!$A$7:$A$"&$AQ$4),$AP$1:$AP$4)), SMALL(IF(ISNUMBER(MATCH(INDIRECT("'FCL-SURCHARGES-2'!$A$7:$A$"&$AQ$4),$AP$1:$AP$4,0)),
ROW(INDIRECT("'FCL-SURCHARGES-2'!$A$7:$A$"&$AQ$4))-ROW(A$2)+2),ROWS(AU$7:AU7)),"")) CSE

EXPLANATION:

$AQ$2 is a formula, using cell("filename") to check if certain sheets are in focus otherwise don't execute this huge code.

INDIRECT("'FCL-SURCHARGES-2'!$A$7:$A$"&$AQ$4) is where the actual database is. I'm using indirect because the db/list can grow so instead of making some arbitrary high range, I count how many rows and return that to $aq$4. This way we don't spend alot of time processing blank rows. $aq$4 code is: MAX(IF('FCL-SURCHARGES-2'!A7:V45000<>"",ROW('FCL-SURCHARGES-2'!A7:V45000))) CSE

$AP$1:$AP$4 is the actual criteria I'm looking for, it is the {"x","d","a","n"} from our simplistic example. These rows contain country-pairs such as CNUS, ALLUS, CNALL, ALLALL (such as China to US, ALL to US...)

Ultimately, I am attempting to return the row numbers of all the matches for the 4 country-pair criterion. The blue code above does that wonderfully, but very slowly.

 

[Aladin Akyurek]

What is the name of the sheet housing the formulas you posted?

Are you entering the first instance of the formula in AU2?

Which column in 'FCL-SURCHARGES-2'!A7:V45000 is numeric and the most complete?

Is there any sheet other than 'FCL-SURCHARGES-2' the formula must reference in order to process data?