IFS function issue...

Sleeplol

New Member
Joined
Apr 10, 2019
Messages
28
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,794
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

New Member
Joined
Apr 10, 2019
Messages
28
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,794
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

New Member
Joined
Apr 10, 2019
Messages
28
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

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
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.
 

Forum statistics

Threads
1,078,134
Messages
5,338,428
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top