Excel 2007 : IFS Function

**themarky** · 05-21-2010, 07:11 AM

A few years ago I created this formula in Excel 2003 using IF and OR.
It was used to get around the "only 7 ifs" thing (or whatever it was).

Anyway, it has served me well up to the 16th iteration. But I want to use it for 32.

I'm just totally unsure of what to do, but I'm led to believe that I can solve this problem with the IFS statement? Is that right?

Anyway, here is my formula:

=IF(OR(A!$D27=A!$F$7,A!I27=A!$F$7,A!D41=A!$F$7,A!I41=A!$F$7,A!D62=A!$F$7,A!I62=A!$F$7),IF(A!$D27=A!$F$7,A!$A$25,IF(A!I27=A!$F$7,A!$F$25,IF(A!D41=A!$F$7,A!$A$39,IF(A!I41=A!$F$7,A!$F$39,IF(A!D62=A!$F$7,A!$A$60,A!$F$60))))),IF(OR(A!D76=A!$F$7,A!I76=A!$F$7,A!D90=A!$F$7,A!I90=A!$F$7,A!D104=A!$F$7,A!I104=A!$F$7),IF(A!$D76=A!$F$7,A!$A$74,IF(A!$I76=A!$F$7,A!$F$74,IF(A!D90=A!$F$7,A!$A$88,IF(A!I90=A!$F$7,A!$F$88,IF(A!D104=A!$F$7,A!$A$102,A!$F$102))))),IF(OR(A!D124=A!$F$7,A!I124=A!$F$7,A!D138=A!$F$7,A!I138=A!$F$7),IF(A!D124=A!$F$7,A!$A$122,IF(A!$I124=A!$F$7,A!$F$122,IF(A!D138=A!$F$7,A!$A$136,IF(A!I138=A!$F$7,A!$F$136,"error")))))))

Yes, it took me ages!

What it does is:
It will output A!F7 if what is in sheet A at D27 is equal to A!F7, but if not then it will output A!D41 if it is equal to A!F7, but if not that then it will output A!I41 if it is equal to A!F7, but if not that then ...

Anyway, with this formula I can only get through 16 places I want to search. I now want 32.

Will the IFS statement be able to do this? Or do I just need to modify my formula?

Or is there a neater solution.

By the way, all of the dollar signs are there because I copy this formula to lots of different cells across the spreadsheet.

Any help would be greatly appreciated!

**Palmetto** · 05-21-2010, 11:15 AM

Or is there a neater solution.

Yes. Possibly, you could use a look up table and a look up formula, or perhaps even a SUMPRODUCT formula.

To get a precise answer, please upload a sample workbook meeting the following requirements:

1. It EXACTLY duplicates the structure of your real workbook, AND
2. It contains representative but non-sensitive dummy data, AND
3. It contains several examples of the desired results to aid in developing the logic to return correct results.

**themarky** · 05-26-2010, 07:06 AM

Thanks for the response.

I've attached a couple of simplified spreadsheets.

"SIMPLIFIED" is a blank example of the spreadsheet.

"EXAMPLE SIMPLIFIED" shows how it is used.

In the real spreadsheet I have sheets called C1 to C32.

I have only been able to get C1 to C16 working properly.

This is a spreadsheet that allows me to enter staff (teachers) absent.

I do this in cells B7 to B22 and D7 to D22 on sheet A.

I enter the replacement teacher names in cells F7 to F22 and G7 to G22.

The spreadsheet looks up the absent teacher's timetable for the given day and places this in the "class" and "room" columns related to their name in the gray cell.

I then assign their classes to replacement teachers.

Sheet A lets me enter staff absent (up to 32).

Sheets C1 to C32 refers to the replacement teacher's names in cells F7 to F22 and G7 to G22 on sheet A.

The cells in sheets C1, C2, C3 etc. search through Sheet A for the Replacement Teacher's name for each of C1, C2, C3 etc. in the "covered by" cells, and if it finds the replacement teachers name, it will put the class's details and rooms in the cells to the left.

e.g. On sheet C1 (for the first replacement teacher) , cell B7 looks through the first 16 teacher absences on Sheet A for each Period 1 class for a match and places the class and room in the appropriate cells for that replacement teacher.

My issue is that this is fine for 16 staff away, but it won't work for more than that.

Will the IFS statement help me do this?

Or is there a more elegant solution to the whole problem?

I've been told I probably should have used MS Access, but I think I'm now to committed to Excel.

Any suggestions?

This is a simplified version. If you like, I can upload a fuller version.