SOLVED

use value in one cell, then compare to another cell finding if the value is between two numbers

Copper Contributor

Hello All,

I am using:

Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20256) 64-bit

 

I am trying to figure out the correct statement that will:
1st. look in the DN column and look at the cells that have the value 3.

 

2nd if the cell has the value 3, I need it to look in the corresponding row and find the value for DIST.

 

3rd if the value for DIST is between certain values (1 to 3, 4 to 6, 7 to 10, and 11+) then I need it to enter that in to the 3RD DOWN column.

 

I can do nested IF AND statements , as long as the info is in one column. ( I did that for the FIELD ZONE column). two columns has me lost, specifically trying to use <= and >= .   I have been messing with this issue for a couple days and my mind isn't grasping it. Is there a way to do it?

 

What I have been doing ( as seen in my sample file) is filtering the DN column for 3, and then filtering the DIST column by the range and manually entering what I need in the 3RD DOWN column. I am hoping to find a better way.

 

Thanks in advance for any help! 

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@CoachGarbs 

=IF(AND(B2=3,C2>=1,C2<=3),"Short 1 to 3",

IF(AND(B2=3,C2>=4,C2<=6),"Medium 4 to 6",

IF(AND(B2=3,C2>=7,C2<=10),"Long 7 to 10",

IF(AND(B2=3,C2>=11),"Very long 11+",""))))

 

This formula returns the results of your range D2:D286.

@OliverScheurich
Thank you!!!
I see exactly where I was going wrong ! Thank you so very much!!!!
It works like a charm! Exactly what I needed!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@CoachGarbs 

=IF(AND(B2=3,C2>=1,C2<=3),"Short 1 to 3",

IF(AND(B2=3,C2>=4,C2<=6),"Medium 4 to 6",

IF(AND(B2=3,C2>=7,C2<=10),"Long 7 to 10",

IF(AND(B2=3,C2>=11),"Very long 11+",""))))

 

This formula returns the results of your range D2:D286.

View solution in original post