Vlookup

[matthewlouis]

Column R18 uses the formula =IFERROR(IF(D18<$H$3,"Failed",""),"")

D = current price

$H$3 is the price I am comparing D18 to. $H$3 is constant, of course

Once the price in Column D becomes LESS THAN the fixed price of $H$3, the cell in R18 says "FAILED." That's great. But when I copy the formula down and the cells in Column D become > than $H$3 again, Column D's price shows up again . . . I don't want that. Once a cell in Column R says "FAILED, I want every Column R cell AFTER that to say "FAILED" regardless if any of the following prices in Column D show up > $H$3 somewhere down the column.

This is messing up my Lookup formula in R4 =IFERROR(VLOOKUP("Failed",$R$6:$R$133,1,FALSE),"") because it's looking up everything AFTER R18 and including it in the Lookup.

Any ideas? Thanks in advance!!

 

[Joe4]

That makes no sense to me.

So, you are seeing "#ERROR " in cell B20, but
=ISERROR(B20) returns FALSE
=IFERROR(B20,"Error found!") returns a blank
=B20="#ERROR " returns #ERROR again

Is that correct?
That does not make any sense to me. These tests suggest that it is not an error or a string.
We have to figure out what it is so we can capture it and address it.

Are you still seeing "#ERROR" in cell B20, or did you remove it or change something?

 

[matthewlouis]

Yes, that is correct on all three items you stated.

This is the formula in B20 with no conditional formats or conditional formulas (returns #ERROR )

=IF(A20=$H$3,xlqHigh($A$1,"Yahoo"),xlqhHigh($A$1,A20,"Yahoo"))

A20 = today's date
$H$3 = today's date
$A$1 = ^GSPC (symbol for S&P 500 Index)

 

[Joe4]

I wish I could see the coding behind the "xlqhHigh" function to see how it is working. It is behaving very oddly. It seems to not be returning a String or an Error when this error message happens, so the question is, what is it?

When there isn't an error, and it works, does it return a numeric value?

If so, perhaps we can adjust our original formula to this:

[COLOR=#333333]=IF(AA19="Failed","Failed",IF([/COLOR][COLOR=#ff0000]IF(ISNUMBER(B20),B20,0)[/COLOR][COLOR=#333333]>$G$3,"Failed",""))
[/COLOR]

 

[matthewlouis]

Viola!!!! You just fixed it, Joe!! Nice, thank you so much for sticking with this. Appreciate it.

Yes, when it works, it provides a number.

Thanks again!

 

[Joe4]

Whew! Excellent!
Glad we were able to finally crack it.