Search list

[dtrom1]

Hoping someone can help! In Columns A2:A1000 I have dates. The column is ordered by most recent date first (i.e. A1 being todays date). In Columns B2:B1000 I have a series of numbers labelled with column heading 'High' (in B2). In Columns C2:C1000 I have a series of numbers labelled with column heading 'Low' (in C2). The values are in no order at all.

In cell E1001 I have a value (HIGH look up) and in cell F1001 I have another value (LOW look up). Basically, in cell H1001, I want to run an IF statement. The statement will return a '1' if the highest value is reached in cells B2:B1000 before the lowest value (which is in cell H1001) is reached in columns C2:C1000. In summary the rule should search to see if the values in column B are higher than that in E1001 before the lowest value in F1001.

Example A
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]998[/TD]
[TD]10/01/2018[/TD]
[TD]100[/TD]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]999[/TD]
[TD]09/01/2018[/TD]
[TD]88[/TD]
[TD]81[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]08/01/2018[/TD]
[TD]63[/TD]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]73[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example B
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]998[/TD]
[TD]10/01/2018[/TD]
[TD]100[/TD]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]999[/TD]
[TD]09/01/2018[/TD]
[TD]88[/TD]
[TD]81[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]08/01/2018[/TD]
[TD]63[/TD]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]101[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So in the example A below, a '1' would be returned because a value higher than 73 is reached first - i.e. on 9/1/2018. In example B, a '0' is returned because Column C returns a lower value (55) first - i.e. it is lower than 70.

 

[FDibbins]

I would probably do this with 2 MATCH functions

You know the High and the LOW, so something like...
=if(MATCH(high-value,B:B,0)>match(low-value,C:C,0),1,"")

 

[dtrom1]

I would probably do this with 2 MATCH functions

You know the High and the LOW, so something like...
=if(MATCH(high-value,B:B,0)>match(low-value,C:C,0),1,"")

Thanks but that does not work - even after replacing with the correct cell numbers. I get a N/A

 

[FDibbins]

Did you try just using 1 to see if it works? (your numbers may be text numbers)

 

[dtrom1]

Did you try just using 1 to see if it works? (your numbers may be text numbers)

Yes tried and no luck - how do you mean text umbers?

 

[dtrom1]

Could it be that the formula is looking for an exact match? So for example I may want to look for a value of 33 but as 44 is higher, if it reaches this value first then the formula is satisfied. Hope this helps.