Combine and graph data in Excel with non-matching X values

Question

I have 2 sets of data in tabular form, say (x,y) and (x,z). Both sets share variable x, but for each set x has a different increment (see example). I would like to generate a graph (y,z) without using VBA or third party interpolation apps. Thank you in advance for your responses.

Example:

Dataset 1:

x: 1, 2, 3

y: 1, 2, 7

Dataset 2:

x: 0.3, 0.6, 0.9, 1.2, 1.5, 1.8, 2.1, 2.4, 2.7, 3.0

z: 1,   2,   3,   4,   5,   6,   7,   8,   9,   10

Answer 1

You can easily do it with scatter plot:

• Select your first dataset (both x and y values)
• navigate to insert - charts - XY (Scatter)
  (this can be slightly different depending on your Excel version)
• insert the chart
• right click on the chart - select data - add new series
  • here select data for horizontal and vertical axis from the second dataset

Answer 2

I'm assuming you want to graph Y vs. Z (without X, where one would become the X and the other the Y for graphing purposes. I had to do this just now.

1. I made sure both sets are sorted on the x value, in separate sheets.
2. I took the larger data set (your data set 2).
3. In the third column (the column after your z), top cell, I wrote this formula: =arrayformula(vlookup($a$1:$a$10,'other_sheet'!$a$1:$a$3,2,true). Call this column y.
4. I created a chart from columns Z and Y. You may have to switch Z and Y to get the correct series on the X axis.

Notes:

• 'other_sheet' has the smaller data set (your set 1). I don't know what's more efficient. I'm assuming that a larger number of VLOOKUPs on a smaller data set is more efficient than a smaller number of lookups on a larger data set.
• I'm starting both data sets from $a$1,assuming no header rows; you can adjust that.