Array formula and parentheses

hi guys,

this is a cross post from http://www.ozgrid.com/forum/showthread.php?t=149129 - just needed a bit of further advice so I thought i'd get some more eyes looking at the issue.

I have an array formula - =INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)),1))

File attached now.

'Risk Register Testing' is another sheet in the workbook which contains the data table I want to query, 6.2 is the matching term and 1 is the occurence I wish to retrieve. However, I am getting a #n/a error.

The data table in 'Risk Register Testing' is constructed through a series of if statement to clean the data and remove blank/erroneous cells.

When I copy and paste the data into the sheet that contains my array formula and recreate my formula, it works absolutely fine.

Really puzzled here...

Also is there anyway to stop the parentheses from disappearing everytime the cell is amended or clicked into? This spreadsheet will go out to a lot of end users who will not know to do the ctrl-shift-enter.

Any help offered will be greatly appreciated

You might want to attach your file here as well as a lot of people won't want to join OzGrid (can't think why).

As for your question about the {} the only way to tell Excel it's an array formula is with Ctrl+Shift+Enter so no you can't get round that. How about protecting the worksheet so the formula can't be edited?

Dom

Originally Posted by Domski

You might want to attach your file here as well as a lot of people won't want to join OzGrid (can't think why).

As for your question about the {} the only way to tell Excel it's an array formula is with Ctrl+Shift+Enter so no you can't get round that. How about protecting the worksheet so the formula can't be edited?

Dom

Apologies for that, the file is now attached to the original post.

Thank you for clearing up the {} issue as well :o)

incidently
ROW('Risk Register Testing'!AA11:AA85)
could just be
ROW(11:85)

row(a11:a85)
row(sheet20!z11:sheet20!z85)
row(11:85)
all produce the same array ,a list of numbers from 11 through to 85
it has nothing to do with the sheet they are on

The specific reason that your formula returns #N/A is that you have an #N/A error in the range 'Risk Register Testing'!AA11:AA85 (in AA34). You either need to prevent that at source (change formulas in 'Risk Register Testing'!AA11:AA85) or alter your other formula to ignore #N/A

In any case your formula won't extract the correct value. The first 6.2 is in row 16 so the SMALL part of the formula returns 16, so this.....

=INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)),1))

....becomes

=INDEX('Risk Register Testing'!AB11:AB85,16)

the 16th value in the range 'Risk Register Testing'!AB11:AB85 is in 'Risk Register Testing'!AB26, I assume you want the value from 'Risk Register Testing'!AB16. The best way to do that is to change the formula to this

=INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)-ROW('Risk Register Testing'!AA11)+1),1)

....or to ignore error values

=INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF(ISNUMBER('Risk Register Testing'!AA11:AA85),IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)-ROW('Risk Register Testing'!AA11)+1)),1))

Note: I think there is a value in referring to the other worksheet within the ROW function. In the formula above if some rows are added or deleted below or within the range the formula will adjust to the new range, hence it's more robust

Originally Posted by daddylonglegs

The specific reason that your formula returns #N/A is that you have an #N/A error in the range 'Risk Register Testing'!AA11:AA85 (in AA34). You either need to prevent that at source (change formulas in 'Risk Register Testing'!AA11:AA85) or alter your other formula to ignore #N/A

In any case your formula won't extract the correct value. The first 6.2 is in row 16 so the SMALL part of the formula returns 16, so this.....

=INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)),1))

....becomes

=INDEX('Risk Register Testing'!AB11:AB85,16)

the 16th value in the range 'Risk Register Testing'!AB11:AB85 is in 'Risk Register Testing'!AB26, I assume you want the value from 'Risk Register Testing'!AB16. The best way to do that is to change the formula to this

=INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)-ROW('Risk Register Testing'!AA11)+1),1)

....or to ignore error values

=INDEX('Risk Register Testing'!AB11:AB85,SMALL(IF(ISNUMBER('Risk Register Testing'!AA11:AA85),IF('Risk Register Testing'!AA11:AA85=6.2,ROW('Risk Register Testing'!AA11:AA85)-ROW('Risk Register Testing'!AA11)+1)),1))

Note: I think there is a value in referring to the other worksheet within the ROW function. In the formula above if some rows are added or deleted below or within the range the formula will adjust to the new range, hence it's more robust

Thank you for your help DLL.

I am still a little confused. If I understood correctly, will your formula return the value from the 16th row in the array or the 16th match for my search term denoted here as 6.2?

I am specifically after an array formula that returns Nth occurences i.e. a Vlookup type affair where I can retrieve the 2nd or 3rd or 4th match and not just the first one.

HSU - Please don't quote whole posts as per the forum rules:

12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

Dom

Yes, the formulas I suggested still return the first match.....and if you change the 1 in the small function to a 2 or 3 or n then it will return the 2nd, 3rd or nth match.

The point I was making was that your formula would return a value from the wrong row (specifically 10 rows too far down, not necessarily a row where the AA column was 6.2), my suggestions fix that - the second one also allows error values in the range 'Risk Register Testing'!AA11:AA85

Ok, I kind of follow you now. For some reason, the formula was pulling up random results if I didnt enter the ctrl+shift+enter parentheses, but it seems to work fine now.

My final question is, the formula has to be able to handle n number of matches and display them on a grid (which I have created). I don't expect n to be greater than 16, but where I only have 8 matches, the 9th formula in the 9th grid will not find a 9th match and therefore #NUM! error. What is the best way to error handle in this instance?

In the past I have used an ISERROR approach, but the way I know how to write them, means I have to enter the formula provided twice, once for the error check and then again for the false outcome. Is there a cleaner and simpler way of doing this?

In Excel 2007 you can use IFERROR to avoid repeating the formula, e.g. to get a blank instead of an error

=IFERROR(original_formula,"")

great! will this work for 2003 users too? I don't think everyone has 2007 in the company yet.

No need for reply now - answered my own question. Thanks.

donjoe,

Welcome to the board.

Per the forum rules please start your own thread rather than posting questions in someone elses.

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

Dom

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.