Need Help Please..... need and IF function or a Formula to assist

[ExcelNovice365]

Formulas to assist snippet below

I'm trying to figure out if Values in K column falls in between column D and E?? and have it present, in comumn F, if the turnout values is present between the upper and lower limit.

Column D being the lower limits for that particular chainage and E the Upper limit. 

Now I want a formula to help out but I'm no expert in this, been trying to use If and function but doesn't seem right because there are multiple values of the turn out( columnK)?? 

=IF(AND(D2<$K$2,E2>$K$2),"Turnout","")

but I want to use all the turn out chainages????? 

Please Help.

[#2 · Accepted solution]

@ExcelNovice365 -- Since you did not provide a file, I created my own data for illustration purposes. I hope its close enough to your actual data to understand.

I have concluded that you need to re-structure your data such that:

1. the LIMITS and CHAINAGES tables are combined into a single table eg "TurnoutDecision", and
2. the CHAINAGES values need to be TRANSPOSED and cast as HEADERS

Once the above has been completed, you merely need to

1. reference the VALUE(HEADER) in the CHAINAGES portion of the table, 
2. compare against the LOCATION 1 & 2 with an IF/AND combo statement,
   throw a "1" where the VALUE(HEADER) meets the BETWEEN conditions for Location 1, Location 2:
   
   
   
   Note we WRAP the HEADER reference in VALUE in order to convert the TEXT format that is forced upon HEADER items into a NUMERAL for SUM purposes. Without the VALUE() function, you'll get nowhere fast.
   
   Next, the TurnoutTest Column performs a SUM on all CHAINAGES by ROW(s):
   
   Finally, the "Turnout or Not" column performs an IF on the TurnoutTest value:

Of course, COL F & G are oversimplified here for the purposes of illustration.
I challenge you to combine the "TurnoutTest" & "Turnout Or Not" columns into a single column.

See the attached file.

Lastly, if you need help TRANSPOSING tables data I suggest referencing this: https://stackoverflow.com/questions/60266169/dynamic-arrays-spill-horizontally

/C

[#3]

THANK YOU THANK YOU, This makes sense. :)