Formula correct

[theta]

Hi, this formula works. But i'm sure there is a simpler solution the find the next non blank row! Any non array methods?

Basically I will be creating a lookup, but instead of the user typing the row number, they just use sequential numbers (1 - 100) and excel translate this index to the next blank row

=SMALL(IF(A1:A17<>"",A1:A17*ROW(A1:A17),""),5)

Cells A1 : A17 in this instance would contain

1
1
1
1
""
""
1
1
""
""
1
1
1
1
""
""
1
1
1

 

[Aladin Akyurek]

What is the expected outcome?

 

[jbeaucaire]

Your example set of data has all 1s in it, making it hard to spot the value you're trying to find. An array formula seems like the correct approach.

Care to explain what you're trying to do without referring to formulas?

 

[theta]

Use a sequential index (1,2,3,4,5,6,7,8,9,10) to be translated as the 'k' giving the ROW() of the next non blank cell. I have cut the formula down to this :

{=SMALL(IF(A1:A17<>"",ROW(A1:A17),""),C2)}

C2 - this cell contains values 1 - 10

I have a large data set of data, with a criteria column. So need to find the next row where the criteria is met (i.e. criteria cell = 1)

 

[theta]

Hmmm... basically if I had data in A10 : D50...I would like to be able to scroll through 1-10 on an INDEX dropdown, and it will give me the row number of the next row that meets the criteria. I think I am on the wrong approach as this one is not very flexible.

Example.

A10 : A50 <> ""
B10 : B50 = "Hello"
C10 : C50 = "Wednesday"
D10 : D50 > "0"

Would not like to be able to select 1 - 10 on the dropdown (which would be used as the 'k' for a SMALL formula) then the corresponding ROW() would be returned for the next item meeting the criteria?

Any help appreciated

 

[jbeaucaire]

Your array function seems perfect for this, exactly what it was designed for. What's wrong with it?

Excel Workbook
	A	B	C	D	E
1	Cat		select	criteria	row
2	Dog		3	dog	8
3	Bird
4	Cat
5	Dog
6	Bird
7	Cat
8	Dog
9	Bird
10	Cat
11	Dog
12	Bird
13	Cat
14	Dog
15	Bird
16	Cat
17	Dog
18	Bird
19	Cat
20	Dog
21	Bird
Sheet1