Aladin
Posted by Jack on November 13, 2001 1:44 PM
Sorry mate forgot to put in the col result colunm should be 3 for this exercise.
=IF(ISERROR(VLOOKUP($A5, 'Oct2001'!$B$4:$D$5000, 3, FALSE))=TRUE, "", IF(Vlookup($A5, 'Oct2001'!$B$4:$D$5000, 3, FALSE)="",, "" , (Vlookup($A5, 'Oct2001'!$B$4$:$D$5000, 3, FALSE))))
hope thats better, work perfectly, i just dislike #NA so blank is easier that all text remeber i gaze at the screen ALL day and blanks are easier. I know AtuoFilter andl all the rest maybe it just hate #NA's
Hope i got the Formula right this time
Posted by Aladin Akyurek on November 13, 2001 2:25 PM
VLOOKUP continued...
Jack --
I'd expect that the first IF would also catch #N/A.
Would you however try the following and report back [ please in this thread, not in another one :) ] whether it works for you?
=IF(ISNUMBER(MATCH($A5, Oct2001!$B$4:$B$5000, 0)), IF(LEN(VLOOKUP($A5, Oct2001!$B$4:$D$5000, 3, 0))=0, "" , VLOOKUP($A5, Oct2001!$B$4:$D$5000, 3, 0)), "")
Don't let 0's in MATCH and VLOOKUP bother you. It's another way of saying FALSE.
Aladin
Posted by Jack on November 14, 2001 11:01 AM
Re: VLOOKUP continued...
Aladin
Sure stick to this feed no probs, ill just drop back
Tahnks for the new trial, ill advise tommorow night and let you know, funny my formula half my office have emailed about and they all say works just fine, now in intrest if we can better it, all good fun, chhers mate!
Posted by Jack on November 15, 2001 11:08 AM
Re: VLOOKUP continued...
OK Tried the formula works fine in that no #NA but only returns #REF and blank no matter what?
Posted by Aladin Akyurek on November 15, 2001 3:47 PM
Re: VLOOKUP continued...
I don't see why it should return #REF! errors. I'd like to see that worksheet named Oct2001.
Cheers.
Aladin
==========
Posted by Jack on November 16, 2001 4:51 AM
Re: VLOOKUP continued...
Aladin
Ill emial to you monday i do not have email at home, ill send to ....nl acct if ok and ill ewxplain more
A big big thanks....take care.
