INDEX SMALL problem

**jwillis07** · 02-09-2015, 08:39 AM

Hi, can someone explain why this array formula works fine...

=IFERROR(INDEX(INDIRECT("Surveys!L6586:L7345"),SMALL(IF(INDIRECT("Surveys!L6586:L7345")<>"",ROW($E$4:$E$203)-ROW($E$4)+1,""),ROW($E$4:$E$203)-ROW($E$4)+1)),"")

But when I change the INDEX range to "Surveys!P6586:P7345" it doesn't.

=IFERROR(INDEX(INDIRECT("Surveys!P6586:P7345"),SMALL(IF(INDIRECT("Surveys!P6586:P7345")<>"",ROW($E$4:$E$203)-ROW($E$4)+1,""),ROW($E$4:$E$203)-ROW($E$4)+1)),"")

It is meant to return the non-blank cells from the INDIRECT range in cells D3:D203. Both columns L & P have the same content, just in a different order so I'm at a loss at to why it'll work for one and not the other.

Any help greatly appreciated.

Jason

**Special-K** · 02-09-2015, 08:50 AM

UPDATE: Forget this, just saw your data is in a different order, this wont work.

What does this produce?

in any cell type
=SUMPRODUCT(--(L6586:L7345=P6586:P7345))

It should be 760
If its not then your data is NOT the same.

**jwillis07** · 02-10-2015, 02:59 PM

Bump.

Still not managed to resolve!

**daddylonglegs** · 02-10-2015, 03:24 PM

Hello Jason,

You say you are putting the first formula in D3:D203, where are you putting the second one? Your formula actually returns an array so unless you are "array entering" in a range (rather than array entry in one cell and copying down) then it could make a difference depending on where you enter the formula.

I recommend you change the end part to ROWS function - the formula will then return a single value and can be array entered in a single cell and copied down.

Ideally the ROWS function should match the first cell, so assuming you put the second formula in E3 first try this version:

=IFERROR(INDEX(INDIRECT("Surveys!P6586:P7345"),SMALL(IF(INDIRECT("Surveys!P6586:P7345")<>"",ROW($E$4:$E$203)-ROW($E$4)+1,""),ROWS(E$3:E3))),"")

confirm with CTRL+SHIFT+ENTER and copy down the column

**jwillis07** · 02-10-2015, 04:52 PM

Hi daddylonglegs, thanks for the response and apologies for the confusion. There is only one array formula in the range D3:D203, I'm just trying to tweek it to gather the contents of a different column.

When entering the formula I'm selecting the range D3:D203, then using the formula bar to enter the above, then CTRL + SHIFT + ENTER.

The purpose is to take the contents of one column in another sheet (Surveys!P6586:P7345), and show only the non-blank cells in the range D3:D203. In all honesty, I'm familar with INDEX but I'm not sure how the SMALL and ROW aspect of the formula works. At the time I put the question to this forum and somebody kindly supplied this solution.

I'll have a go at using your modification and let you know the outcome. Should I enter the revised formula in cell D3 and array enter, drag down?

Many thanks,

Jason

**daddylonglegs** · 02-10-2015, 05:06 PM

In that case I'm not really sure why your revised formula doesn't work.

What do you get with that formula, do you get blanks or errors or just not all the data you expected?

**jwillis07** · 02-11-2015, 07:02 AM

Yeah I thought as much! Can't understand why it won't work.

It returns just blanks in the range D3:D203 when the formula is set to column P. When it's column L, I get the non-blank cells only in order of appearance.

**XOR LX** · 02-11-2015, 08:22 AM

Hi.

Not sure I understand your formulas.

Apart from the issue pointed out by daddylonglegs re the construction you are using for the k parameter of the SMALL function, there is also the fact that:

Surveys!P6586:P7345

is a range comprising 760 rows.

whereas:

ROW($E$4:$E$203)-ROW($E$4)+1

generates an array comprising just 200 rows.

Hence any non-blank entries beyond P6785 will simply not be considered. In fact, every formula will return a blank since every formula will result in an error by virtue of your array being passed to SMALL being filled with #N/A errors as a result of this issue.

These two ranges need to be of the same dimension, e.g.:

=IFERROR(INDEX(INDIRECT("Surveys!P6586:P7345"),SMALL(IF(INDIRECT("Surveys!P6586:P7345")<>"",ROW(INDIRECT("Surveys!P6586:P7345"))-ROW(INDIRECT("Surveys!P6586"))+1,""),ROWS($1:1))),"")

though can I also ask why you are using INDIRECT in this expression, especially since you are simply hard-coding the sheet name in the formula, and not e.g. referencing it dynamically from some cell?

Regards

**jwillis07** · 02-11-2015, 11:34 AM

Hi XOR LX and thanks for your input.

I'm using INDIRECT as the workbook containing this formula is a template that then has another sheet (Surveys) moved in from a different workbook. I'm hard-coding rather than putting the source in a seperate cell just for tidiness.

Okay, that makes sense what you're saying about the two ranges needing to be the same dimension. It will have worked for Column L as all of the non-blank cells will invariably be toward the top, whereas column P has the same data scattered throughout.

As I said, I'm just not sure on how the SMALL function works to be able to interpret what the overall formula is trying to do.

When trying your revised formula, it's returning the first non-blank cell throughout the whole range of D3:D203. Progress though - Any idea what is causing that?

Many thanks,

Jason

**XOR LX** · 02-11-2015, 11:40 AM

I think it's about time you uploaded an actual workbook!

Regards

**jwillis07** · 02-11-2015, 11:46 AM

Would be happy to but the file size is 6.55mb, this forum, as I'm sure you're aware only allows 1mb files to be attached. I could email it if that's acceptable?

**XOR LX** · 02-11-2015, 12:17 PM

Actually it doesn't have to be the actual file. Are you not able to create a reduced example which at least exhibits the same issue?

Regards

**daddylonglegs** · 02-11-2015, 01:42 PM

Originally Posted by jwillis07

When trying your revised formula, it's returning the first non-blank cell throughout the whole range of D3:D203. Progress though - Any idea what is causing that?

I assume you are trying to array enter that single formula in the whole range. XOR LX's suggested formula would go in D3, array entered and copied down.

If you want to put it in as a single formula try this version:

=IFERROR(INDEX(INDIRECT("Surveys!P6586:P7345"),SMALL(IF(INDIRECT("Surveys!P6586:P7345")<>"",ROW(INDIRECT("Surveys!P6586:P7345"))-ROW(INDIRECT("Surveys!P6586"))+1,""),ROW(INDIRECT("Surveys!P6586:P7345"))-ROW(INDIRECT("Surveys!P6586"))+1)),"")

INDEX SMALL problem

LinkBack

Thread Tools

Rate This Thread

Display

INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Re: INDEX SMALL problem

Thread Information

Users Browsing this Thread

Similar Threads

Problem with INDEX/SMALL in formula

[SOLVED] Problem with Index, Small, Array to return multiple values for single look up value,

[SOLVED] Problem with function INDEX, SMALL AND IF

[SOLVED] problem with INDEX([...],SMALL(IF[...],ROW to list results based on more than one criteria

Problem with Index and Small functions to lookup and display multiple cells

Bookmarks

Bookmarks

Posting Permissions