ISNA error

[PM1]

I have the following formula:

=IF(J15<"10/10/05",VLOOKUP(E15,'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,5,FALSE),IF(J15>"11/10/05"<"19/10/05",VLOOKUP(E15,'Pricing 10th Oct - 19th Oct'!$B$12:$F$148,5,FALSE),IF(J15>"19/10/05",VLOOKUP(E15,'Pricing 20th Oct - 31st oct'!$B$12:$F$150,5,FALSE))))

which is producing the correct result when the search criteria is found, the problem is that I am getting #N/A where the result is not found. I have tried using if(isna) but to no avail. I got it to work with:-

=IF(ISERROR((IF(J175<"10/10/05",VLOOKUP(E175,'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,5,FALSE),IF(J175>"11/10/05"<"19/10/05",VLOOKUP(E175,'Pricing 10th Oct - 19th Oct'!$B$12:$F$148,5,FALSE),IF(J175>"19/10/05",VLOOKUP(E175,'Pricing 20th Oct - 31st oct'!$B$12:$F$150,5,FALSE)))))),"- NOT on ATLAS",(IF(J175<"10/10/05",VLOOKUP(E175,'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,5,FALSE),IF(J175>"11/10/05"<"19/10/05",VLOOKUP(E175,'Pricing 10th Oct - 19th Oct'!$B$12:$F$148,5,FALSE),IF(J175>"19/10/05",VLOOKUP(E175,'Pricing 20th Oct - 31st oct'!$B$12:$F$150,5,FALSE))))))

but this seems unmanageable.

All I want to do is select a value and it's date in Sheet 1, look for it's value within the date period in either sheets 2,3 or 4 and return a result.

There must be an easier way to do this.

 

[cfree36]

You never tell it what to do if the value is not found.

before the last ')' put what you want to see if the value does not meet any of the three criteria

,0
,""
,"Not Found"
etc.

 

[Oaktree]

Not quite, cfree. If no third option is given to the IF function, FALSE (not #N/A) is returned as a default. [Consider =IF(1>2, "This is correct")]

PM's problem is how to handle the vlookups. Consider something like:

if(ISNA(match(E15,'Pricing 26 Aug - 10th Oct'!$B$12:$B$148,0)),"Bad entry",VLOOKUP(E15,'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,5,FALSE))

instead of:

VLOOKUP(E15,'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,5,FALSE)

etc.

 

[GlennUK]

You have other things to worry about too ... is the entry in J15 a date? If so, you shouldn't be comparing it to text strings, but to DATEVALUE("10/10/05") and so on. And logic tests like :

J15>"11/10/05"<"19/10/05"

are nonsense. You should be using the AND function in cases like that.

 

[PM1]

GlennUK,

Yes "J15" is a date and I think you are correct as far as the logic tests are concerned, I have just found that it's only looking at the first lookup statement and producing the result from that.

Now I'm really lost on how to convert the Column to DATEVALUE let alone use the AND() Function as well.

 

[Aladin Akyurek]

I have the following formula:

=IF(J15<"10/10/05",VLOOKUP(E15,'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,5,FALSE),IF(J15>"11/10/05"<"19/10/05",VLOOKUP(E15,'Pricing 10th Oct - 19th Oct'!$B$12:$F$148,5,FALSE),IF(J15>"19/10/05",VLOOKUP(E15,'Pricing 20th Oct - 31st oct'!$B$12:$F$150,5,FALSE))))...

Assuming that the result is a number when the VLOOKUP formula succeeds...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP(E15,CHOOSE(MATCH(J15,{0,38636,38645}),'Pricing 26 Aug - 10th Oct'!$B$12:$F$148,'Pricing 10th Oct - 19th Oct'!$B$12:$F$148,'Pricing 20th Oct - 31st oct'!$B$12:$F$150),5,0)))

 

[PM1]

Sincerest Thanks Aladin, it worked

POST CLOSED


Thankyou all for your contribution - please check out my previous challenging post which is still awaiting conclusion:- :wink:

http://www.mrexcel.com/board2/viewtopic.php?t=176342