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..

[TABLE="width: 570"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Thursday, October 9, 1980[/TD]
[TD]1.1763[/TD]
[TD]Thursday, October 9, 1980[/TD]
[TD]85.64[/TD]
[/TR]
[TR]
[TD]Friday, October 10, 1980[/TD]
[TD]1.1765[/TD]
[TD]Friday, October 10, 1980[/TD]
[TD]85.59[/TD]
[/TR]
[TR]
[TD]Monday, October 13, 1980[/TD]
[TD]1.1777[/TD]
[TD]Tuesday, October 14, 1980[/TD]
[TD]85.73[/TD]
[/TR]
[TR]
[TD]Tuesday, October 14, 1980[/TD]
[TD]1.1774[/TD]
[TD]Wednesday, October 15, 1980[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]Wednesday, October 15, 1980[/TD]
[TD]1.1767[/TD]
[TD]Thursday, October 16, 1980[/TD]
[TD]86.09[/TD]
[/TR]
[TR]
[TD]Thursday, October 16, 1980[/TD]
[TD]1.1756[/TD]
[TD]Friday, October 17, 1980[/TD]
[TD]86.52[/TD]
[/TR]
[TR]
[TD]Friday, October 17, 1980[/TD]
[TD]1.1755[/TD]
[TD]Monday, October 20, 1980[/TD]
[TD]86.98[/TD]
[/TR]
[TR]
[TD]Monday, October 20, 1980[/TD]
[TD]1.1754[/TD]
[TD]Tuesday, October 21, 1980[/TD]
[TD]86.92[/TD]
[/TR]
[TR]
[TD]Tuesday, October 21, 1980[/TD]
[TD]1.1754[/TD]
[TD]Wednesday, October 22, 1980[/TD]
[TD]87.06[/TD]
[/TR]
</tbody>[/TABLE]

 

[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"))