Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching two column ranges with a date
Hi everyone,
In short, I have Column A with a range of contract numbers from A2:A7113. Column B has a range from B2:B354. Column C has a specific date for the contract number in Column B. I need a formula that take a contract number from Column B and searches for it's match in Column A. If there is a match, it needs to enter the date coinciding with that contract from Column C. If there is no match, then it should leave it blank. Although I've tried using the IF statement, I haven't been able to find an effective formula that includes ranges correctly. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching two column ranges with a date
Hi
One way Enter in cell D2 =IF(ISNA(INDEX($A$2:$C$7113,MATCH($B2,$A$2:$A$7113 ,0),3)) ,"",INDEX($A$2:$C$7113,MATCH($B2,$A$2:$A$7113,0),3 )) Format the cell as date Copy down as far as D354 -- Regards Roger Govier "Data Analyst" wrote in message ... Hi everyone, In short, I have Column A with a range of contract numbers from A2:A7113. Column B has a range from B2:B354. Column C has a specific date for the contract number in Column B. I need a formula that take a contract number from Column B and searches for it's match in Column A. If there is a match, it needs to enter the date coinciding with that contract from Column C. If there is no match, then it should leave it blank. Although I've tried using the IF statement, I haven't been able to find an effective formula that includes ranges correctly. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching two column ranges with a date
Thanks Roger. Column D gives me a date by it's not the one that matches up
with the contract number in column B. Is there something I might be missing? "Roger Govier" wrote: Hi One way Enter in cell D2 =IF(ISNA(INDEX($A$2:$C$7113,MATCH($B2,$A$2:$A$7113 ,0),3)) ,"",INDEX($A$2:$C$7113,MATCH($B2,$A$2:$A$7113,0),3 )) Format the cell as date Copy down as far as D354 -- Regards Roger Govier "Data Analyst" wrote in message ... Hi everyone, In short, I have Column A with a range of contract numbers from A2:A7113. Column B has a range from B2:B354. Column C has a specific date for the contract number in Column B. I need a formula that take a contract number from Column B and searches for it's match in Column A. If there is a match, it needs to enter the date coinciding with that contract from Column C. If there is no match, then it should leave it blank. Although I've tried using the IF statement, I haven't been able to find an effective formula that includes ranges correctly. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching two column ranges with a date
Nevermind...I switched the columns and it worked great! thanks again Roger! -
Becca "Data Analyst" wrote: Thanks Roger. Column D gives me a date by it's not the one that matches up with the contract number in column B. Is there something I might be missing? "Roger Govier" wrote: Hi One way Enter in cell D2 =IF(ISNA(INDEX($A$2:$C$7113,MATCH($B2,$A$2:$A$7113 ,0),3)) ,"",INDEX($A$2:$C$7113,MATCH($B2,$A$2:$A$7113,0),3 )) Format the cell as date Copy down as far as D354 -- Regards Roger Govier "Data Analyst" wrote in message ... Hi everyone, In short, I have Column A with a range of contract numbers from A2:A7113. Column B has a range from B2:B354. Column C has a specific date for the contract number in Column B. I need a formula that take a contract number from Column B and searches for it's match in Column A. If there is a match, it needs to enter the date coinciding with that contract from Column C. If there is no match, then it should leave it blank. Although I've tried using the IF statement, I haven't been able to find an effective formula that includes ranges correctly. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching two column ranges with a date
Hi
If I understand your request correctly, column A has 7112 numbers entered in it, and associated with each of those numbers there will be a date in the corresponding row of column C, or column C might be blank. Column B holds numbers which can be found in column A, but there are only 353 numbers represented. In my test data I had A2 514 B2 815 C2 01/01/2007 A3 713 B3 217 C3 15/01/2007 A4 815 B4 713 C4 15/02/2007 A5 969 B5 C5 A6 217 B6 C6 31/03/2007 The values returned in column D are D2 15/02/2007 D3 31/03/2007 D4 15/01/2007 Have I misunderstood what you were looking for? -- Regards Roger Govier "Data Analyst" wrote in message ... Thanks Roger. Column D gives me a date by it's not the one that matches up with the contract number in column B. Is there something I might be missing? "Roger Govier" wrote: Hi One way Enter in cell D2 =IF(ISNA(INDEX($A$2:$C$7113,MATCH($B2,$A$2:$A$7113 ,0),3)) ,"",INDEX($A$2:$C$7113,MATCH($B2,$A$2:$A$7113,0),3 )) Format the cell as date Copy down as far as D354 -- Regards Roger Govier "Data Analyst" wrote in message ... Hi everyone, In short, I have Column A with a range of contract numbers from A2:A7113. Column B has a range from B2:B354. Column C has a specific date for the contract number in Column B. I need a formula that take a contract number from Column B and searches for it's match in Column A. If there is a match, it needs to enter the date coinciding with that contract from Column C. If there is no match, then it should leave it blank. Although I've tried using the IF statement, I haven't been able to find an effective formula that includes ranges correctly. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCHING DATE TO A WEEKDAY | Excel Worksheet Functions | |||
Sum Rows with a matching date | Excel Discussion (Misc queries) | |||
Range matching multiple named Ranges | Excel Discussion (Misc queries) | |||
Matching a Date question | New Users to Excel | |||
How do I count cells in a column of dates between date ranges? | Excel Worksheet Functions |