INDEX SMALL problem

**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

**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)),"")