IFERROR and IF together in same formula

**Christopher135** · 12-01-2013, 09:13 PM

Hi Guys,

I have a formula that reads through our roster and pulls out the managerial staff roster (basically saves me from manually entering this data). it looks like this:

Please Login or Register  to view this content.

My problem is that we have about 20 different rosters a manager could be working, with 120+ different 'lines' (specific week). and for the purposes of the database I'm creating, I don't really care which specific line or roster they're working, I just want to know they're in a management role for that week. So the roster code looks like ABCSE1, or ABCMN4 etc etc. I want to add an IF formula to the above formula, that will compare the result (ABCSE1) to the full list of manager roster codes, and if true, instead display 'Manager' in the cell.

They will often not be working a managers role, then their roster code might look like ABCDR1, or ABCBE3. In this situation I want the IF formula to compare their code to the full list of manager roster codes, and if false, cell to either be blank, or display 'free'

any and all help appreciated!

Thanks guys!

**daddylonglegs** · 12-01-2013, 09:35 PM

Assuming you have the Manager roster codes in a list named MRC then try this version

=IFERROR(IF(COUNTIF(MRC,VLOOKUP($C133,'[ManagementRoster.xlsm]ManagementRoster'!$E$23:$FL$800,COLUMNS($E:CY),FALSE)),"Manager","Free"),"")

**Christopher135** · 12-01-2013, 09:45 PM

Thank you so much! instantly fixed! wonderful!

The only thing I forgot was their leave! There are a few codes that stand for leave (AL, AT and similar) that I still want shown as AL or AT. Is there anyway to tell this formula to ignore those examples and leave them unchanged?