IF cell is BLANK

[anshupillay]

Hi All,

I need help with updating below formula. I need another criteria added to with "EMPLOYEE", I need it to look at another column (B)and return value only if its BLANK.


=IFERROR(INDEX(Data_Entry!$B$3:$B$1000,SMALL(IF(Data_Entry!$A$3:$A$1000="EMPLOYEE",ROW(Data_Entry!$A$3:$A$1000)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

File attached. First tab is how data is received, I need values to return in other tabs ie TEMPLATE_employee based on Classification and Sysytem id.

Thanks!!!

[anshupillay]

attachment

[kersplash]

Withdrawn comment

[BoredWorker]

please have a more clear description.

example and desired result (by manual plug in)

[anshupillay]

please have a more clear description.

example and desired result (by manual plug in)

I need values returned on tab 2 based on classification “Employee”&System ID Blank.

[Phuocam]

Try this in A3:

=IFERROR(INDEX(Data_Entry!C:C,SMALL(IF((Data_Entry!$A$2:$A$1000="EMPLOYEE")*(Data_Entry!$B$2:$B$1000=""),ROW(Data_Entry!$A$2:$A$1000),""),ROWS($A$3:A3))),"")

Array formula, enter with Ctrl+Shift+Enter.

[anshupillay]

Try this in A3:

=IFERROR(INDEX(Data_Entry!C:C,SMALL(IF((Data_Entry!$A$2:$A$1000="EMPLOYEE")*(Data_Entry!$B$2:$B$1000=""),ROW(Data_Entry!$A$2:$A$1000),""),ROWS($A$3:A3))),"")

Array formula, enter with Ctrl+Shift+Enter.

HI again,
Formula is working well however its returning 0 for blank fields. I need output to be blank if Data_Entry is blank. Is there a quick fix for this?? Thanks

[FDibbins]

Without going into too much detail on your data, is there a column that you could test to see if the data exists, and if not, just return ""?

[anshupillay]

Without going into too much detail on your data, is there a column that you could test to see if the data exists, and if not, just return ""?

YES, column E (ABN) should return blank for first Employee

[FDibbins]

Maybe add a countifs() based on the same criteria?
=if(countifs(...)=0,"",formula)

[anshupillay]

I cant get it to work

[Phuocam]

I cant get it to work

=IFERROR(INDEX(Data_Entry!C:C,SMALL(IF((Data_Entry!$A$2:$A$1000="EMPLOYEE")*(Data_Entry!$B$2:$B$1000=""),ROW(Data_Entry!$A$2:$A$1000),""),ROWS($A$3:A3))),"")&""