Excel: Column comparison with dates and values

I currently have 4 columns that contain data on Bitcoin Prices and S&P 500 closing day values, as well as their respective dates:

Since the S&P 500 is closed on non-trading days and the Bitcoin market is open 24/7, the amount of data I have for each is unequal; I have way more days for Bitcoin than for the S&P 500.

I want to compare the two columns containing their dates, extract the dates that are matching in both columns, and then output the matches and their corresponding values into a separate column.

Please let me know if any of this is unclear.

Ezra Womark

Posted 2019-11-04T20:36:09.687

Reputation: 1

VLOOKUP? – cybernetic.nomad – 2019-11-04T21:00:40.063

If you are open to changing the format of the data, a pivot table might work. – gns100 – 2019-11-06T17:35:11.723

Answers

Assuming that all original values are on Sheet1 and new values will be placed on a new sheet.

Set the values as follows:

C1 = =Sheet1!C1, "fill" that formula down to as many rows as you want.
D1 = =Sheet1!D1, "fill" that formula down to as many rows as you want.
A1 = =Sheet1!A1
A2 onwards = =VLOOKUP(C2,Sheet1!$A$2:$B$6,1,FALSE), "fill" that formula down to as many rows as you want. Technically you don't need this, you could just set it to the value in the same row, in column C. However, this will give you a blank string in both columns A and B if there isn't a matching value in the original data.
B1 = =Sheet1!B1
B2 onwards = =VLOOKUP(C2,Sheet1!$A$2:$B$6,2,FALSE), "fill" that formula down to as many rows as you want.

Here's what that looks like given the first few rows of data:

Original data:

Sheet 2 without non-trading days:

EDIT 1: Lock the references on Sheet 1

zakaluka

Posted 2019-11-04T20:36:09.687

Reputation: 100

So I tried your suggestion, however, after obtaining 3 rows of data I just got "#N/A" terms for the rest of the sheet(meaning after the date 1/14/2009 I got only "#N/A"). I am not sure exactly what's wrong. Any more help is greatly appreciated! – Ezra Womark – 2019-11-05T19:02:55.843

@EzraWomark Are you using your spreadhseet or my limited dataset? It will show N/A if there's no data in the source file. I have uploaded a spreadsheet here which you can look at with your original data in it. NOTE: I assume that for every S&P date, there is a corresponding Bitcoin date and value. That's why you get #N/A sometimes. To truly find the common dates require VBA code, but that seems like overkill because Bitcoin is traded every day that S&P is, only the reverse is not true.

– zakaluka – 2019-11-08T15:20:46.170

A simple combination of INDEX & MATCH solves the issue:

I would like to suggest two search patterns, since you have not specified that what would be it!

Formula in cell E67:

 =IFERROR(INDEX(D$67:D$74,MATCH(C67,A$67:A$74,0)),"")

N.B. Matches dates from Column C into A.

Formula in cell F67:

 =IFERROR(INDEX(D$67:D$74,MATCH(A67,C$67:C$74,0)),"")

N.B. Formula matches dates from Column A into C.

You may use either of the formulas as is suitable.
Adjust cell references, Dates & related values as needed.

Rajesh S

Posted 2019-11-04T20:36:09.687

Reputation: 6 800