| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return matching value
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return matching value
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return matching value
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return matching value
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return matching value
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return matching value
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return matching value
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" wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return matching value
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" wrote: 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" wrote: 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return matching value
That was the solution I was after. Thanks Jacob!! "Jacob Skaria" wrote: 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" wrote: 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" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Look up two values in different columns and return matching value | Excel Discussion (Misc queries) | |||
| matching two worksheets and return to different value | Excel Worksheet Functions | |||
| Return Matching Data Help Needed | Excel Discussion (Misc queries) | |||
| Return all matching values | Excel Worksheet Functions | |||
| Looking and matching vaules to return another vaule | Excel Discussion (Misc queries) | |||
Linear Mode
