lookup between two worksheets not working well

[Angela]

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.

 

[Mike H]

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

 

[T. Valko]

You can use wildcards in VLOOKUP:

A1 = 123456

=VLOOKUP("*"&A1&"*",table_array,col_index_num,range_lookup)

 

[Angela]

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

 

[Angela]

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]

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