If cell contains value between x-y, should equal certain text

**D-smoke** · 07-12-2012, 10:27 AM

Hello!
I'm trying to get a cell to display certain pre-defined text (from one of 5 other cells) if it equals a certain number range, e.g. 0-50 equals "Warning".

It should also display certain colors depending on the value, but I think I got that covered with the conditional formatting.

Any ideas?
Thanks!

**ChemistB** · 07-12-2012, 10:33 AM

You need a table with the first column being the minimum of that catagory and the second being your text. i.e.
0......warning
51.....something else

then use VLOOKUP(value to lookup, table range, column to return, TRUE) True is optional and means don't look for exact match (False only returns a value if there is an exact match) so if your table is in F1:G6 and your value is in A1
=VLOOKUP(A1, $F$1:$G$6,2, TRUE)
Does that help?

**D-smoke** · 07-12-2012, 10:55 AM

Thanks for you help. Can't get it to work though,. It gives me an "N/A".
I've attached a copy of the file. Perhaps you can see where I've gone wrong? As you can see, cell B25 changes color depending on the value in B25. This is important to keep too.

Number Equal Text.xlsx

Cheers!

**ChemistB** · 07-12-2012, 11:02 AM

I fixed your table in D29:E34 and put the VLOOKUP in B30

**D-smoke** · 07-12-2012, 05:59 PM

This works fine! Thanks ever so much ChemistB. You rock!

**Cutter** · 07-12-2012, 07:15 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save