How do I combine these two formulas??

[Scott82]

Hi there, both of these formulas work on their own but how can I combine both? I have apx 7 of these to combine in total!

=IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Giles House\2010 - 2011\[Giles House Term 6 2010-2011.xls]Sheet1'!$B$3:$B$13,0)),"","Giles")

=IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Millington House\2010 - 2011\[Millington House Term 6 2010-2011.xls]Sheet1'!$B$3:$B$13,0)),"","Millington")

Many thanks Scott

 

[Andrew Poulsom]

Like this?

=IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Giles House\2010 - 2011\[Giles House Term 6 2010-2011.xls]Sheet1'!$B$3:$B$13,0)),IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Millington House\2010 - 2011\[Millington House Term 6 2010-2011.xls]Sheet1'!$B$3:$B$13,0)),"","Millington")
,"Giles")

 

[CharlesChuckieCharles]

=TRIM(IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Giles House\2010 - 2011\[Giles House Term 6 2010-2011.xls]Sheet1'!$B$3:$B$13,0)),"","Giles")
& " " & IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Millington House\2010 - 2011\[Millington House Term 6 2010-2011.xls]Sheet1'!$B$3:$B$13,0)),"","Millington"))


This allows both to be true

 

[Ruddles]

Can't wait to see the x7 version of this!

Have you considered using a lookup table in some way?

 

[Scott82]

Thanks all!
I'll repost the 7x version for you! (If it works!)

This has given me such a headache, I'm so pleased to have a result!

Thank you, thank you, thank you!

 

[Scott82]

Well this is it but I dont think I haver enough (((())))'s??? Where should they go?

=TRIM(IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Giles House\2010 - 2011\[Giles House Term 6 2010-2011.xls]Sheet1'!B3:B13,0)),"","Giles"))&""&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Millington House\2010 - 2011\[Millington House Term 6 2010-2011.xls]Sheet1'!B3:B12,0)),"","Millington"))&""& IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Shirley House\2010 - 2011\[Shirley House Term 6 2010-2011.xls]Sheet1'!B3:B11,0)),"","Shirley"))&""&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Palmer House\2010-2011\[Palmer Term 6 2010-2011.xls]Sheet1'!B3:B12,0)),"","Palmer"))&""& IF(ISNA(MATCH("Julie",S:\School\SMT\Rotas\Fleming House\2010 - 2011\[Fleming House Term 6 2010-2011.xls]Sheet1'!B3:B12,0)),"","Fleming"))&""&IF(ISNA(MATCH("Julie",S:\School\SMT\Rotas\Todhunter House\2010-2011\[Todhunter House Term 6 2010-2011.xls]Sheet1'!B3:B14,0)),"","Todhunter"))&""&IF(ISNA(MATCH("Julie",S:\School\SMT\Rotas\Charlotte House\2010-2011\[Term 6.xls]Sheet1'!B3:B13,0)),"","Charlotte"))&""&IF(ISNA(MATCH("Julie",S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!B3:B13,0)),"","School"))

Thank you to all who have helped!

 

[jasonb75]

You have too many, but is your logic correct?

Should there only be one result for each, or multiple results?

 

[GlennUK]

Duplicate thread, with solution:

http://www.mrexcel.com/forum/showthread.php?t=556696

 

[Jon von der Heyden]

Hi Scott

You seem to be posting the same questions alot here. Per the forum rules please use a single thread for your question. You shouldn't start new threads and if you do we will typically lock or delete your dupes. I'm locking the other posts. Please stick with just this one now.

 

[Scott82]

Sorry! I had seen that you should post one question per thread, I had reposted the new question from the previous thread but I will be more careful! Sorry again Scott