Arrays

[grantm15]

I have a 2 columns of data. Sometimes, the second column has some blank cells due to a condition that I set. I want to list in another part of the spreadsheet, the values in the left hand column when that second column value is blank. In other words, if this was my data

january cold
february
March warm
April
May hot

I would want the formula to search this array and return February and April (the list of Column A values that has no value in column B) because the second column was blank.

Can somebody help!

 

[NBVC]

Assuming your data is in Columns A and B, respectively,

Try this in column C

=IF(ROW($B2)-ROW($B$1)>COUNTIF($B$1:$B$100,""),"",INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100="",ROW($A$1:$A$100)),ROW($B2)-ROW($B$1))))


Adjust range to suit.
Must be confirmed with CTRL+SHIFT+ENTER instead of just ENTER.
Then copy down as far as your list is long.

 

[watkins6878]

You could use something like this

=if(B2=0,A2,IFB2>0,""))

 

[Aladin Akyurek]

I have a 2 columns of data. Sometimes, the second column has some blank cells due to a condition that I set. I want to list in another part of the spreadsheet, the values in the left hand column when that second column value is blank. In other words, if this was my data

january cold
february
March warm
April
May hot

I would want the formula to search this array and return February and April (the list of Column A values that has no value in column B) because the second column was blank.

Can somebody help!

Book2
	A	B	C	D	E	F	G
1			0			2
2	Field-1	Field-2	Idx			List
3	january	cold				february
4	february		1			April
5	March	warm
6	April		2
7	May	hot
8
Sheet1

C1 must house a 0.

C3, copied down:

=IF((A3<>"")*(B3=""),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,"")

F1:

=LOOKUP(9.99999999999999E+307,C1:C7)

F3:

=IF(ROW()-ROW(F$3)+1<=$F$1,LOOKUP(ROW()-ROW(F$3)+1,$C$3:$C$7,$A$3:$A$7),"")

 

[grantm15]

Got it!

NBVC,

That was perfect!

Thanks!