Dynamic Chart to show the range based on the condition

[Laxmi Nayak]

Hi Guys,

I have created a clustered column chart and scattered chart to show the market rate and the actual rate as shown below.

Now for each job titles with location I have been manually looking for FTE, Terms value by inputting respective values from the data stored in 'Inputs' sheet.

Is there a formula that I can write in X series and Y series to consider values based on the series name or something. I want it to pick values dynamically without having to manually adjust the X and Y series.

Please let me know if my question is clear. I would really appreciate your help and owe you big time!

Thanks in advance!