Q: Two sets of XY pairs (four columns); the first increments 'X' by 1 at a time; the second increments it by 7. Any way to have their X values match? These are four columns with over 300 rows. Here are the first few entries of the first (increments X by 1 at a time); and here are the first few of the second (increments X by 7 at a time). I'll call these columns X1, Y1 and X2, Y2 for convenience
I would like to do two things:

*

*Create three columns using X1, Y1, and Y2, where the values of Y2 are placed only on those rows where X2 would have had data. (so, Y2 has a value at rows 0, 7, 14, but is empty on rows 1-6, 8-13, etc.) Example


*Create three columns using X2, Y1, and Y2, where those values of Y1 that would have had a matching entry in X1 but not in X2 are deleted. (so the rows 1-6, 8-13, 15-21, etc. of Y1 are deleted). Example
Any way to do this? I have too many entries to do it manually. Thanks a lot for your time!

A: Create a copy of the first list, let's call it List1.
Use Vlookup to look up the values in the first column of List1 in List2, returning the Y value
Assuming the lists are in two separate sheets called List1 and List2, data in columns A and B, labels in the first row and the formula starts in row 2
=iferror(Vlookup(a2,List2!A:B,2,0),"")

Or, if you have the new Xlookup function
=XLOOKUP(A2,List2!A:A,List2B:B,"")

Similarly, for scenario 2, copy List2 and use the equivalent formula to look up the matching numbers from List1.
If you want the result to be static instead of formulas, copy the three columns and paste them with Paste Special > Values.

A: You could use lookup functions to fill you table, but since your data are occurring at regular intervals, you can easily and efficiently use INDEX and ROW. Next to X1 Y1 add Y2:
=IF(MOD(ROW()-1,7)=0,INDEX(Y2, INT((ROW()-1)/7)+1), "")

Similarly next to X2 Y2 put
=INDEX(Y1, (ROW()-1)*7+1)

