Return matching value

G

Gotroots

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.
 
S

Sean Timmons

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))
 
J

Jacob Skaria

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
G

Gotroots

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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Gotroots

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 said:
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 said:
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 said:
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))



:

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.
Click to expand...
Click to expand...
Click to expand...
 
J

Jacob Skaria

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 said:
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 said:
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 said:
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


:

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))



:

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.
Click to expand...
Click to expand...
Click to expand...
 
G

Gotroots

Actually I need the value to return regardless if it is in either one or
both sheets. Basically I need to add an addition sheet to search that also.

I am not concerned if the value is not in both sheets, all I need to know
that it is in at least one of the sheets.


Jacob Skaria said:
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 said:
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 said:
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.

:

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


:

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))



:

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.
Click to expand...
Click to expand...
Click to expand...
 
J

Jacob Skaria

Try the below

=IF(ISNA(MATCH(A1,[Book2]Sheet1!$A:$A,0)),
IF(ISNA(MATCH(A1,[Book2]Sheet2!$C:$C,0)),
"Not found",A1),A1)

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


Gotroots said:
Actually I need the value to return regardless if it is in either one or
both sheets. Basically I need to add an addition sheet to search that also.

I am not concerned if the value is not in both sheets, all I need to know
that it is in at least one of the sheets.


Jacob Skaria said:
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 said:
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

:

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.

:

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


:

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))



:

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.
Click to expand...
Click to expand...
Click to expand...
 
G

gootroots

That was the solution I was after.
Thanks Jacob!!

Jacob Skaria said:
Try the below

=IF(ISNA(MATCH(A1,[Book2]Sheet1!$A:$A,0)),
IF(ISNA(MATCH(A1,[Book2]Sheet2!$C:$C,0)),
"Not found",A1),A1)

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


Gotroots said:
Actually I need the value to return regardless if it is in either one or
both sheets. Basically I need to add an addition sheet to search that also.

I am not concerned if the value is not in both sheets, all I need to know
that it is in at least one of the sheets.


Jacob Skaria said:
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


:

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

:

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.

:

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


:

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))



:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF statement help please 4
Vlookup and return sheet name also 2
Return BLANK is cell value sought is blank 13
ISNA with VLOOKUP against 2 locations 7
Vlookups to return a N/A value 3
Correct #N/A Value 6
Match Multiple Criteria and Return values positioned above the zero 6
Index return value REF#! 2

Top