Modifying an Array?

So I am fairly well-versed in Excel (I think?), but I don't know much about arrays. I'm trying to modify a spreadsheet made by someone else.

Here's the formula:
=IF(ISERROR(INDEX(Database!$A$1:$B$3000,SMALL(IF(Database!$CE$1:$CE$3000=$B$126,ROW(Database!$A$1:$A$3000)),ROW(234:234)),2)),"",INDEX(Database!$A$1:$B$3000,SMALL(IF(Database!$CE$1:$CE$3000=$B$126,ROW(Database!$A$1:$A$3000)),ROW(234:234)),2))

It looks to me like it's accessing the Database tab and trying to find rows where the data in column CE matches the data in cell B126. This is good, but I don't want it to include that row if column CS (Database tab) has a certain value. Is there an easy way to do this?

Also - this is a massive spreadsheet that takes several minutes to open. Are arrays formulas that require a lot of CPU power? If so, I'm hoping deleting a lot of these rows with the arrays will help the performance of this particular spreadsheet.

Thanks in advance to anyone who is able to help!

Are you just trying to return a single column? if not what columns do you want?
Also what is the "certain" value in col CS?

This sheet creates a list of past due accounts and various data from each account (all from the Database tab).

Here are one of the formulas to the right of the formulas above. This formula returns the value from the Database tab, column H for that account. There are 8 of these formulas, each returning the data for that account from a different column on the Database tab.
=INDEX(Database!$C$12:$C$3000,MATCH($B382,Database!$B$12:$B$3000,0))&CHAR(10)&INDEX(Database!$H$12:$H$3000,MATCH($B382,Database!$B$12:$B$3000,0))

Back to the original formula - I'd like it to exclude the account if Database column CS has a value of " CO".

It would also be great if this list was sorted based on the value in Database column CO, instead of in the same order as these accounts appear on the Database tab, but that's not a necessity.

Originally Posted by AliGW

Welcome to the forum.

Without the workbook, a guess (clear ALL formula results first)

=IFERROR(FILTER(Database!$A$1:$B$3000,(Database!$CE$1:$CE$3000=$B$126)*(Database!$CS$1:$CS$3000<>certain_value)),"")

Replace the bit in red with the value you wish to omit.

Enter normally with just ENTER.

Thanks for the welcoming message and the reply! Will those additional arguments in the formula require a lot of additional CPU power? I'm not familiar with the FILTER function. Maybe I'm not as well-versed in Excel as I thought...

How about

Formula:

Please Login or Register  to view this content.

Originally Posted by Fluff13

How about

Formula:

Please Login or Register  to view this content.

When I tried this I'm getting #SPILL! error

You need to clear all the rows below the formula.

Originally Posted by AliGW

Welcome to the forum.

Without the workbook, a guess (clear ALL formula results first)

=IFERROR(FILTER(Database!$A$1:$B$3000,(Database!$CE$1:$CE$3000=$B$126)*(Database!$CS$1:$CS$3000<>certain_value)),"")

Replace the bit in red with the value you wish to omit.

Enter normally with just ENTER.

I am also getting #SPILL! error when I try this formula. I must be doing something wrong.

See my previous post.

Originally Posted by Fluff13

You need to clear all the rows below the formula.

I thought that might be it. Now I'm trying it on the first row in the list and still getting the same error though.

Now I'm trying it on the first row in the list and still getting the same error though.

Are you putting the formula in a Table, or a normal range?

Ok, I deleted everything below and started with the first row...

I don't have a clue how this thing works, but it does! Thank you! I've always pasted formulas down as many rows as I needed it... never used one that can be put into the first row only and it populates the rows below it...

I'd also like to do the same to some areas above (basically, past due accounts are divided into four sections: 30-59 days past due, 60-89 days, 90-119, and 120+).

Many of the accounts over 120 days past due have been "charged off", hence the exclusion if they have the "CO" in the CS column.

For example, the 60-89 formula is (B59 = "60-89"):
=IF(ISERROR(INDEX(Database!$A$1:$B$3000,SMALL(IF(Database!$CE$1:$CE$3000=$B$59,ROW(Database!$A$1:$A$3000)),ROW(2:2)),2)),"",INDEX(Database!$A$1:$B$3000,SMALL(IF(Database!$CE$1:$CE$3000=$B$59,ROW(Database!$A$1:$A$3000)),ROW(2:2)),2))

I'd also like these sorted by days past due, but there's no need to filter out the ones with "CO" in the CS column.

I am also needing to hide any blank rows between the sections (i.e., 30-59 and 60-89 sections). This was formerly being done with a macro which hid any rows where #N/A appeared in column B (where the new formula will be).

I'd be happy to share the workbook, but unfortunately it contains a large amount of confidential information.

Originally Posted by AliGW

You can desensitise the workbook - we don't need to see the whole thing, just a representative sample. It's going to be impossible without seeing the layout. A screenshot will NOT suffice!

How do I desensitise it?