Match Dates and values

[xeven_]

Using excel 2016 I have dates in columns A and C and values in columns B and D.
The datesand their corresponding values are not matched but I only want to compare the values in B and D with their correspoding dates. Is there a formula I can put in the adjacent columns to match the dates with one another? I need the dates and values to match to do an accurate comparison om my chart..

Thursday, October 9, 1980	1.1763	Thursday, October 9, 1980	85.64
Friday, October 10, 1980	1.1765	Friday, October 10, 1980	85.59
Monday, October 13, 1980	1.1777	Tuesday, October 14, 1980	85.73
Tuesday, October 14, 1980	1.1774	Wednesday, October 15, 1980	86
Wednesday, October 15, 1980	1.1767	Thursday, October 16, 1980	86.09
Thursday, October 16, 1980	1.1756	Friday, October 17, 1980	86.52
Friday, October 17, 1980	1.1755	Monday, October 20, 1980	86.98
Monday, October 20, 1980	1.1754	Tuesday, October 21, 1980	86.92
Tuesday, October 21, 1980	1.1754	Wednesday, October 22, 1980	87.06

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[jmacleary]

Hi there. If I understand the question correctly, and assuming your data starts in row 2, this formula in E2 would find the relevant rows data from column B: =VLOOKUP(C2,A$2:B$10,2,FALSE) (replace B$10 with B$ followed by the last row number). You can build on this to make a comparison e.g. =IF(VLOOKUP(C2,A$2:B$10,2,FALSE)=D2,"match","no match")

Hope this helps
John

 

[xeven_]

Thanks for the reply. I put the first one in and it seems to N/A in the adjacent cell where the dates dont match. That makes it easier to notice which dates are offset so I can delete/adjust between the two. When trying to go through 10,000 cells of data it gets quite time consuming doing it manually.


Thanks again

 

[jmacleary]

Hi there. Yes, if the vlookup cant find a matching date it will. I'm not sure I understood your question properly then. Try this improvement: =IF(ISNA(VLOOKUP(C2,A$2:B$10,2,FALSE)),"no match",VLOOKUP(C2,A$2:B$10,2,FALSE)). If you then want to also compare column B with D where the dates match use this: =IF(ISNA(VLOOKUP(C2,A$2:B$10,2,FALSE)),"Date not found",VLOOKUP(C2,A$2:B$10,2,FALSE)=D2,"match","no match"))