# Add IF IS BLANK argument to existing formula

1. ## Add IF IS BLANK argument to existing formula

Hello,

I would like to add an "IF IS BLANK" argument to this existing formula: =IF(MAINDATA!A2<>"",MAINDATA!A2,"")

The attached sample spreadsheet contains data in the MAINDATA worksheet bringing over certain columns to the VENDOR_REPORT worksheet.

Now we decided that we only want rows where the RELEASED column (COLUMN Q in the MAINDATA worksheet) are blank.

We don't want to use VBA to do this. Can I simply add an IF IS BLANK argument to the existing formula, and if so, what would that look like?  Register To Reply

2. ## Re: Add IF IS BLANK argument to existing formula

This is the basis of the formula

in VENDOR REPORT A2
=IFERROR(INDEX(MAINDATA!\$A\$2:\$T\$1000,AGGREGATE(15,6,ROW(\$A\$2:\$A\$1000)/((ISNUMBER(MAINDATA!\$Q\$2:\$Q\$1000)=FALSE)),ROWS(A\$2:A2))-(2-1),1),"")
in B2
=IFERROR(INDEX(MAINDATA!\$A\$2:\$T\$1000,AGGREGATE(15,6,ROW(\$A\$2:\$A\$1000)/((ISNUMBER(MAINDATA!\$Q\$2:\$Q\$1000)=FALSE)),ROWS(A\$2:A2))-(2-1),2),"")
in C2
=IFERROR(INDEX(MAINDATA!\$A\$2:\$T\$1000,AGGREGATE(15,6,ROW(\$A\$2:\$A\$1000)/((ISNUMBER(MAINDATA!\$Q\$2:\$Q\$1000)=FALSE)),ROWS(A\$2:A2))-(2-1),4),"")
in D2
=IFERROR(INDEX(MAINDATA!\$A\$2:\$T\$1000,AGGREGATE(15,6,ROW(\$A\$2:\$A\$1000)/((ISNUMBER(MAINDATA!\$Q\$2:\$Q\$1000)=FALSE)),ROWS(A\$2:A2))-(2-1),10),"")
in E2
=IFERROR(INDEX(MAINDATA!\$A\$2:\$T\$1000,AGGREGATE(15,6,ROW(\$A\$2:\$A\$1000)/((ISNUMBER(MAINDATA!\$Q\$2:\$Q\$1000)=FALSE)),ROWS(A\$2:A2))-(2-1),14),"")
in F2
=IFERROR(INDEX(MAINDATA!\$A\$2:\$T\$1000,AGGREGATE(15,6,ROW(\$A\$2:\$A\$1000)/((ISNUMBER(MAINDATA!\$Q\$2:\$Q\$1000)=FALSE)),ROWS(A\$2:A2))-(2-1),7),"")

Column G is now redundant since it will always contain spaces

NOTE: I'm not sure why zeroes are appearing, this is a standard formula I use with amended parameters to reflect your data. I've never had this problem before but the only condition set ifor column Q to be blank which it looks like they are.  Register To Reply

3. ## Re: Add IF IS BLANK argument to existing formula

That is cool, but my head hurts trying to figure out what it's doing!

Tim  Register To Reply

4. ## Re: Add IF IS BLANK argument to existing formula

AGGREGATE 15 is equivalent to SMALL

I used to have a detailes explanation of how this works but cant find it.

If you search for remove blanks from list or INDEX SMALL it should turn up lots of versions of this formula - some with explanation.
I'm afraid I'm at work so haven't got time to explain it fully.  Register To Reply

5. ## Re: Add IF IS BLANK argument to existing formula

A2
=IFERROR(INDEX(MAINDATA!\$A:\$A,AGGREGATE(15,6,ROW(MAINDATA!\$Q\$2:\$Q\$99)/(MAINDATA!\$Q\$2:\$Q\$99=0)/(MAINDATA!\$A\$2:\$A\$99>0),ROWS(A\$2:A2))),"")

B2:F2
=IF(\$A2="","",VLOOKUP(\$A2,MAINDATA!\$A\$2:\$T\$39,MATCH(B\$1,MAINDATA!\$A\$1:\$T\$1,),))

E2
=IF(\$A2="","",IFERROR(1/(1/VLOOKUP(\$A2,MAINDATA!\$A\$2:\$T\$39,MATCH(E\$1,MAINDATA!\$A\$1:\$T\$1,),)),""))

or if you have update Office 365, Dynamic array should available
A2
=FILTER(MAINDATA!\$A\$2:\$A\$99,(MAINDATA!\$Q\$2:\$Q\$99="")*MAINDATA!\$A\$2:\$A\$99)  Register To Reply

6. ## Re: Add IF IS BLANK argument to existing formula

Thank you Special-K and Bo-Ry as your formulas worked perfectly. I did try to use the Office 365 option (see below), but am not sure why it doesn't work. I have used the 365 versions of formulas before. That said, the other formulas you both provided work. Thank you so much!

=FILTER(MAINDATA!\$A\$2:\$A\$99,(MAINDATA!\$Q\$2:\$Q\$99="")*MAINDATA!\$A\$2:\$A\$99)  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 