PDA

View Full Version : Solved: vlookup question



vzachin
09-16-2008, 07:27 AM
hi,

this is not a vba issue.

i'm having a problem with vlookup.

in column a, i have a string of 10 characters.
in columns b:k, i have a formula (eg: mid(a2,1,1) ) that places 1 character in the following columns b:k

then i have a lookup formula in column m that looks at data in columns p:q

#1 question:
when there is a numeric value in column b:k, the vlookup returns #N/A. it is not recognizing the numeric value.
if i enter the numerical value manually, then it works.
i then tried copy,pastespecial,values but that also fails.
if i use vba to enter the values, then all is well.

what am i doing wrong? can i fix this without using vba?


#2 question:
when there is no value in column q, the vlookup returns 0. is there a way to eliminate this?
this seems to work:
=IF(VLOOKUP(G2,myrange,2,FALSE) = 0,"",VLOOKUP(G2,myrange,2,FALSE))
but is there another way?

thanks
zach

Bob Phillips
09-16-2008, 07:34 AM
Question #1

=VLOOKUP(IF(ISNUMBER(--H2),--H2,H2),myrange,2,FALSE)

Question #2

Not really, but the proper test is

=IF(VLOOKUP(G2,myrange,2,FALSE)="","",VLOOKUP(G2,myrange,2,FALSE))

vzachin
09-16-2008, 10:02 AM
thanks bob,

here's my final formula:
=IF(ISNA(VLOOKUP(IF(ISNUMBER(--B2),--B2,B2),myrange,2,FALSE)),"",IF(VLOOKUP(IF(ISNUMBER(--B2),--B2,B2),myrange,2,FALSE)="","",VLOOKUP(IF(ISNUMBER(--B2),--B2,B2),myrange,2,FALSE)))

zach