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

	A	B	C	D	E	F	G	H	I
998	10/01/2018	100	92
999	09/01/2018	88	81
1000	08/01/2018	63	55
1001					73	20		1

<tbody>
</tbody>

Example B

	A	B	C	D	E	F	G	H	I
998	10/01/2018	100	92
999	09/01/2018	88	81
1000	08/01/2018	63	55
1001					101	70		0

<tbody>
</tbody>

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.