Posted by Jack on November 13, 2001 11:22 AM

Hi mate been busy this is the formula i promised Vlookup i hate them so tuchy, i plan to make custon function witch will take and age unless a programmer knows better than me and will help, i know exactly how and what i want butt the codes a pain

This never fails me in about a week not i need to point out value and text effect Vlookup and thisis my dislike,
Ture numbers trip o right of cell
If they trip left they are text (even if numbers)
1234 is numbet
A1234 is text
Also text goes left if true text
nont true text goes right
Also the #NA winds me up, i would rather a blank if not returned.

=if(iserror(vlookup(\$A5,'Oct2001'!\$B\$5:\$D5000,FALSE))=TRUE,"",IF(Vlookup(\$A5,'Oct2001'!\$B\$5:\$D5000,FALSE)="","",(\$A5,'Oct2001'!\$B\$5:\$D5000,FALSE)))

I think thats correct if my micd not suffering Brain Fade again, forgot to bring exact home, sorry

What do you think, i feel best about if its right NEVER FAILS

Posted by Aladin Akyurek on November 13, 2001 12:59 PM

VLOOKUP

Jack --

Just trying to understand.
You want to look up the value in \$A5 in Oct2001!\$B\$5:\$D\$5000.

Oct2001!\$B\$5:\$D\$5000 is a 3-column table.

In which column are you searching for the value to be retrieved?
If nothing can be retrieved for some reason, what do you want the VLOOKUP formula to return: a zero, a blank,...?