Help to simplify formula - too many arguments

[rck3]

Ok you guys helped me once before and I need it again. I've been working on a overly complex sheet which should really have just been done as a true database but I was not given that option. I was able to figure out the formula to pull data from another sheet based on obtain selected on a master sheet. Now I was asked to just expand a few more choices which is making me run into the too many arguments issue.

The formula was to have 2 choices in cell C5, now it needs to be 6 choices. The formula for the 2 is below and works fine. Adding even 1 more IF condition causes too many arguments. I have been trying to think of another way to tackle this.

=IF((C5="Emergency Evacuation: Cat 3 HES Estimate"),INDEX('Shelter Demand Analysis'!$A$4:$G$91,MATCH(C3,'Shelter Demand Analysis'!$A$4:$A$91,0),6),IF((C5="Emergency Evacuation: Cat 4 HES Estimate"),INDEX('Shelter Demand Analysis'!$A$4:$G$91, MATCH(C3,'Shelter Demand Analysis'!$A$4:$A$91,0), 5),INDEX('Shelter Demand Analysis'!$A$4:$G$91, MATCH(C3,'Shelter Demand Analysis'!$A$4:$A$91,0), 7)))

I basically have a master sheet that allows users to select C5(event #) and C3 (name). Based on those 2 choices, data is pulled from a set data sheet (header below). The data table does not contain c5 in an easy to match column so I am creating an if statement for each event type. 2 is fine but more is not. I do not want to simply add Event as part of the name since this will not allow for easy reading and make the list VERY long. HELP please, I guess I could create a hidden data page that reformats everything and use it for the calcs and have the data sheet pull from it but that is alot of work plus for someone else to maintain it is not a good alternative.

<TABLE style="WIDTH: 423pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=558><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" span=9 width=62><TBODY><TR style="HEIGHT: 36.75pt; mso-height-source: userset" height=49><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; HEIGHT: 36.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=49 width=62></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #953735; WIDTH: 188pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=248 colSpan=4>Event 1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #953735; WIDTH: 188pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=248 colSpan=4>Event 2</TD></TR><TR style="HEIGHT: 33pt; mso-height-source: userset" height=44><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #002060; WIDTH: 47pt; HEIGHT: 33pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 height=44 width=62>Name</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=62>Start</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=62>Stop</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=62>Number</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=62>Total</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=62>Start</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=62>Stop</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl67 width=62>Number</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=62>Total</TD></TR></TBODY></TABLE>

 

[rck3]

really what I would like to do is have the datasheet as below so a user could select and event type, a name, and a condition but I ended up combining name + condition for a unique name. I ran into the too many arguement issue before and this was a quick fix. I don't really think the 3 variable options could fit. (Wish people would listen to me at the start of a project that we needed to go beyond excel but me creating an excel sheet is alot cheaper than me developing a database for our programs to code)

<TABLE style="WIDTH: 482pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=636><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" span=8 width=62><TBODY><TR style="HEIGHT: 36.75pt; mso-height-source: userset" height=49><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; HEIGHT: 36.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=49 width=62></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=78></TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #953735; WIDTH: 188pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=248 colSpan=4>Event 1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #953735; WIDTH: 188pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=248 colSpan=4>Event 2</TD></TR><TR style="HEIGHT: 33pt; mso-height-source: userset" height=44><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #002060; WIDTH: 47pt; HEIGHT: 33pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl68 height=44 width=62>Name</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 59pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl68 width=78>Condition</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 width=62>Start</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 width=62>Stop</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 width=62>Number</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl68 width=62>Total</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 width=62>Start</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 width=62>Stop</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 width=62>Number</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 47pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl68 width=62>Total</TD></TR></TBODY></TABLE>