Referencing multiple ranges to return a value

I have a worksheet with about 15 different sheets and I would like to have one reference sheet that I can see if someone appears in differing ranges from the other sheets. How do i write a formula that will return a value if of "1" if "Chris C" appears in the range FSplash, "2" if "Chris C" appears in FSSolo, "3" if "Chris C" Appears in Fduo, and finally ")" if he doesn't appear at all. I have names set up for my various ranges but the ranges sometimes appear on the same sheets, but most are on differing sheets. I have tried many variations of IF formulas, but I can't figure how to allow several references. I tried VLOOKUP, but the names change and are not in order. I tried MATCH, but again can't get it to reference differing ranges. Thanks for your help.

Mike

Excellent thank you. I think I was missing some (and maybe a "