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!!

 

[crystalneedshelpplzthnx]

If I'm understanding you clearly, once a row is found < $H$3, all following rows should fail, even if they become > $H$3.

=IFERROR(if(r17="Failed","Failed",IF(D18<$H$3,"Failed","")),"")

Crystal

 

[matthewlouis]

Yes, Crystal!! Thanks so much, that works, appreciate you.

 

[matthewlouis]

Hey Crystal,

Stumped again. The formula you gave me above works great. However, I am on another spreadsheet and I need to use the same formula except I need to us > and not <.

When I enter =IF(AA19="Failed","Failed",IF(B20>$G$3,"Failed","")) I get Failed when B20 is blank. On the other sheet you helped me, this was not a problem. That formula was =IFERROR(IF(R9="Failed","Failed",IF(C22<$G$3,"Failed","")),"") would return a blank, the only difference in these is the column and I am using > and not <

I have no idea. Same formula . . . using > (doesn't work) instead of < (works).

I am stumped! any idea?

Thanks in advance!

 

[Joe4]

When I enter =IF(AA19="Failed","Failed",IF(B20>$G$3,"Failed","")) I get Failed when B20 is blank.

What is in cells AA19 and G3 in this particular example?

 

[matthewlouis]

B20 and $G$3 are numbers. I copy the formula down so B20 changes and $G$3 (2,520) remains constant.

AA20 is a column that will say Failed if B20 > $G$3. However, B20 is blank . . . so how can B20 be > $G$ and return Failed when there is no number there. It's supposed to return a blank.

 

[Joe4]

B3 and $G$3 are numbers.

I am trying to recreate your exact scenario on my side.
So for any example that is not working for you, please provide me with the exact values in those cells where it is not working for you.

 

[Joe4]

A few other things to note:

If your last reply, you talk about B20 and AA20:

B20 and $G$3 are numbers. I copy the formula down so B20 changes and $G$3 (2,520) remains constant.

AA20 is a column that will say Failed if B20 > $G$3. However, B20 is blank . . . so how can B20 be > $G$ and return Failed when there is no number there. It's supposed to return a blank.

But in your previous reply, your formula is looking at cell AA19, not AA20.

When I enter =IF(AA19="Failed","Failed",IF(B20>$G$3,"Failed","")) I get Failed when B20 is blank.

Are you sure that you have written your formula correctly to look at the correct cells?

One other thing to check to is make sure you are dealing with numbers.
What do the following formulas return?

=ISNUMBER(B20)
=LEN(B20)
=ISNUMBER(G3)

 

[matthewlouis]

Solving for cell AA20

=IF(AA19="Failed","Failed",IF(B20>$G$3,"Failed",""))

AA19 is blank

B20 is blank

$G$3 is 266.07

If B20 is blank, I am asking why it's returning Failed in AA20 because how can a blank be > $G$3.

Thanks, Joe, sorry I wasn't more clear.

 

[Joe4]

=IF(AA19="Failed","Failed",IF(B20>$G$3,"Failed",""))

AA19 is blank

B20 is blank

$G$3 is 266.07

If B20 is blank, I am asking why it's returning Failed in AA20 because how can a blank be > $G$3.

OK. I have set up this scenario, and the formula returned nothing for me, as it should.

So, that means that you probably have the following going on:
Either AA19 or B20 is not really blank (contains a blank space or some special character).
This can be confirmed easily by the formula formulas, which should return 0 in both cases:
=LEN(AA19)
=LEN(B20)
If either one returns anything other than 0, it is not really blank.
If that is the case, then where is the value coming from? A formula (if so, what is the formula)? Or copied or imported from an external source (another file, the web, etc)?