Return 2nd non-blank value in a list (with example)

[ANDIF]

You all are awesome. I have one more question to solve so I can finish this project.

I came across this page: http://www.exceltip.com/st/Find_the...ank_cell_in_range_in_Microsoft_Excel/606.html

which shows how to return the value of the first non-blank cell in an array. That's great, but I now need to find the next value, and so on. In the example, I'd need to get "BBB" to come out of the formula.

My list has other blanks inserted throughout the array, so it would more closely resemble:
A1
AAA

BBB

CCC

Any ideas?

 

[Oaktree]

Try:

=INDEX(A1:A10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)-ROW($A$1)+1),2))

which is an array formula and must be confirmed with CTRL+SHIFT+ENER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)

Changing the ,2 on the end to ,1 or ,3 will return the first or third non empty item in the range respectively.

 

[ANDIF]

Try:

=INDEX(A1:A10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)-ROW($A$1)+1),2))

which is an array formula and must be confirmed with CTRL+SHIFT+ENER (doing so correctly will result in Excel putting { }'s around your formula in the formula bar)

Changing the ,2 on the end to ,1 or ,3 will return the first or third non empty item in the range respectively.

Looks like this will do it, as soon as I solve the merged cells problem. Thanks for your help!

 

[ANDIF]

Is there any way to plug this equation into an IF statement?

Here's my problem: I'm looking up an array that can have a maximum of 4 values, so I use your equation with 1 through 4 as the last value. The array, however, can have less than 4 (or zero) values. When this is the case, the formula that doesn't have a value returns a #NUM! error.

I tried surrounding it with an IF statement that read "=IF(your equation=ISERROR,"",your equation)" but I get a #VALUE! error. Entering with CTRL SHIFT ENTER yields a #NAME? error.

 

[Aladin Akyurek]

Is there any way to plug this equation into an IF statement?

Here's my problem: I'm looking up an array that can have a maximum of 4 values, so I use your equation with 1 through 4 as the last value. The array, however, can have less than 4 (or zero) values. When this is the case, the formula that doesn't have a value returns a #NUM! error.

I tried surrounding it with an IF statement that read "=IF(your equation=ISERROR,"",your equation)" but I get a #VALUE! error. Entering with CTRL SHIFT ENTER yields a #NAME? error.

Control+shift+enter...

=IF(COUNTIF($A$1:$A$10,"?*")>=2,INDEX(A1:A10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)-ROW($A$1)+1),2)),"")

 

[ANDIF]

Control+shift+enter...

=IF(COUNTIF($A$1:$A$10,"?*")>=2,INDEX(A1:A10,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)-ROW($A$1)+1),2)),"")

I appreciate the effort on this one - I learn something new every time I post here (what is "?*"?).

This isn't quite getting it done. If my array is empty, this works fine, but if I have:
A1
AAA
(blank)
BBB
(blank)
(blank)
(blank)
-end of array-

The last two formulas are still coming up with a #NUM! error

 

[ANDIF]

Must be frustrating to be so good at this stuff and have people like me screw up the little stuff.

I finally discovered the logic. Thanks for the help!

 

[ANDIF]

I lied - there is one more step after all. These equations have worked wonderfully for the majority of this project.

The last step: I'd like to be able to return the value in the left adjacent cell. So if I have

A, B
X, AAA


Y, BBB

The formula would look for AAA, then return X. The next formula would skip over AAA, find BBB, and return Y.

 

[ANDIF]

I can get the value with a formula such as this:

=INDEX(A1:B5,MATCH(theTextIWant,B1:B5,0),1)

But this obviously can't accommodate the "find next" functionality of the previous formulas. This stuff is just too over my head for me to figure out on my own.

 

[ANDIF]

I can get the value with a formula such as this:

=INDEX(A1:B5,MATCH(theTextIWant,B1:B5,0),1)

But this obviously can't accommodate the "find next" functionality of the previous formulas. This stuff is just too over my head for me to figure out on my own.

Well a little trial and error and I have it. I hope y'all can benefit from this.

My table looks like this (A1:B5):
X,AAA
,
,
,
Y,BBB

The formula for finding first, second and third values in column B and returning corresponding A values is:

=INDEX(A1:B5,MATCH(INDEX(B1:B5,(SMALL(IF($B$1:$B$5<>"",ROW($B$1:$B$5)-ROW($B$1)+1),1))),B1:B5,0),1)

That one returns the first value, it would look like this for the second:

=INDEX(A1:B5,MATCH(INDEX(B1:B5,(SMALL(IF($B$1:$B$5<>"",ROW($B$1:$B$5)-ROW($B$1)+1),2))),B1:B5,0),1)

Cheers!