Change text of one cell based on the numbers of a range of cells?

**spookymyo** · 01-18-2013, 02:05 PM

Howdy!

I think I have a tough one here.

I have a row of cells, B8:I8, that have numbers in them controlled by a spin button. The numbers can go from 1 to 20. These numbers are scores.
The row above this, B6:I6, have names in them that coorespond to the scores.

What I'm trying to do is have a seperate cell, A1, look in cells B8:I8 and if any of those numbers are 10 or 20 then it will display the cooresponding name above it.

Ex: If D8 is 10, and D6 is "Bob", then cell A1 will display "Bob". If cell B8 is 20, and B6 is "John", then cell A1 will display "John". If there are no numbers that are 10 or 20 then cell A1 would be blank.

Is this possible?

Eric.

**JBeaucaire** · 01-18-2013, 02:29 PM

Try this:

For 10: =INDEX(B6:I6, MATCH(10, B8:I8, 0))

For 20: =INDEX(B6:I6, MATCH(20, B8:I8, 0))

For 10, then 20, then Blank: =IFERROR(IFERROR(INDEX(B6:I6, MATCH(10, B8:I8, 0)), INDEX(B6:I6, MATCH(20, B8:I8, 0))), "")

**Teethless mama** · 01-18-2013, 02:33 PM

Lookup(1e99,match(b8:i8,{10,20},0),b6:i6)

**spookymyo** · 01-18-2013, 02:37 PM

Thanks guys! Both of those work! This site is awesome!

**Teethless mama** · 01-18-2013, 02:40 PM

You're Welcome!

**spookymyo** · 01-18-2013, 02:55 PM

I have another question about this formula.

How can it be modified to where if i have a cell with TRUE in it, then the formula won't display a name when a score gets to 10, only 20?

Basically it's a checkbox. If the checkbox is ticked then the winning score is 20, if it's not ticked then the winning score is 10. My linked cell to the checkbox is W31.

Or could i just conditionally format AB1 (the cell with the formula in it) to change the text to white if the checkbox's linked cell (W31) is TRUE?

**spookymyo** · 01-18-2013, 03:39 PM

I figured it out with conditional formatting.

I used this formula to change the text to white if the checkbox is ticked. =UPPER(TRIM($W$31))="TRUE"

**spookymyo** · 01-18-2013, 04:50 PM

Awwwwww crap!

Conditionally formatting it with: =UPPER(TRIM($W$31))="TRUE" changes the font to white no matter what number it is. So if the checkbox is ticked, W31 is "TRUE" and then cell AB1 has white font no matter what and then when the value is 20 it's still white.

I'm back to square one. What can i do?

Edit: I figured it out with the help of another post.

Eric.