Try the below. Customize workbookname and return message

=IF(ISNA(MATCH(A1,[Book2]Sheet1!$A:$A,0)),
"No match found in Book2Sheet1ColA",
IF(ISNA(MATCH(A1,[Book2]Sheet2!$C:$C,0)),
"Found in Sheet1ColA but not in Sheet2ColC",A1))

If this post helps click Yes
---------------
Jacob Skaria


"Gotroots" wrote:

As a mater of interest could I include another sheet to lookup also

along with
[Workbook2.xls]Sheet1!A:A

could
[Workbook2.xls]Sheet2!C:C

also be used in the formula

"Gotroots" wrote:

the first solution by Sean Timmons worked just fine

the spellings "motorbike" and "moterbike" is my mistake Jacob,

thankyou you guys for your expertise you have help me greatly.

"Jacob Skaria" wrote:

Did you notice the spelling difference? I think the OP is looking to match
the spellings "motorbike" and "moterbike"

The below formula is not a perfect solution..This looks for the first 3
characters in the other workbook. You could add more checks like the number
of characters LEN() etc;....

=IF(ISNA(MATCH(LEFT(A1,3)&"*",[Book1]Sheet1!$A:$A,0)),
"No match found",INDEX([Book1]Sheet1!$A:$A,
MATCH(LEFT(A1,3)&"*",[Book1]Sheet1!$A:$A,0)))

If this post helps click Yes
---------------
Jacob Skaria


"Sean Timmons" wrote:

in your column B,

=IF(ISNA(VLOOKUP(A1,[Workbook2.xls]Sheet1!A:A,1,0)),"No Match
Found",VLOOKUP(A1,[Workbook2.xls]Sheet1!A:A,1,0))



"Gotroots" wrote:

Workbook1
Sheet1
A
1 car
2 boat
3 plane
4 train
5 motorbike

Workbook2
Sheet1
A B
1 moterbike formula returns €œmotorbike€�
2 car formula returns €œcar€�
3 lorry formula returns €œno match found€�

The formula looks in workbook1 and returns the same value as that displayed
in colA. If no match is found then display message.