If Statement using values within range

Hello,

The question is this.

Cell in Sheet 1 A2 has a number (1-10). A range exisits in Sheet 2 where numbers 1-5 are catorigzied as blue, and where 6-10 are catogorized as red. In Sheet 1 B2 I need a formula that states if A2 is equal to a value within a range (lets say blue) then Cell B2 will reflect the text "Blue". I will then use nested If statments if untrue to check if cell B2 is equal to anothe value within another range and so on.

the only thing I can come up with is this: =IF(A1=Blue,"Y",IF(A1=Red,"Y"," ")) but it returns the error #VALUE.

any suggestions

Hi,

You need to use quotation marks when referencing text:

=IF(A1="Blue","Y",IF(A1="Red","Y"," "))

Thanks for the reply but that doesnt do it. That makes the formula look to see if the cell is = specifically to the text "Blue" rather than looking to see if it matches one of the values which is within the range Blue.

To clarify in Sheet two A2:A6 has been named as range Blue and A8:A12 has been named as range Red. What I need in Sheet 1 B2 is a formula that looks at the value of Sheet 1 A2 and checks to see if it is an applicable value within range Blue and if so state Blue. If not Check to see if the value is an applicable value within range Red and so on. If all untrue then reflect blank value (" ").

A1 Blue
A2 1
A3 2
A4 3
A5 4
A6 5
A7 Red
A8 6
A9 7
A10 8
A11 9
A12 10

Thanks tho.

I am using numerical values in the spirit of simplicity. The values within the Ranges I have named are truly text. So maybe I should state something similar to the follwing.


A1 Blue
A2 Light
A3 Royal
A4 Sea
A5 Sky
A6 Navy
A7 Red
A8 Fire Engine Red
A9 Light Red
A10 Burgundy
A11 Magenta
A12 Scarlet

Thanks for your insight. Won’t be able to attach a numerical value but certainly a good thought.

You can use a VLOOKUP to categorise data by a range of values.

Have a look at this link.

I know I'm really late to this post, but wanted to chime in just incase someone else had this same question.

You have you use a "IF(And" nested function to make this work.

=IF(AND(A1>=1,A1<=5),"RED",IF(AND(A1>=6,A1<=11),"Blue"))

So this statement is saying if A1 is greater than or equal to 1 (AND) less than or equal to 5 then return "RED" Then there's another statement which is the same, but would be concidered the "NESTED" part of your formula. the nested statement is saying if A1 is greater than or equal to 6 (AND) less than or equal to 11 then return "BLUE". I've used this statement for Date ranges. so i do know that you can nest upto 12 statements maybe more.