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

%3CLINGO-SUB%20id%3D%22lingo-sub-2838769%22%20slang%3D%22en-US%22%3Euse%20value%20in%20one%20cell%2C%20then%20compare%20to%20another%20cell%20finding%20if%20the%20value%20is%20between%20two%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2838769%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3EI%20am%20using%3A%3C%2FP%3E%3CP%3E%3C!--%20%20StartFragment%20%20%20--%3E%3C%2FP%3E%3CP%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3EMicrosoft%C2%AE%20Excel%C2%AE%20for%20Microsoft%20365%20MSO%20(Version%202109%20Build%2016.0.14430.20256)%2064-bit%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3C!--%20%20EndFragment%20%20%20--%3E%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20figure%20out%20the%20correct%20statement%20that%20will%3A%3CBR%20%2F%3E1st.%20look%20in%20the%20DN%20column%20and%20look%20at%20the%20cells%20that%20have%20the%20value%203.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2nd%20if%20the%20cell%20has%20the%20value%203%2C%20I%20need%20it%20to%20look%20in%20the%20corresponding%20row%20and%20find%20the%20value%20for%20DIST.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3rd%20if%20the%20value%20for%20DIST%20is%20between%20certain%20values%20(1%20to%203%2C%204%20to%206%2C%207%20to%2010%2C%20and%2011%2B)%20then%20I%20need%20it%20to%20enter%20that%20in%20to%20the%203RD%20DOWN%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20do%20nested%20IF%20AND%20statements%20%2C%20as%20long%20as%20the%20info%20is%20in%20one%20column.%20(%20I%20did%20that%20for%20the%20FIELD%20ZONE%20column).%20two%20columns%20has%20me%20lost%2C%20specifically%20trying%20to%20use%20%26lt%3B%3D%20and%20%26gt%3B%3D%20.%26nbsp%3B%20%26nbsp%3BI%20have%20been%20messing%20with%20this%20issue%20for%20a%20couple%20days%20and%20my%20mind%20isn't%20grasping%20it.%20Is%20there%20a%20way%20to%20do%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20have%20been%20doing%20(%20as%20seen%20in%20my%20sample%20file)%20is%20filtering%20the%20DN%20column%20for%203%2C%20and%20then%20filtering%20the%20DIST%20column%20by%20the%20range%20and%20manually%20entering%20what%20I%20need%20in%20the%203RD%20DOWN%20column.%20I%20am%20hoping%20to%20find%20a%20better%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2838769%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2838955%22%20slang%3D%22en-US%22%3ERe%3A%20use%20value%20in%20one%20cell%2C%20then%20compare%20to%20another%20cell%20finding%20if%20the%20value%20is%20between%20two%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2838955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1183404%22%20target%3D%22_blank%22%3E%40CoachGarbs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(B2%3D3%2CC2%26gt%3B%3D1%2CC2%26lt%3B%3D3)%2C%22Short%201%20to%203%22%2C%3C%2FP%3E%3CP%3EIF(AND(B2%3D3%2CC2%26gt%3B%3D4%2CC2%26lt%3B%3D6)%2C%22Medium%204%20to%206%22%2C%3C%2FP%3E%3CP%3EIF(AND(B2%3D3%2CC2%26gt%3B%3D7%2CC2%26lt%3B%3D10)%2C%22Long%207%20to%2010%22%2C%3C%2FP%3E%3CP%3EIF(AND(B2%3D3%2CC2%26gt%3B%3D11)%2C%22Very%20long%2011%2B%22%2C%22%22))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20returns%20the%20results%20of%20your%20range%20D2%3AD286.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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

@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.

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