ISNUMBER & VLOOKUP

M

Mukesh

Is it possible to use ISNUMBER & VLOOKUP together?
example =IF(ISNUMBER(VLOOKUP(E2,Sheet2!$A$1:$B$35....I can't
get it right, please help.

Thanks.
Mukesh
 
J

Jacob Skaria

Try with a test data

=IF(ISNUMBER(VLOOKUP(1,A1:B5,2,FALSE)),"Numeric","Not a numeric")


If this post helps click Yes
 
M

Mukesh

I still can't get it right.....I get #N/A......my code is
=IF(ISNUMBER(VLOOKUP(C214,Email.Filters!$A$1:$B$15,2,FALSE)), " ",
VLOOKUP(C214,Email.Filters!$A$1:$B$15,2,FALSE))

I am trying to look for part of the word, infact using
=IF(ISNUMBER(SEARCH works, but since the list is long I think Vlookup should
be the right way to do...please help.

Thanks.
Mukesh
 
D

Dave Peterson

Are you trying to check for an error when there's no match?

=if(isna(vlookup(e2,Sheet2!$a1:$b$35,2,false)),"",
vlookup(e2,Sheet2!$a1:$b$35,2,false))
 
J

Jacob Skaria

Mukesh

The below formula applies only if you have a match.. If you want to handle
that use ISNA() or ISERROR() or if you are using 2007 use IfERROR().

=IF(ISNA(formula),"",VLOOKUP)

=IF(ISERROR(formula),"",VLOOKUP)

If this post helps click Yes
 
M

Mukesh

Hi Jacob / Dave,

I am trying to get result if the e-mail addresses contains
".us" = USA, ".ae" = UAE, ".jp" = Japan...and so on.

I used =IS(ISNUMBER(SEARCH(".us",C3)),"USA", " ") and
it works fine, but doesn't work if the search is more than 6 times.

I am using xl2003.

Thanks.
Mukesh
 
D

Dave Peterson

Since you're returning "" if there is no match, you could use:

=if(isnumber(search(".us",c3)),"USA","")
&if(isnumber(search(".ae",c3)),"UAE","")
&if(isnumber(search(".jp",c3)),"Japan","")

Or if those are always the last characters in email address:

You could build a table (in another sheet):

A B
jp Japan
US USA
ae UAE

Then this formula will return the characters after the last dot:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+1,99)

(999 is just a number big enough to cover the worst case possibility)

So you could use an extra column (say B) that contains those last few characters
and use a formula like:

=vlookup(b1,sheet2!a:b,2,false)
or
=if(isna(vlookup(b1,sheet2!a:b,2,false)),"Not on list",
vlookup(b1,sheet2!a:b,2,false))

You could actually embed the first formula into the second, but it gets pretty
long.

=IF(ISNA(VLOOKUP(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+1,99),Sheet2!A:B,2,FALSE)),
"Not on list",
VLOOKUP(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+1,99),Sheet2!A:B,2,FALSE))

========
If I were doing it, I'd use the table on the other sheet, extra column and
shorter formulas. I could hide that intermediate column if it was distracting.

But updating the table would be easier than adding more stuff to the long
concatenating formula (which is limited to 1024 characters when measured in R1C1
reference style).
 
M

Mukesh

Hi Jacob / Dave,

I am trying to get result if the e-mail addresses contains
".us" = USA, ".ae" = UAE, ".jp" = Japan...and so on.

I used =IS(ISNUMBER(SEARCH(".us",C3)),"USA", " ") and
it works fine, but doesn't work if the search is more than 6 times.

I am using xl2003.

Thanks.
Mukesh
 
Top