i HAVE ALOOK UP RANGE THAT HAS IN THE 1ST COLUMN NUMBERS & OR NUMBERS &
LETTERS THE vLOOK UP ONLY GIVES ME THE NUMBERS IT GIVE n?A ON THE ALPHA OR
ALPHA NUMERIC
I HAVE THEM FORMATTED AS GENERAL IN BOTH LOCATIONS

Hi Jacob,

stop SHOUTING at people, be friendy.

I HAVE THEM FORMATTED AS GENERAL IN BOTH LOCATIONS

well, you must have the same format on both sides. you can check that
with simple formulas. eg. if a1 and b1 should be the same, then write
into c1 the formula

=a1=b1

if the result is "FALSE" then you can edit the formula in c1, select
c1, press F2, higlight "a1" with the mouse, press F9 which will show
you the value it uses, highlight "b1" with the mouse and press F9, then
you shoud see a difference. Eg.

="22"=22 == text 22 and number 22 do not match
="33"="33 " == some databaseprogramm fill fields with blanks,
use trim-functions
etc. etc.

try this an post your results (contents of formulas showing "false")
then we can fix your problem.

arno

Sorry I do most of my tables in caps

I tried and it came back as "FALSE"
I formatted the cell as TEXT on Both (I used only one cell to test)
and it still came back "FALSE"
Jacob

"arno" wrote:

Hi Jacob,

stop SHOUTING at people, be friendy.

I HAVE THEM FORMATTED AS GENERAL IN BOTH LOCATIONS

well, you must have the same format on both sides. you can check that
with simple formulas. eg. if a1 and b1 should be the same, then write
into c1 the formula

=a1=b1

if the result is "FALSE" then you can edit the formula in c1, select
c1, press F2, higlight "a1" with the mouse, press F9 which will show
you the value it uses, highlight "b1" with the mouse and press F9, then
you shoud see a difference. Eg.

="22"=22 == text 22 and number 22 do not match
="33"="33 " == some databaseprogramm fill fields with blanks,
use trim-functions
etc. etc.

try this an post your results (contents of formulas showing "false")
then we can fix your problem.

arno

Sorry I do most of my tables in caps
no worries, but this increases your chances of getting an answer cause
caps is considered as unfriendly, netiquette bla bla.

I tried and it came back as "FALSE"
I formatted the cell as TEXT on Both (I used only one cell to test)
and it still came back "FALSE"

this is why you should _post_ this

="22"=22

stuff!

arno

....or instead of converting all your data you can try and use my solution
posted below :-))

Regards,
KL

"arno" wrote in message
...
Sorry I do most of my tables in caps
no worries, but this increases your chances of getting an answer cause
caps is considered as unfriendly, netiquette bla bla.

I tried and it came back as "FALSE"
I formatted the cell as TEXT on Both (I used only one cell to test)
and it still came back "FALSE"

this is why you should _post_ this

="22"=22

stuff!

arno

Hi KL,
...or instead of converting all your data you can try and use my
solution posted below :-))

I do not agree. I always set the datatypes right once, then I never
have to think about it again. Pls. note that the data can be a
datasource for other databases etc. etc. Then you always have to manage
the problem within the formulas. The best is always to set everything
as early as possible - eg. in the sql-statement you use to query the
data which solves the problem even before the data arrives in excel.
This problem arises very very often so I solve it where it starts.

BTW your formula is _wrong_ because if you search for 1a you will find
it in
1a
xxxx1a
1axxxx
etc
etc.

arno

Hi Jacob,

Try doing something like this:

=VLOOKUP("*"&A1&"*",TEXT(B1:G100,"0"),2,0)

This is an ARRAY formula (Ctrl+Shift+Enter)

Regards,
KL


"JACOB" wrote in message
...
i HAVE ALOOK UP RANGE THAT HAS IN THE 1ST COLUMN NUMBERS & OR NUMBERS &
LETTERS THE vLOOK UP ONLY GIVES ME THE NUMBERS IT GIVE n?A ON THE ALPHA OR
ALPHA NUMERIC
I HAVE THEM FORMATTED AS GENERAL IN BOTH LOCATIONS