Work Around to If Statement Max of 8

[Northrun]

I set up, with the camera tool, in cell (C6 in Sheet1) an image that changes based on the contents of another cell (S2 in a sheet called Scoring). I Name-Defined cells B1, C1, D1, E1 in sheet Helmet to be the images that fill into C6 on Sheet1. The Name-Define was given the title Helmet and “Refers To” the following formula:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
=IF(Scoring!$S$2="Colts",Helmet!$B$1,IF(Scoring!$S$2="Jaguars",Helmet!$C$1,IF(Scoring!$S$2="Titans",Helmet!$D$1, IF(Scoring!$S$2="Texans",Helmet!$E$1,""))))
<o> </o>
Then the image in C6 was changed to have a formula that reads =Helmet
<o> </o>
In other words, when I type the words Colts or Jaguars or Titans or Texans into S2 the image in C6 changes.
<o> </o>
Excel has a max of 8 IF statements and I need 32 if statements, is there any way to achieve the same results using a different type of formula ( INDEX, MATCH?) I am not too familiar with these other types of formulas, so an example would help.
<o> </o>
I was thinking along the lines of; IF any cells on sheet Reduce in the Range A1 to A4 match the value of the images in Helmet B1, C1, D1, E1 then the image in C6 is changed, but I just don’t know how to do it : (
<o> </o>
Thanks!

 

[Jonmo1]

Build a lookup table

You'll have to move whatever is in Helmet!B1:E1 Down 1 row to 2, then put the team names in Row 1.

Then use HLOOKUP..

Excel Workbook
	B	C	D	E	F	G	H
1	Colts	Jaguars	Titans	Texans			Titans
2	A	B	C	D			C
Sheet1

 

[Northrun]

Thanks, let me give this a shot

 

[Northrun]

I don't think it is working because I need a way to link the team name (Colts) to the cell that contains the image or the actual image itself (Colts Helmet)

 

[Jonmo1]

You have to actually MOVE whatever is in
Helmet!$C$1
to
Helmet!$C$2

And
Helmet!$D$1
to
Helmet!$D$2

etc..

Then in C1 put Colts, D1 put Jaguars etc..

 

[Jonmo1]

If MOVING the contents is not an option, then
put the team names in Row 2 (or ANY available row)

and use
=INDEX(Helmet!$C$1:$E$1,MATCH(Scoring!$S$2,Helmet!$C$2:$E$2,0))

where
Helmet!$C$2:$E$2
is whatever available row you chose to put the team names in.

 

[Northrun]

OK I will try the 2nd option it think that is more specific to my project

Thanks!

 

[BobUmlas]

You can do it this way which is effectively limitless and isn't nested:
=IF(Scoring!$S$2="Colts",Helmet!$B$1,"")&IF(Scoring!$S$2="Jaguars",Helmet!$C$1,"")&IF(Scoring!$S$2="Titans",Helmet!$D$1,"")&IF(Scoring!$S$2="Texans",Helmet!$E$1,"")
etc.

Bob Umlas
Excel MVP
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Northrun]

Bob, That equation seems to make sense, and I have it in the spreadsheet; however, it is not working. I don't really understand why, becasue it doesn't seem too different from the first IF statement I posted it is just limitless.

The cells still using the original IF formula change but the cell I am testing with the new formula doesn't

 

[BobUmlas]

I'm guessing the text is not an exact match. For example, if you have "Colts " in Scoring!S2 it won't match because of the trailing space. Check the contents of S2 and see why it's not working. You can follow the evaluation of the formula by Formulas/Formula Auditing group/Evaluate Formula and keep clicking the "Evaluate" button.