Display Text Based on Values in Cell Range

**kythom** · 03-21-2013, 12:25 PM

Hi,

What I want this to accomplish is if a 3 is anywhere in the range of cells (B2:B6) on the input tab, it will display the text "EMERGENCY EVENT ACTIVE" on the output tab. If a 2 is displayed anywhere in the same range, display "EVENT POSSIBLE". And if a 1 is displayed, "NO CURRENT EVENTS". What I have set up now is =IF(LOOKUP(3,Input!B2:B6)=3,"EMERGENCY EVENT ACTIVE",IF(LOOKUP(2,Input!B2:B6)=2,"EVENT POSSIBLE","NO CURRENT EVENTS")) and it works for the cells B4 through B6, but it seems to look past B2 and B3. When B2 or B3 contain a 3 for "EVENT POSSIBLE", the output does not respond.

Any clue as to why it isn't working properly for all the cells in the range? Should I use a different formula?

Thank you in advance!

(I've attached the basic template of what the issue I'm having is)

**kbkumar** · 03-21-2013, 12:33 PM

Hi - Welcome to the forum.

Perhaps you need to narrow down your criteria. For ex. what should be displayed in in output sheet if range B2:B6 has got all criterias 1, 2 and 3 or any 2 of these 3 criterias....

Thanks.

**kythom** · 03-25-2013, 02:25 PM

Yes I suppose that would possibly work. Although I'm not sure what the function would look like? Could you maybe give me an example as to what you would do?

Thanks

Edit:

Yeah I'm not sure what to do to get this to work. It seems that if I limit the range to only include 3 cells, the output will correlate properly. But I would like to have at least 5 cells that could change the output display. It needs to display "emergency" whenever there is a 3 anywhere in the range, display "possible" if there is a 2 anywhere in the range of cells (if there are no 3's in the range anyhow), and "none" when there are only 1's in the cells.

I'm just not understanding why it works for up to 3 cells in a range but not 5.

I'd appreciate any help soon!