Return data if blank cell is found

**Dartmouth_jock** · 03-09-2013, 01:24 PM

I have a spreadsheet with several columns of data. I have a formula that returns a cell from column "a" if column "d" contains a "Y". It returns the next cell in the column where that criteria is found. I want to modify the formula so that it does the same thing if the cell in column d is blank.

This is the formula I use to get the data from column a =IF(ROWS(P$3:P3)<=$P$2,INDEX(A$2:A$133,SMALL(IF($D$2:$D$133=$O$2,ROW($D$2:$D$133)-ROW($A$2)+1),ROWS(P$3:P3))),""). I have tried to modify the formula so that the small looks at a 02 and returns the data if it is a blank. I have tried to insert a match formula that returns the row reference for a blank cell. I am stuck!

**dredwolf** · 03-09-2013, 01:33 PM

This is just a guess, but if you used the Evaluate formula tool on that, I am thinking $O$2 is evaluating to '0' not "", so it is actually trying to match cells with a zero in them..
If you replace the $D$2:$D$133=$O$2 with $D$2:$D$133="", does it work ?

Edit-
If it does, then a way to force O2 to ="" is just that, In O2: =""

Hope this helps

**Dartmouth_jock** · 03-09-2013, 02:56 PM

Thanks, it worked.

**dredwolf** · 03-09-2013, 03:03 PM

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"