 # Index Small Referencing

• Firstly, apologies if the thread title is wrong but I'm not sure of the best way to describe my question.

I have a worksheet which uses the formula below to extract data from the 'All authority data' worksheet and place it in columns in sheet1

Code
``=INDEX(LAname,SMALL(IF(LSCarea=\$D\$6,ROW(LAname)-ROW('All authority data'!\$B\$4)+1),[B]1[/B]))``

Column D in sheet1 contains data on a organisation and D6 contains that organisation's geographical region (which can be selected by a user). What I'm doing is displaying the same information for other organisations in the same region alongside it. The formula finds each data row in the All authority sheet for that region and displays the value in the first column (B) in sheet1. The formula is in five cells as there are multiple matches, with the bold number different in each (1,2,3,4,5).

I have one question. Are there non-VBA solutions?

1. The formula obviously picks up the original organisation specified in column D so it appears twice: is there a way of avoiding this?

Hope my explanation hasn't completely baffled everyone.

Stephen

• Re: Index Small Referencing

I don't do array formulas, so I don't quite understand what that formula in cells F2 through J2 is doing. What is the SMALL doing - bringing something out in order?

I think two changes need to be made to the array formulas to solve your two questions:

1. If the formula has no output, give a blank output. Then you can make the formulas go as far in row 2 as you need, and you will only have the right number displayed.

2. Include a test to not be a match in cell D2 and the column B in the All authority sheet.

I don't know how to do this, but someone will help you.

• Re: Index Small Referencing

Thanks ByTheCringe2, you've given me an idea for 1. I can use conditional formatting to have white font if there's an error in the formula.

The Small function takes the nth-smallest value in a set so once the matching rows are found, the first formula takes the 1st smallest (",1" at the end), the next one takes the 2nd smallest and so on.

• Re: Index Small Referencing

Quote from StephenR

2. Is there way to automatically extend the number of columns. One region might have 4 orgs whilst another might have 6. Can the formula be automatically 'hidden'?
Stephen

I'm not sure this will work for you.

Input formula in cell D3 and copied down.

=IF((VLOOKUP(\$D\$2,AllData,ROW(A2),FALSE))=0," ",VLOOKUP(\$D\$2,AllData,ROW(A2),FALSE))

[vlookup]*[/vlookup]

• Re: Index Small Referencing

Quote from StephenR

Thanks ByTheCringe2, you've given me an idea for 1. I can use conditional formatting to have white font if there's an error in the formula.

The Small function takes the nth-smallest value in a set so once the matching rows are found, the first formula takes the 1st smallest (",1" at the end), the next one takes the 2nd smallest and so on.

But what is it doing in your workbook - I couldn't see what data was being ordered.

• Re: Index Small Referencing

Hi,

In J1,

=COUNTIF(LSCarea,D6)-1

In F2 and copied across,

=IF(COLUMNS(\$F\$1:F\$1)<=\$J\$1,INDEX(LAname,SMALL(IF(LSCarea=\$D\$6,IF(LAname<>\$D\$2,ROW(LAname)-ROW('All authority data'!\$B\$4)+1)),COLUMNS(\$F\$1:F\$1))),"")

[ea]*[/ea]

HTH

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!