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.

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.

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.

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.

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.

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.