| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or Match
I have two worksheets with the following Data in each column
Sheet 1 A B C D 1 1000 14/08/2008 10:20 15/09/2008 13:45 2 1002 19/08/2008 21:00 17/09/2008 23:55 3 1003 22/08/2008 06:55 22/09/2008 19:35 4 1006 29/08/2008 02:20 25/09/2008 05:15 Sheet 2 A B C D 152 1001 12/08/2008 00:25 10/09/2008 18:35 153 1000 18/08/2008 10:20 15/09/2008 13:45 154 1002 24/08/2008 21:00 17/09/2008 23:55 155 1003 28/08/2008 06:55 22/09/2008 19:35 In column E on Sheet 2 I would like to see the date from Column C on Sheet 1 where the number in Column B Sheet 2 matches the number in Column B Sheet 1 and where their is no match then return "blank" |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or Match
Hi,
=SUMPRODUCT(--(G2=sheet1!B1:B1000),sheet1!C1:C1000) change range to fit your needs but remember both sides of the formula need the same range Format column E with the same format used in column C if you are using excel 2007 =SUMPRODUCT(--(G2=sheet1!B:B),sheet1!C:C) if this helps please click yes thanks "Rob" wrote: I have two worksheets with the following Data in each column Sheet 1 A B C D 1 1000 14/08/2008 10:20 15/09/2008 13:45 2 1002 19/08/2008 21:00 17/09/2008 23:55 3 1003 22/08/2008 06:55 22/09/2008 19:35 4 1006 29/08/2008 02:20 25/09/2008 05:15 Sheet 2 A B C D 152 1001 12/08/2008 00:25 10/09/2008 18:35 153 1000 18/08/2008 10:20 15/09/2008 13:45 154 1002 24/08/2008 21:00 17/09/2008 23:55 155 1003 28/08/2008 06:55 22/09/2008 19:35 In column E on Sheet 2 I would like to see the date from Column C on Sheet 1 where the number in Column B Sheet 2 matches the number in Column B Sheet 1 and where their is no match then return "blank" |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or Match
Try this in E1 of Sheet2:
=IF(ISNA(MATCH(B1,Sheet1!B:B,0)),"",VLOOKUP(B1:She et1!B:C,2,0)) Format the cell as dd/mm/yyyy if you only want to see the date, then copy down. Hope this helps. Pete On Sep 16, 11:31*am, Rob wrote: I have two worksheets with the following Data in each column Sheet 1 * * * * * * * * * * * * * * * * A * * B * * * * *C * * * * * * * * * * * * * * * * D 1 * 1000 * 14/08/2008 10:20 * * 15/09/2008 13:45 2 * 1002 * 19/08/2008 21:00 * * 17/09/2008 23:55 3 * 1003 * 22/08/2008 06:55 * * 22/09/2008 19:35 4 * 1006 * 29/08/2008 02:20 * * 25/09/2008 05:15 Sheet 2 * * * * * * * * * * * * * * * * A * * * *B * * * * * * * C * * * * * * * * * * * * * * * * D 152 *1001 * * * 12/08/2008 00:25 * * * *10/09/2008 18:35 153 *1000 * * * 18/08/2008 10:20 * * * *15/09/2008 13:45 154 *1002 * * * 24/08/2008 21:00 * * * *17/09/2008 23:55 155 *1003 * * * 28/08/2008 06:55 * * * *22/09/2008 19:35 In column E on Sheet 2 I would like to see the date from Column C on Sheet 1 where the number in Column B Sheet 2 matches the number in Column B Sheet 1 and where their is no match then return "blank" |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or Match
Sorry, a typo in there - should be:
=IF(ISNA(MATCH(B1,Sheet1!B:B,0)),"",VLOOKUP(B1,She et1!B:C,2,0)) Pete On Sep 16, 11:45*am, Pete_UK wrote: Try this in E1 of Sheet2: =IF(ISNA(MATCH(B1,Sheet1!B:B,0)),"",VLOOKUP(B1:She et1!B:C,2,0)) Format the cell as dd/mm/yyyy if you only want to see the date, then copy down. Hope this helps. Pete On Sep 16, 11:31*am, Rob wrote: I have two worksheets with the following Data in each column Sheet 1 * * * * * * * * * * * * * * * * A * * B * * * * *C * * * * * * * * * * * * * * * * D 1 * 1000 * 14/08/2008 10:20 * * 15/09/2008 13:45 2 * 1002 * 19/08/2008 21:00 * * 17/09/2008 23:55 3 * 1003 * 22/08/2008 06:55 * * 22/09/2008 19:35 4 * 1006 * 29/08/2008 02:20 * * 25/09/2008 05:15 Sheet 2 * * * * * * * * * * * * * * * * A * * * *B * * * * * * * C * * * * * * * * * * * * * * * * D 152 *1001 * * * 12/08/2008 00:25 * * * *10/09/2008 18:35 153 *1000 * * * 18/08/2008 10:20 * * * *15/09/2008 13:45 154 *1002 * * * 24/08/2008 21:00 * * * *17/09/2008 23:55 155 *1003 * * * 28/08/2008 06:55 * * * *22/09/2008 19:35 In column E on Sheet 2 I would like to see the date from Column C on Sheet 1 where the number in Column B Sheet 2 matches the number in Column B Sheet 1 and where their is no match then return "blank"- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or Match
Opps I misread your post use this
=IF(ISNA(MATCH(c2,Sheet1!B1:B1000,0)),"",SUMPRODUC T(--(C2=sheet1!B1:B1000),sheet1!C1:C1000)) if you are using excel 2007 =IF(ISNA(MATCH(c2,Sheet1!B:B,0)),"",SUMPRODUCT(--(C2=sheet1!B:B),sheet1!C:C)) "Eduardo" wrote: Hi, =SUMPRODUCT(--(G2=sheet1!B1:B1000),sheet1!C1:C1000) change range to fit your needs but remember both sides of the formula need the same range Format column E with the same format used in column C if you are using excel 2007 =SUMPRODUCT(--(G2=sheet1!B:B),sheet1!C:C) if this helps please click yes thanks "Rob" wrote: I have two worksheets with the following Data in each column Sheet 1 A B C D 1 1000 14/08/2008 10:20 15/09/2008 13:45 2 1002 19/08/2008 21:00 17/09/2008 23:55 3 1003 22/08/2008 06:55 22/09/2008 19:35 4 1006 29/08/2008 02:20 25/09/2008 05:15 Sheet 2 A B C D 152 1001 12/08/2008 00:25 10/09/2008 18:35 153 1000 18/08/2008 10:20 15/09/2008 13:45 154 1002 24/08/2008 21:00 17/09/2008 23:55 155 1003 28/08/2008 06:55 22/09/2008 19:35 In column E on Sheet 2 I would like to see the date from Column C on Sheet 1 where the number in Column B Sheet 2 matches the number in Column B Sheet 1 and where their is no match then return "blank" |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or Match
Thank you these have worked fine
"Pete_UK" wrote: Sorry, a typo in there - should be: =IF(ISNA(MATCH(B1,Sheet1!B:B,0)),"",VLOOKUP(B1,She et1!B:C,2,0)) Pete On Sep 16, 11:45 am, Pete_UK wrote: Try this in E1 of Sheet2: =IF(ISNA(MATCH(B1,Sheet1!B:B,0)),"",VLOOKUP(B1:She et1!B:C,2,0)) Format the cell as dd/mm/yyyy if you only want to see the date, then copy down. Hope this helps. Pete On Sep 16, 11:31 am, Rob wrote: I have two worksheets with the following Data in each column Sheet 1 A B C D 1 1000 14/08/2008 10:20 15/09/2008 13:45 2 1002 19/08/2008 21:00 17/09/2008 23:55 3 1003 22/08/2008 06:55 22/09/2008 19:35 4 1006 29/08/2008 02:20 25/09/2008 05:15 Sheet 2 A B C D 152 1001 12/08/2008 00:25 10/09/2008 18:35 153 1000 18/08/2008 10:20 15/09/2008 13:45 154 1002 24/08/2008 21:00 17/09/2008 23:55 155 1003 28/08/2008 06:55 22/09/2008 19:35 In column E on Sheet 2 I would like to see the date from Column C on Sheet 1 where the number in Column B Sheet 2 matches the number in Column B Sheet 1 and where their is no match then return "blank"- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or Match
You're welcome, Rob - thanks for feeding back.
Pete On Sep 16, 1:43*pm, Rob wrote: Thank you these have worked fine "Pete_UK" wrote: Sorry, a typo in there - should be: =IF(ISNA(MATCH(B1,Sheet1!B:B,0)),"",VLOOKUP(B1,She et1!B:C,2,0)) Pete On Sep 16, 11:45 am, Pete_UK wrote: Try this in E1 of Sheet2: =IF(ISNA(MATCH(B1,Sheet1!B:B,0)),"",VLOOKUP(B1:She et1!B:C,2,0)) Format the cell as dd/mm/yyyy if you only want to see the date, then copy down. Hope this helps. Pete On Sep 16, 11:31 am, Rob wrote: I have two worksheets with the following Data in each column Sheet 1 * * * * * * * * * * * * * * * * A * * B * * * * *C * * * * * * * * * * * * * * * * D 1 * 1000 * 14/08/2008 10:20 * * 15/09/2008 13:45 2 * 1002 * 19/08/2008 21:00 * * 17/09/2008 23:55 3 * 1003 * 22/08/2008 06:55 * * 22/09/2008 19:35 4 * 1006 * 29/08/2008 02:20 * * 25/09/2008 05:15 Sheet 2 * * * * * * * * * * * * * * * * A * * * *B * * * * * * * C * * * * * * * * * * * * * * * * D 152 *1001 * * * 12/08/2008 00:25 * * * *10/09/2008 18:35 153 *1000 * * * 18/08/2008 10:20 * * * *15/09/2008 13:45 154 *1002 * * * 24/08/2008 21:00 * * * *17/09/2008 23:55 155 *1003 * * * 28/08/2008 06:55 * * * *22/09/2008 19:35 In column E on Sheet 2 I would like to see the date from Column C on Sheet 1 where the number in Column B Sheet 2 matches the number in Column B Sheet 1 and where their is no match then return "blank"- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
| vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
| vlookup retunrning a match, when not a match... | Excel Worksheet Functions | |||
| second or third match in vlookup() or Match() | Excel Worksheet Functions | |||
| index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
Linear Mode
