Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function and compare
Hello All,
I am merging two work sheets the first sheet contains 80 rows and the second sheet contains 194 rows and some of these rows are duplicated in sheet 2. I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2. So far the lookup function works fine. However, if a row in sheet 1 is missing in sheet 2 i get the next larger value and i don't want that. when I further resarched it I found the following note about lookup function. "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. " 1)I need help with the following: is there any other function that i can use to return 0 if the value in sheet 1 is not found in sheet2 2)How can I accomidate the duplicate values in sheet2 ...I do want these values to be duplicated in my results too thank you in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function and compare
Try vlookup(A1:sheet2!A1:A100,1,False) or better =vlookup(A1:sheet2!A:A,1,False) and the items flagged #N/A are your missing list -- Student Wrote: Hello All, I am merging two work sheets the first sheet contains 80 rows and the second sheet contains 194 rows and some of these rows are duplicated in sheet 2. I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2. So far the lookup function works fine. However, if a row in sheet 1 is missing in sheet 2 i get the next larger value and i don't want that. when I further resarched it I found the following note about lookup function. "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. " 1)I need help with the following: is there any other function that i can use to return 0 if the value in sheet 1 is not found in sheet2 2)How can I accomidate the duplicate values in sheet2 ...I do want these values to be duplicated in my results too thank you in advance -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function and compare
Thank you for the fast respond that solved the matching problem
However, sheet2 is a table and upon finding the exact match for the column I need to copy the entire row to sheet1 what function may i use for this thank you again "Bryan Hessey" wrote: Try vlookup(A1:sheet2!A1:A100,1,False) or better =vlookup(A1:sheet2!A:A,1,False) and the items flagged #N/A are your missing list -- Student Wrote: Hello All, I am merging two work sheets the first sheet contains 80 rows and the second sheet contains 194 rows and some of these rows are duplicated in sheet 2. I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2. So far the lookup function works fine. However, if a row in sheet 1 is missing in sheet 2 i get the next larger value and i don't want that. when I further resarched it I found the following note about lookup function. "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. " 1)I need help with the following: is there any other function that i can use to return 0 if the value in sheet 1 is not found in sheet2 2)How can I accomidate the duplicate values in sheet2 ...I do want these values to be duplicated in my results too thank you in advance -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function and compare
Hi, On Sheet2 after you have an *N/A list, select a cell in row 1, then Data, Filter, Autofilter, and use the dropdown arrow to select all *N/A items. Select all displayed rows (complete rows all together), COPY, and paste into Sheet 1 after your present data. HTH -- Student Wrote: Thank you for the fast respond that solved the matching problem However, sheet2 is a table and upon finding the exact match for the column I need to copy the entire row to sheet1 what function may i use for this thank you again "Bryan Hessey" wrote: Try vlookup(A1:sheet2!A1:A100,1,False) or better =vlookup(A1:sheet2!A:A,1,False) and the items flagged #N/A are your missing list -- Student Wrote: Hello All, I am merging two work sheets the first sheet contains 80 rows and the second sheet contains 194 rows and some of these rows are duplicated in sheet 2. I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2. So far the lookup function works fine. However, if a row in sheet 1 is missing in sheet 2 i get the next larger value and i don't want that. when I further resarched it I found the following note about lookup function. "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. " 1)I need help with the following: is there any other function that i can use to return 0 if the value in sheet 1 is not found in sheet2 2)How can I accomidate the duplicate values in sheet2 ...I do want these values to be duplicated in my results too thank you in advance -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function and compare
Good Morning
I can't use the filter for the following resons: sheet2 contains User2 (contains the list of 194 users), Type, Date, Time columns I inserted User1 column (contains the list of 80 users) and these are the users that i want The result column is where I applied the Vlookup formula If I filter the result column and delete all the N/A the problem is that my results are not displayed next to their row. look at the exapmle below user @Y118 is on row 5 and the result column is displayed on row 3. If I filter NA from the result column i will be deleting row 5 which has the data for user @Y118 1 uSER1 uSER2 TYPE RESULT DATE TIME 2 @Y040 @Y530 BSYS #N/A 2/8/2006 8:48:00 3 @Y118 @Y042 BSYS @Y118 2/14/2006 13:51:00 4 @Y131 @Y072 BSYS #N/A 1/20/2006 10:21:00 5 @Y133 @Y118 BSYS #N/A 1/17/2006 7:13:00 what i really need is a function like the Vlookup function that will be done on a new sheet and that will match the User1 and User2 and copy the entire row of matched row of User2 Please Advice thank you I really appreciate the effort "Bryan Hessey" wrote: Hi, On Sheet2 after you have an *N/A list, select a cell in row 1, then Data, Filter, Autofilter, and use the dropdown arrow to select all *N/A items. Select all displayed rows (complete rows all together), COPY, and paste into Sheet 1 after your present data. HTH -- Student Wrote: Thank you for the fast respond that solved the matching problem However, sheet2 is a table and upon finding the exact match for the column I need to copy the entire row to sheet1 what function may i use for this thank you again "Bryan Hessey" wrote: Try vlookup(A1:sheet2!A1:A100,1,False) or better =vlookup(A1:sheet2!A:A,1,False) and the items flagged #N/A are your missing list -- Student Wrote: Hello All, I am merging two work sheets the first sheet contains 80 rows and the second sheet contains 194 rows and some of these rows are duplicated in sheet 2. I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2. So far the lookup function works fine. However, if a row in sheet 1 is missing in sheet 2 i get the next larger value and i don't want that. when I further resarched it I found the following note about lookup function. "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. " 1)I need help with the following: is there any other function that i can use to return 0 if the value in sheet 1 is not found in sheet2 2)How can I accomidate the duplicate values in sheet2 ...I do want these values to be duplicated in my results too thank you in advance -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function and compare
Confused about what you are trying to achieve or the reason for inserting User 1 list as column A of sheet 2. Which list do you want as a final? Are there details on sheet 1 in the same column(s) as the detail you want from sheet 2? Do you want a duplicate line in sheet 1 for any matched user from sheet 2, or do you want the details duplicated on the same line in sheet 1? The 114 extra on sheet 2, do you want these added to sheet 1 or ignored? ---------- To produce duplicate info on the same lines in sheet 1, then from sheet 1 do the lookup for eachrequired column, ie If you have data in columns A to T, then in columns AA to AT do lookups from sheet 2 for increasing column numbers, up to: =IF(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE)),"",VLO OKUP(A1,Sheet2!B:B,20,FALSE)) ----------- To produce duplicate lines in Sheet 1 for items in sheet 2, use column AA in sheet 2 for the lookup, and lookup on column B to the range A:A (ex sheet 1) with a test to leave blank if a match is not found. Filter these and Copy lines to sheet 1. =IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),"",VLOOKUP(B1 ,A:A,1,FALSE)) ----------- Does this help? - Student Wrote: Good Morning I can't use the filter for the following resons: sheet2 contains User2 (contains the list of 194 users), Type, Date, Time columns I inserted User1 column (contains the list of 80 users) and these are the users that i want The result column is where I applied the Vlookup formula If I filter the result column and delete all the N/A the problem is that my results are not displayed next to their row. look at the exapmle below user @Y118 is on row 5 and the result column is displayed on row 3. If I filter NA from the result column i will be deleting row 5 which has the data for user @Y118 1 uSER1 uSER2 TYPE RESULT DATE TIME 2 @Y040 @Y530 BSYS #N/A 2/8/2006 8:48:00 3 @Y118 @Y042 BSYS @Y118 2/14/2006 13:51:00 4 @Y131 @Y072 BSYS #N/A 1/20/2006 10:21:00 5 @Y133 @Y118 BSYS #N/A 1/17/2006 7:13:00 what i really need is a function like the Vlookup function that will be done on a new sheet and that will match the User1 and User2 and copy the entire row of matched row of User2 Please Advice thank you I really appreciate the effort "Bryan Hessey" wrote: Hi, On Sheet2 after you have an *N/A list, select a cell in row 1, then Data, Filter, Autofilter, and use the dropdown arrow to select all *N/A items. Select all displayed rows (complete rows all together), COPY, and paste into Sheet 1 after your present data. HTH -- Student Wrote: Thank you for the fast respond that solved the matching problem However, sheet2 is a table and upon finding the exact match for the column I need to copy the entire row to sheet1 what function may i use for this thank you again "Bryan Hessey" wrote: Try vlookup(A1:sheet2!A1:A100,1,False) or better =vlookup(A1:sheet2!A:A,1,False) and the items flagged #N/A are your missing list -- Student Wrote: Hello All, I am merging two work sheets the first sheet contains 80 rows and the second sheet contains 194 rows and some of these rows are duplicated in sheet 2. I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2. So far the lookup function works fine. However, if a row in sheet 1 is missing in sheet 2 i get the next larger value and i don't want that. when I further resarched it I found the following note about lookup function. "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. " 1)I need help with the following: is there any other function that i can use to return 0 if the value in sheet 1 is not found in sheet2 2)How can I accomidate the duplicate values in sheet2 ...I do want these values to be duplicated in my results too thank you in advance -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function and compare
Sorry for causing the confusing
Here is what I am trying to do: I have a sheet that contains 194 users and their information; however, I need to extract only 80 users and their information out of that list. I have a column of 80 users The final list should contain only the 80 users and their information. How can we achieve that? Please advice Thank you "Bryan Hessey" wrote: Confused about what you are trying to achieve or the reason for inserting User 1 list as column A of sheet 2. Which list do you want as a final? Are there details on sheet 1 in the same column(s) as the detail you want from sheet 2? Do you want a duplicate line in sheet 1 for any matched user from sheet 2, or do you want the details duplicated on the same line in sheet 1? The 114 extra on sheet 2, do you want these added to sheet 1 or ignored? ---------- To produce duplicate info on the same lines in sheet 1, then from sheet 1 do the lookup for eachrequired column, ie If you have data in columns A to T, then in columns AA to AT do lookups from sheet 2 for increasing column numbers, up to: =IF(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE)),"",VLO OKUP(A1,Sheet2!B:B,20,FALSE)) ----------- To produce duplicate lines in Sheet 1 for items in sheet 2, use column AA in sheet 2 for the lookup, and lookup on column B to the range A:A (ex sheet 1) with a test to leave blank if a match is not found. Filter these and Copy lines to sheet 1. =IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),"",VLOOKUP(B1 ,A:A,1,FALSE)) ----------- Does this help? - Student Wrote: Good Morning I can't use the filter for the following resons: sheet2 contains User2 (contains the list of 194 users), Type, Date, Time columns I inserted User1 column (contains the list of 80 users) and these are the users that i want The result column is where I applied the Vlookup formula If I filter the result column and delete all the N/A the problem is that my results are not displayed next to their row. look at the exapmle below user @Y118 is on row 5 and the result column is displayed on row 3. If I filter NA from the result column i will be deleting row 5 which has the data for user @Y118 1 uSER1 uSER2 TYPE RESULT DATE TIME 2 @Y040 @Y530 BSYS #N/A 2/8/2006 8:48:00 3 @Y118 @Y042 BSYS @Y118 2/14/2006 13:51:00 4 @Y131 @Y072 BSYS #N/A 1/20/2006 10:21:00 5 @Y133 @Y118 BSYS #N/A 1/17/2006 7:13:00 what i really need is a function like the Vlookup function that will be done on a new sheet and that will match the User1 and User2 and copy the entire row of matched row of User2 Please Advice thank you I really appreciate the effort "Bryan Hessey" wrote: Hi, On Sheet2 after you have an *N/A list, select a cell in row 1, then Data, Filter, Autofilter, and use the dropdown arrow to select all *N/A items. Select all displayed rows (complete rows all together), COPY, and paste into Sheet 1 after your present data. HTH -- Student Wrote: Thank you for the fast respond that solved the matching problem However, sheet2 is a table and upon finding the exact match for the column I need to copy the entire row to sheet1 what function may i use for this thank you again "Bryan Hessey" wrote: Try vlookup(A1:sheet2!A1:A100,1,False) or better =vlookup(A1:sheet2!A:A,1,False) and the items flagged #N/A are your missing list -- Student Wrote: Hello All, I am merging two work sheets the first sheet contains 80 rows and the second sheet contains 194 rows and some of these rows are duplicated in sheet 2. I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2. So far the lookup function works fine. However, if a row in sheet 1 is missing in sheet 2 i get the next larger value and i don't want that. when I further resarched it I found the following note about lookup function. "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. " 1)I need help with the following: is there any other function that i can use to return 0 if the value in sheet 1 is not found in sheet2 2)How can I accomidate the duplicate values in sheet2 ...I do want these values to be duplicated in my results too thank you in advance -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function and compare
Sorry to bother you, but i haven't got a solution yet ...does it mean this is
not doable with Excel "Bryan Hessey" wrote: Confused about what you are trying to achieve or the reason for inserting User 1 list as column A of sheet 2. Which list do you want as a final? Are there details on sheet 1 in the same column(s) as the detail you want from sheet 2? Do you want a duplicate line in sheet 1 for any matched user from sheet 2, or do you want the details duplicated on the same line in sheet 1? The 114 extra on sheet 2, do you want these added to sheet 1 or ignored? ---------- To produce duplicate info on the same lines in sheet 1, then from sheet 1 do the lookup for eachrequired column, ie If you have data in columns A to T, then in columns AA to AT do lookups from sheet 2 for increasing column numbers, up to: =IF(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE)),"",VLO OKUP(A1,Sheet2!B:B,20,FALSE)) ----------- To produce duplicate lines in Sheet 1 for items in sheet 2, use column AA in sheet 2 for the lookup, and lookup on column B to the range A:A (ex sheet 1) with a test to leave blank if a match is not found. Filter these and Copy lines to sheet 1. =IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),"",VLOOKUP(B1 ,A:A,1,FALSE)) ----------- Does this help? - Student Wrote: Good Morning I can't use the filter for the following resons: sheet2 contains User2 (contains the list of 194 users), Type, Date, Time columns I inserted User1 column (contains the list of 80 users) and these are the users that i want The result column is where I applied the Vlookup formula If I filter the result column and delete all the N/A the problem is that my results are not displayed next to their row. look at the exapmle below user @Y118 is on row 5 and the result column is displayed on row 3. If I filter NA from the result column i will be deleting row 5 which has the data for user @Y118 1 uSER1 uSER2 TYPE RESULT DATE TIME 2 @Y040 @Y530 BSYS #N/A 2/8/2006 8:48:00 3 @Y118 @Y042 BSYS @Y118 2/14/2006 13:51:00 4 @Y131 @Y072 BSYS #N/A 1/20/2006 10:21:00 5 @Y133 @Y118 BSYS #N/A 1/17/2006 7:13:00 what i really need is a function like the Vlookup function that will be done on a new sheet and that will match the User1 and User2 and copy the entire row of matched row of User2 Please Advice thank you I really appreciate the effort "Bryan Hessey" wrote: Hi, On Sheet2 after you have an *N/A list, select a cell in row 1, then Data, Filter, Autofilter, and use the dropdown arrow to select all *N/A items. Select all displayed rows (complete rows all together), COPY, and paste into Sheet 1 after your present data. HTH -- Student Wrote: Thank you for the fast respond that solved the matching problem However, sheet2 is a table and upon finding the exact match for the column I need to copy the entire row to sheet1 what function may i use for this thank you again "Bryan Hessey" wrote: Try vlookup(A1:sheet2!A1:A100,1,False) or better =vlookup(A1:sheet2!A:A,1,False) and the items flagged #N/A are your missing list -- Student Wrote: Hello All, I am merging two work sheets the first sheet contains 80 rows and the second sheet contains 194 rows and some of these rows are duplicated in sheet 2. I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2. So far the lookup function works fine. However, if a row in sheet 1 is missing in sheet 2 i get the next larger value and i don't want that. when I further resarched it I found the following note about lookup function. "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. " 1)I need help with the following: is there any other function that i can use to return 0 if the value in sheet 1 is not found in sheet2 2)How can I accomidate the duplicate values in sheet2 ...I do want these values to be duplicated in my results too thank you in advance -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup function and compare
Copy the worksheet of 194 users, and on that sheet in a spare column, do a lookup to the 80 user list, =vlookup(thisUser,UserList1:Userlistend,1,false) and 80 of those should be users, the remaining should be #N/A Copy the column, and Paste Special = Values back over itsself. Select All Data and sort over that column, delete all #N/A lines. That should be your list. If there were other details on the original 80 column sheet, they can be picked up with additional VLoopkup into additional cells. This should suit your purpose -- Student Wrote: Sorry to bother you, but i haven't got a solution yet ...does it mean this is not doable with Excel "Bryan Hessey" wrote: Confused about what you are trying to achieve or the reason for inserting User 1 list as column A of sheet 2. Which list do you want as a final? Are there details on sheet 1 in the same column(s) as the detail you want from sheet 2? Do you want a duplicate line in sheet 1 for any matched user from sheet 2, or do you want the details duplicated on the same line in sheet 1? The 114 extra on sheet 2, do you want these added to sheet 1 or ignored? ---------- To produce duplicate info on the same lines in sheet 1, then from sheet 1 do the lookup for eachrequired column, ie If you have data in columns A to T, then in columns AA to AT do lookups from sheet 2 for increasing column numbers, up to: =IF(ISERROR(VLOOKUP(A1,Sheet2!B:B,1,FALSE)),"",VLO OKUP(A1,Sheet2!B:B,20,FALSE)) ----------- To produce duplicate lines in Sheet 1 for items in sheet 2, use column AA in sheet 2 for the lookup, and lookup on column B to the range A:A (ex sheet 1) with a test to leave blank if a match is not found. Filter these and Copy lines to sheet 1. =IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),"",VLOOKUP(B1 ,A:A,1,FALSE)) ----------- Does this help? - Student Wrote: Good Morning I can't use the filter for the following resons: sheet2 contains User2 (contains the list of 194 users), Type, Date, Time columns I inserted User1 column (contains the list of 80 users) and these are the users that i want The result column is where I applied the Vlookup formula If I filter the result column and delete all the N/A the problem is that my results are not displayed next to their row. look at the exapmle below user @Y118 is on row 5 and the result column is displayed on row 3. If I filter NA from the result column i will be deleting row 5 which has the data for user @Y118 1 uSER1 uSER2 TYPE RESULT DATE TIME 2 @Y040 @Y530 BSYS #N/A 2/8/2006 8:48:00 3 @Y118 @Y042 BSYS @Y118 2/14/2006 13:51:00 4 @Y131 @Y072 BSYS #N/A 1/20/2006 10:21:00 5 @Y133 @Y118 BSYS #N/A 1/17/2006 7:13:00 what i really need is a function like the Vlookup function that will be done on a new sheet and that will match the User1 and User2 and copy the entire row of matched row of User2 Please Advice thank you I really appreciate the effort "Bryan Hessey" wrote: Hi, On Sheet2 after you have an *N/A list, select a cell in row 1, then Data, Filter, Autofilter, and use the dropdown arrow to select all *N/A items. Select all displayed rows (complete rows all together), COPY, and paste into Sheet 1 after your present data. HTH -- Student Wrote: Thank you for the fast respond that solved the matching problem However, sheet2 is a table and upon finding the exact match for the column I need to copy the entire row to sheet1 what function may i use for this thank you again "Bryan Hessey" wrote: Try vlookup(A1:sheet2!A1:A100,1,False) or better =vlookup(A1:sheet2!A:A,1,False) and the items flagged #N/A are your missing list -- Student Wrote: Hello All, I am merging two work sheets the first sheet contains 80 rows and the second sheet contains 194 rows and some of these rows are duplicated in sheet 2. I am using Lookup to determine if the 80 rows from sheet 1 exist in sheet 2. So far the lookup function works fine. However, if a row in sheet 1 is missing in sheet 2 i get the next larger value and i don't want that. when I further resarched it I found the following note about lookup function. "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. " 1)I need help with the following: is there any other function that i can use to return 0 if the value in sheet 1 is not found in sheet2 2)How can I accomidate the duplicate values in sheet2 ...I do want these values to be duplicated in my results too thank you in advance -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534525 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - need a function to compare lists | Excel Worksheet Functions | |||
Limiting the range of a lookup function | Excel Worksheet Functions | |||
An in between lookup function | Excel Worksheet Functions | |||
Compare side by side function | Excel Worksheet Functions | |||
Lookup Function | Excel Worksheet Functions |