lookup between two worksheets not working well
 

I have 1 workbook that has two worksheets, I'm using Excel 2003. The cell in
the first worksheet has a # that I want to lookup in the 2nd worksheet to see
if it exists. the problem is the cell in the table_array may contain more
than just the #.

for example

cell in worksheet #1 = "123456"
the cell I want to find in worksheet #2 has "AR 123456 this is a test"

I would expect the vlookup to be able to do an approximate match and
recognize the "AR 123456 test is a test" but it comes back with "NA". I
don't know what I'm doing wrong, was wondering if there is some help out
there.

lookup between two worksheets not working well
 

try this

=MATCH(A1,FIND(A1,Sheet2!A1:A20,1))0

Array entered with Ctrl+Shift+Enter
will return true if it finds a string containing the number

Mike

"Angela" wrote:

I have 1 workbook that has two worksheets, I'm using Excel 2003. The cell in
the first worksheet has a # that I want to lookup in the 2nd worksheet to see
if it exists. the problem is the cell in the table_array may contain more
than just the #.

for example

cell in worksheet #1 = "123456"
the cell I want to find in worksheet #2 has "AR 123456 this is a test"

I would expect the vlookup to be able to do an approximate match and
recognize the "AR 123456 test is a test" but it comes back with "NA". I
don't know what I'm doing wrong, was wondering if there is some help out
there.

lookup between two worksheets not working well
 

You can use wildcards in VLOOKUP:

A1 = 123456

=VLOOKUP("*"&A1&"*",table_array,col_index_num,rang e_lookup)


--
Biff
Microsoft Excel MVP


"Angela" wrote in message
...
I have 1 workbook that has two worksheets, I'm using Excel 2003. The cell
in
the first worksheet has a # that I want to lookup in the 2nd worksheet to
see
if it exists. the problem is the cell in the table_array may contain more
than just the #.

for example

cell in worksheet #1 = "123456"
the cell I want to find in worksheet #2 has "AR 123456 this is a test"

I would expect the vlookup to be able to do an approximate match and
recognize the "AR 123456 test is a test" but it comes back with "NA". I
don't know what I'm doing wrong, was wondering if there is some help out
there.

lookup between two worksheets not working well
 

cool this worked - but only if I removed "True" for the range_lookup

"T. Valko" wrote:

You can use wildcards in VLOOKUP:

A1 = 123456

=VLOOKUP("*"&A1&"*",table_array,col_index_num,rang e_lookup)


--
Biff
Microsoft Excel MVP


"Angela" wrote in message
...
I have 1 workbook that has two worksheets, I'm using Excel 2003. The cell
in
the first worksheet has a # that I want to lookup in the 2nd worksheet to
see
if it exists. the problem is the cell in the table_array may contain more
than just the #.

for example

cell in worksheet #1 = "123456"
the cell I want to find in worksheet #2 has "AR 123456 this is a test"

I would expect the vlookup to be able to do an approximate match and
recognize the "AR 123456 test is a test" but it comes back with "NA". I
don't know what I'm doing wrong, was wondering if there is some help out
there.

lookup between two worksheets not working well
 

how about if I wanted to do the reverse using worksheet #2 "AR 123456 this is
a test" to find match in worksheet #1 "123456" ?

"T. Valko" wrote:

You can use wildcards in VLOOKUP:

A1 = 123456

=VLOOKUP("*"&A1&"*",table_array,col_index_num,rang e_lookup)


--
Biff
Microsoft Excel MVP


"Angela" wrote in message
...
I have 1 workbook that has two worksheets, I'm using Excel 2003. The cell
in
the first worksheet has a # that I want to lookup in the 2nd worksheet to
see
if it exists. the problem is the cell in the table_array may contain more
than just the #.

for example

cell in worksheet #1 = "123456"
the cell I want to find in worksheet #2 has "AR 123456 this is a test"

I would expect the vlookup to be able to do an approximate match and
recognize the "AR 123456 test is a test" but it comes back with "NA". I
don't know what I'm doing wrong, was wondering if there is some help out
there.

lookup between two worksheets not working well
 

That doesn't work very well. You'd have to separate the keyword
(lookup_value) from the string.

--
Biff
Microsoft Excel MVP


"Angela" wrote in message
...
how about if I wanted to do the reverse using worksheet #2 "AR 123456 this
is
a test" to find match in worksheet #1 "123456" ?

"T. Valko" wrote:

You can use wildcards in VLOOKUP:

A1 = 123456

=VLOOKUP("*"&A1&"*",table_array,col_index_num,rang e_lookup)


--
Biff
Microsoft Excel MVP


"Angela" wrote in message
...
I have 1 workbook that has two worksheets, I'm using Excel 2003. The
cell
in
the first worksheet has a # that I want to lookup in the 2nd worksheet
to
see
if it exists. the problem is the cell in the table_array may contain
more
than just the #.

for example

cell in worksheet #1 = "123456"
the cell I want to find in worksheet #2 has "AR 123456 this is a test"

I would expect the vlookup to be able to do an approximate match and
recognize the "AR 123456 test is a test" but it comes back with "NA".
I
don't know what I'm doing wrong, was wondering if there is some help
out
there.