IFS function issue...

[Sleeplol]

Hello everyone,

I'm assuming this is the ideal function, but I could be way off.

Issue on "data" page: Need name of employee (column E) to register a "Division" (e.g. Administrative, Maintenance, Shipping, Production) in another Cell (column W)

On "Employees" page I have a the four "division" columns with all of the corresponding employees listed under each.

If the name on the "data" page is recognized on that list, then the specific division shows in column W.

Each division title is cell 1 of that column on the "employees" page.

Here's my current formula:

=IFS([@[Observer name]]=Employees!A:A,Employees!$A$1,[@[Observer name]]=Employees!B:B,Employees!$B$1,[@[Observer name]]=Employees!C:C,Employees!$C$1,[@[Observer name]]=Employees!D:D,Employees!$D$1)

Thanks for any correction

 

[Eric W]

Here's an alternative:

=INDEX(Employees!$A$1:$D$1,SMALL(IF(COUNTIF(OFFSET(Employees!$A:$A,0,{0,1,2,3}),[@[Observer Name]]),{1,2,3,4}),1))

 

[Sleeplol]

Eric,
Thank you!
I was just now able to test this and it works exactly like i had asked.

However, if the "observer name" cell is blank or if there is a name not recognized, is there a way to return a blank?

 

[jasonb75]

Perhaps

=IFERROR(LOOKUP(2,1/COUNTIF(OFFSET(Employees!$A:$A,0,{0,1,2,3}),[@[Observer Name]]),Employees!$A$1:$D$1),"")

Eric's formula looks like it should return an error in the cells that should be blank, so using IFERROR with that should work as well.

 

[Eric W]

Jason is correct, this should work:

=IFERROR(INDEX(Employees!$A$1:$D$1,SMALL(IF(COUNTIF(OFFSET(Employees!$A:$A,0,{0,1,2,3}),[@[Observer Name]]),{1,2,3,4}),1)),"")

although he came up with a slightly shorter version.

 

[Sleeplol]

Jason and Eric,
Thank you, both formulas work.
I ended up shifting the lists on the "Employee" page from A:D to B:E and making Column A "None". Reason is, whenever a "blank" cell was entered into the "observer name" it would return "Administration", or in Jason's formula "Production". But yet if I "delete or backspace that same "blank" field it returns a blank cell (ideal). Anyway, wasn't sure how to delete only "blank" cells, so for now the quick fix is returning "None".

Thank again guys.

 

[jasonb75]

It sounds as if that blank cell isn't truly blank, but contains a space character of some kind. A fairly common occurence with data copied from browsers.