Matching two column ranges with a date

[Guest]

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!

 

[Roger Govier]

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

 

[Guest]

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?

 

[Guest]

Nevermind...I switched the columns and it worked great! thanks again Roger! -
Becca

 

[Roger Govier]

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?