Search code examples
excelexcel-formulaexcel-2007

Excel skip blank or 0-valued cells

I would like to have a formula that scans through one column of data, and should this column be non-zero, it registers the contents of a cell in another column, but the same row. I would also like it to trim out the blank cells. Is this possible in Excel 2007?

for example: I have, 

column A    
A
B
C
G
H

column B
1
2
blank
blank
8

If the criteria is a non-zero column B, we have the following output:

column N

A
B
H
Solution

  • The usual way to do something like this would be to use INDEX and SMALL in an array entered formula:

    =IFERROR(INDEX(A:A,SMALL(IF(B:B<>0,ROW(B:B)),ROWS($B$1:B1))),"")

    Put that in the first cell in column N and after typing the formula, don't press Enter. Instead, press Ctrl+Shift+Enter

    And then you can drag it down.