how to find and paste the x value of a certain Y value

[bartdv]

X	0	0,273611	0,546389	0,818333	1,090833	1,363611	1,635556	1,908333	2,181389	2,454444	2,726944	2,998889	3,271944	3,544722	3,816944	4,09	4,362778	4,634167	4,907222	5,18	5,452778	5,724722	5,9975	6,27	6,542778	6,815278	7,087222	7,359167	7,631944	7,904722	8,177222	8,925833	9,197778	9,470278	9,743056	10,01556	10,2875	10,56028	10,83222	11,10472	11,37667	11,64944	11,92194	12,19444	12,46583	12,73889	13,01083	13,28361	13,55556	13,82833	14,10083	14,37278	14,64472	14,91667	15,18944	15,46222	15,73472	16,00667	16,27972	16,55194	16,82472	17,09722	17,36972	17,6425	17,91528	18,18778	18,46056	18,73361	19,00639	19,27833	19,55111	19,82306	20,09583	20,36889	20,64083	20,91361	21,18639	21,45833	21,73139	22,00417	22,27722	22,54972	22,82278	23,09472	23,36861	23,64028	23,91306	24,18639	24,45806	24,73	25,00278	25,27556	25,54861	25,82056	26,09361	26,36639	26,63889	26,91167	27,18417	27,45611	27,72917	28,00139	28,27444	28,54722	28,82	29,09278	29,36583	29,63861	29,91139	30,18417	30,45694	30,73	31,0025	31,27444	31,54722	31,81917	32,09194	32,36389	32,63583	32,90889	33,18139	33,45389	33,72611	33,99778	34,27167	34,545	34,81694	35,09056	35,36333	35,63583	35,90778	36,17972	36,45222	36,725	36,99778	37,26972	37,54222	37,81528	38,08806	38,36	38,63278	38,90556	39,17861	39,45056	39,72222	39,99528	40,26722	40,53972	40,81333	41,08722	41,35944	41,63194	41,90472	42,17833	42,45194	42,725	42,99833	43,27222	43,545	43,81694	44,09056	44,36389	44,63639	44,90972	45,18333	45,45694	45,72944	46,00306	46,27556	46,54833	46,82139	47,09417	47,36694	47,63972	47,91306	48,18694	48,45972	48,73306	49,00583	49,27778	49,55167	49,82528	50,09806	50,37139	50,64528	50,91889	51,1925	51,46583	51,73972	52,0125	52,28528	52,55861	52,83139	53,10472	53,37861	53,65139	53,925	54,19833	54,47139	54,74417	55,01556	55,28972	55,5625	55,835	56,10778	56,38083	56,65361	56,92694	57,19972	57,4725	57,74611	58,01861	58,29278	58,56528	58,83806	59,11194	59,38556	59,65944	59,93222	60,20583	60,47778	60,75056	61,02417	61,29778	61,57056	61,84333	62,11722	62,38917	62,66167	62,93528	63,20889	63,48139	63,75417	64,02806	64,30139	64,57417	64,84611	65,11972	65,39139	65,665	65,93833	66,21222	66,48444	66,75833	67,03194	67,30556	67,57889	67,85306	68,12556	68,39806	68,67028	68,94361	69,21722	69,48972	69,76389	70,03639	70,30917	70,58306	70,85667	71,12861	71,40222	71,67528	71,94722	72,22083	72,49417	72,76806	73,03944	73,31194	73,58556	73,85917	74,1325	74,405	74,67889	74,95167	75,22444	75,49806	75,77056	76,04361	76,31583	76,59	76,86167	77,13361	77,405	77,67917	77,95194	78,22528	78,49722	78,77111	79,04306	79,31583	79,58944	79,86194	80,135	80,40778	80,67917	80,95167	81,22444	81,49694	81,76972	82,0425	82,31417	82,58722	82,85889	83,13083	83,40389	83,67667	83,94861	84,22056	84,49278	84,76528	85,03722	85,31028	85,58222	85,855	86,12694	86,39833	86,67083	86,94361	87,21556	87,48889	87,76056	88,03361	88,30639	88,57806	88,85111	89,12306	89,39611	89,66861	89,94111	90,21389	90,48667	90,75861	91,03167	91,30444	91,57667	91,84944	92,12194	92,39389	92,66694	92,93833	93,21167	93,48444	93,75639	94,02944	94,30222	94,57417	94,84694	95,11972	95,39306	95,66472	95,93667	96,20972	96,48222	96,755	97,02694	97,29889	97,57139	97,84333	98,11583	98,38861	98,66056	98,93361	99,20583	99,4775	99,74972	100,0217	100,2942	100,5661	100,8394	101,1114	101,3833	101,6561	101,9292	102,2006	102,4731	102,7458	103,0175	103,2914	103,5639	103,8369	104,1097	104,3825	104,6553	104,9281	105,2006	105,4733	105,745	106,0172	106,2897	106,5628	106,8344	107,1069	107,3806	107,6542	107,9278	108,2011	108,4747	108,7483	109,0219	109,2947
Y	58,58807	20,58807	52,58807	11,58807	-239,412	-319,412	-389,412	-357,412	-398,412	-305,412	-427,412	-319,412	-154,412	-375,412	-250,412	-297,412	-203,412	-405,412	-315,412	-101,412	-150,412	-168,412	-270,412	-123,412	-185,412	26,58807	-125,412	-244,412	136,5881	182,5881	-230,412	-145,412	391,5881	267,5881	93,58807	54,58807	374,5881	140,5881	330,5881	375,5881	196,5881	212,5881	536,5881	199,5881	389,5881	604,5881	467,5881	59,58807	-9,41193	47,58807	545,5881	310,5881	564,5881	646,5881	705,5881	371,5881	1067,588	643,5881	538,5881	674,5881	823,5881	694,5881	850,5881	855,5881	852,5881	1110,588	1412,588	1571,588	1891,588	2190,588	1993,588	2191,588	2646,588	2950,588	2904,588	3303,588	3737,588	3846,588	4327,588	4056,588	4762,588	5166,588	5964,588	6282,588	7858,588	8549,588	9578,588	10515,59	12240,59	13804,59	15386,59	16584,59	24860,59	18872,59	18845,59	32024,59	32780,59	32745,59	33313,59	43169,59	42439,59	40325,59	51763,59	48811,59	61913,59	65539,59	71339,59	62005,59	73080,59	77396,59	80690,59	84152,59	82499,59	87798,59	86589,59	88956,59	84620,59	88782,59	97093,59	112559,6	97716,59	105863,6	103314,6	120254,6	95753,59	102809,6	108508,6	107744,6	110980,6	107909,6	105047,6	100796,6	125571,6	103970,6	106139,6	102489,6	108592,6	109744,6	125682,6	125933,6	129637,6	108159,6	105794,6	111297,6	104304,6	137576,6	121521,6	108169,6	122699,6	127110,6	114879,6	117651,6	117140,6	113323,6	109746,6	119577,6	129808,6	124803,6	134618,6	180208,6	161400,6	156211,6	163348,6	176658,6	158798,6	173784,6	141107,6	173386,6	149431,6	170780,6	180893,6	163022,6	144342,6	123813,6	175691,6	144975,6	160065,6	158710,6	178510,6	122096,6	169226,6	186298,6	159141,6	142417,6	214943,6	187755,6	179554,6	169906,6	213019,6	171971,6	176920,6	186293,6	143303,6	178418,6	178268,6	200769,6	215292,6	184864,6	186063,6	200877,6	218061,6	167961,6	188353,6	194374,6	189318,6	156814,6	187668,6	210884,6	223245,6	191960,6	194748,6	228633,6	186745,6	231359,6	212609,6	157645,6	196224,6	175596,6	178360,6	203716,6	193531,6	208802,6	246878,6	223348,6	221581,6	195984,6	232726,6	218586,6	232550,6	220264,6	213092,6	220381,6	212052,6	218702,6	220532,6	241258,6	198612,6	199124,6	237375,6	234983,6	206740,6	223654,6	216421,6	227253,6	229371,6	244208,6	233426,6	208270,6	219115,6	246958,6	246036,6	187813,6	230436,6	204199,6	221964,6	220420,6	192841,6	239659,6	213148,6	245900,6	235998,6	223392,6	226696,6	200601,6	246958,6	223779,6	246659,6	224147,6	218475,6	188742,6	222706,6	216243,6	212335,6	230831,6	218088,6	238653,6	198615,6	219303,6	245272,6	239076,6	246958,6	244774,6	212859,6	227968,6	245915,6	231626,6	192642,6	246164,6	231335,6	226806,6	239867,6	225405,6	235797,6	233359,6	202870,6	245910,6	246958,6	245947,6	233115,6	245091,6	243966,6	227244,6	245808,6	199359,6	228825,6	246958,6	213554,6	215161,6	246958,6	246958,6	231573,6	225016,6	214137,6	246911,6	235182,6	242463,6	232098,6	221164,6	229546,6	246958,6	240088,6	229498,6	221216,6	246922,6	203347,6	234492,6	238458,6	238854,6	232998,6	218063,6	246958,6	223804,6	214201,6	212428,6	246958,6	246953,6	246958,6	238326,6	216020,6	241684,6	218508,6	246958,6	220354,6	231724,6	246958,6	232973,6	211366,6	246898,6	211004,6	237548,6	235803,6	210664,6	244843,6	224869,6	246958,6	238823,6	234752,6	233114,6	225730,6	244444,6	216157,6	215184,6	213756,6	230605,6	226396,6	246958,6	237194,6	219898,6	246771,6	222282,6	242271,6	246958,6	232507,6	243576,6	215232,6	245970,6	244639,6	246958,6	232107,6	238808,6	244491,6	225947,6	223587,6	229279,6	246958,6	234475,6	240220,6	213381,6	246797,6	230104,6	240818,6	233488,6	246567,6	207311,6	246958,6	246958,6	234388,6	246958,6	225578,6	231969,6
Y ≥	5000	10000	15000	20000	25000	30000	35000	40000	45000
X	?	?	?	?	?	?	?	?	?

<colgroup><col width="64" style="width: 48pt;" span="401"> <tbody> </tbody>

Dear Experts

I am in search for a formula that will do the following:
In the row with the Y data I would like to search for the cell nearest to +5000 and copy the data of that corresponding X cell into the cell marked in yellow.
I don’t see how to work this out.
Thanks in advance
Bart

 

[cyrilbrd]

Hi, so for 5000 do you want to see 2227722 or 2254972?

 

[bartdv]

Hi, so for 5000 do you want to see 2227722 or 2254972?

Dear Cyrubrd
Thans for your respons.
For Y equals or above 5000 I would like to see 22,54972, for 10.000 I would like to see 24,1863, for 15.000 --> 25,00278
The Y values are measured values. I would like to find the X belonging to the first Y value just above 5000, 10.000, 15.000, ... I mention the first value above ... because sometimes the measured values go up, down and up again.

Would this be possible to construct a formula for this?

thanks again
Bart

 

[cyrilbrd]

Dear Cyrubrd
Thans for your respons.
For Y equals or above 5000 I would like to see 22,54972, for 10.000 I would like to see 24,1863, for 15.000 --> 25,00278
The Y values are measured values. I would like to find the X belonging to the first Y value just above 5000, 10.000, 15.000, ... I mention the first value above ... because sometimes the measured values go up, down and up again.

Would this be possible to construct a formula for this?

thanks again
Bart

X	109.2947	109.0219	108.7483	108.4747	108.2011	107.9278	107.6542	107.3806	107.1069	106.8344	106.5628	106.2897	106.0172	105.745	105.4733	105.2006	104.9281	104.6553	104.3825	104.1097	103.8369	103.5639	103.2914	103.0175	102.7458	102.4731	102.2006	101.9292	101.6561	101.3833	101.1114	100.8394	100.5661	100.2942	100.0217	99.74972	99.4775	99.20583	98.93361	98.66056	98.38861	98.11583	97.84333	97.57139	97.29889	97.02694	96.755	96.48222	96.20972	95.93667	95.66472	95.39306	95.11972	94.84694	94.57417	94.30222	94.02944	93.75639	93.48444	93.21167	92.93833	92.66694	92.39389	92.12194	91.84944	91.57667	91.30444	91.03167	90.75861	90.48667	90.21389	89.94111	89.66861	89.39611	89.12306	88.85111	88.57806	88.30639	88.03361	87.76056	87.48889	87.21556	86.94361	86.67083	86.39833	86.12694	85.855	85.58222	85.31028	85.03722	84.76528	84.49278	84.22056	83.94861	83.67667	83.40389	83.13083	82.85889	82.58722	82.31417	82.0425	81.76972	81.49694	81.22444	80.95167	80.67917	80.40778	80.135	79.86194	79.58944	79.31583	79.04306	78.77111	78.49722	78.22528	77.95194	77.67917	77.405	77.13361	76.86167	76.59	76.31583	76.04361	75.77056	75.49806	75.22444	74.95167	74.67889	74.405	74.1325	73.85917	73.58556	73.31194	73.03944	72.76806	72.49417	72.22083	71.94722	71.67528	71.40222	71.12861	70.85667	70.58306	70.30917	70.03639	69.76389	69.48972	69.21722	68.94361	68.67028	68.39806	68.12556	67.85306	67.57889	67.30556	67.03194	66.75833	66.48444	66.21222	65.93833	65.665	65.39139	65.11972	64.84611	64.57417	64.30139	64.02806	63.75417	63.48139	63.20889	62.93528	62.66167	62.38917	62.11722	61.84333	61.57056	61.29778	61.02417	60.75056	60.47778	60.20583	59.93222	59.65944	59.38556	59.11194	58.83806	58.56528	58.29278	58.01861	57.74611	57.4725	57.19972	56.92694	56.65361	56.38083	56.10778	55.835	55.5625	55.28972	55.01556	54.74417	54.47139	54.19833	53.925	53.65139	53.37861	53.10472	52.83139	52.55861	52.28528	52.0125	51.73972	51.46583	51.1925	50.91889	50.64528	50.37139	50.09806	49.82528	49.55167	49.27778	49.00583	48.73306	48.45972	48.18694	47.91306	47.63972	47.36694	47.09417	46.82139	46.54833	46.27556	46.00306	45.72944	45.45694	45.18333	44.90972	44.63639	44.36389	44.09056	43.81694	43.545	43.27222	42.99833	42.725	42.45194	42.17833	41.90472	41.63194	41.35944	41.08722	40.81333	40.53972	40.26722	39.99528	39.72222	39.45056	39.17861	38.90556	38.63278	38.36	38.08806	37.81528	37.54222	37.26972	36.99778	36.725	36.45222	36.17972	35.90778	35.63583	35.36333	35.09056	34.81694	34.545	34.27167	33.99778	33.72611	33.45389	33.18139	32.90889	32.63583	32.36389	32.09194	31.81917	31.54722	31.27444	31.10025	30.73	30.45694	30.18417	29.91139	29.63861	29.36583	29.09278	28.82	28.54722	28.27444	28.00139	27.72917	27.45611	27.18417	26.91167	26.63889	26.36639	26.09361	25.82056	25.54861	25.27556	25.00278	24.73	24.45806	24.18639	23.91306	23.64028	23.36861	23.09472	22.82278	22.54972	22.27722	22.00417	21.73139	21.45833	21.18639	20.91361	20.64083	20.36889	20.09583	19.82306	19.55111	19.27833	19.00639	18.73361	18.46056	18.18778	17.91528	17.6425	17.36972	17.09722	16.82472	16.55194	16.27972	16.00667	15.73472	15.46222	15.18944	14.91667	14.64472	14.37278	14.10083	13.82833	13.55556	13.28361	13.01083	12.73889	12.46583	12.19444	11.92194	11.64944	11.37667	11.10472	10.83222	10.56028	10.2875	10.01556	9.743056	9.470278	9.197778	8.925833	8.177222	7.904722	7.631944	7.359167	7.087222	6.815278	6.542778	6.27	5.9975	5.724722	5.452778	5.18	4.907222	4.634167	4.362778	4.09	3.816944	3.544722	3.271944	2.998889	2.726944	2.454444	2.181389	1.908333	1.635556	1.363611	1.090833	0.818333	0.546389	0.273611	0
Y	231969.6	225578.6	246958.6	234388.6	246958.6	246958.6	207311.6	246567.6	233488.6	240818.6	230104.6	246797.6	213381.6	240220.6	234475.6	246958.6	229279.6	223587.6	225947.6	244491.6	238808.6	232107.6	246958.6	244639.6	245970.6	215232.6	243576.6	232507.6	246958.6	242271.6	222282.6	246771.6	219898.6	237194.6	246958.6	226396.6	230605.6	213756.6	215184.6	216157.6	244444.6	225730.6	233114.6	234752.6	238823.6	246958.6	224869.6	244843.6	210664.6	235803.6	237548.6	211004.6	246898.6	211366.6	232973.6	246958.6	231724.6	220354.6	246958.6	218508.6	241684.6	216020.6	238326.6	246958.6	246953.6	246958.6	212428.6	214201.6	223804.6	246958.6	218063.6	232998.6	238854.6	238458.6	234492.6	203347.6	246922.6	221216.6	229498.6	240088.6	246958.6	229546.6	221164.6	232098.6	242463.6	235182.6	246911.6	214137.6	225016.6	231573.6	246958.6	246958.6	215161.6	213554.6	246958.6	228825.6	199359.6	245808.6	227244.6	243966.6	245091.6	233115.6	245947.6	246958.6	245910.6	202870.6	233359.6	235797.6	225405.6	239867.6	226806.6	231335.6	246164.6	192642.6	231626.6	245915.6	227968.6	212859.6	244774.6	246958.6	239076.6	245272.6	219303.6	198615.6	238653.6	218088.6	230831.6	212335.6	216243.6	222706.6	188742.6	218475.6	224147.6	246659.6	223779.6	246958.6	200601.6	226696.6	223392.6	235998.6	245900.6	213148.6	239659.6	192841.6	220420.6	221964.6	204199.6	230436.6	187813.6	246036.6	246958.6	219115.6	208270.6	233426.6	244208.6	229371.6	227253.6	216421.6	223654.6	206740.6	234983.6	237375.6	199124.6	198612.6	241258.6	220532.6	218702.6	212052.6	220381.6	213092.6	220264.6	232550.6	218586.6	232726.6	195984.6	221581.6	223348.6	246878.6	208802.6	193531.6	203716.6	178360.6	175596.6	196224.6	157645.6	212609.6	231359.6	186745.6	228633.6	194748.6	191960.6	223245.6	210884.6	187668.6	156814.6	189318.6	194374.6	188353.6	167961.6	218061.6	200877.6	186063.6	184864.6	215292.6	200769.6	178268.6	178418.6	143303.6	186293.6	176920.6	171971.6	213019.6	169906.6	179554.6	187755.6	214943.6	142417.6	159141.6	186298.6	169226.6	122096.6	178510.6	158710.6	160065.6	144975.6	175691.6	123813.6	144342.6	163022.6	180893.6	170780.6	149431.6	173386.6	141107.6	173784.6	158798.6	176658.6	163348.6	156211.6	161400.6	180208.6	134618.6	124803.6	129808.6	119577.6	109746.6	113323.6	117140.6	117651.6	114879.6	127110.6	122699.6	108169.6	121521.6	137576.6	104304.6	111297.6	105794.6	108159.6	129637.6	125933.6	125682.6	109744.6	108592.6	102489.6	106139.6	103970.6	125571.6	100796.6	105047.6	107909.6	110980.6	107744.6	108508.6	102809.6	95753.59	120254.6	103314.6	105863.6	97716.59	112559.6	97093.59	88782.59	84620.59	88956.59	86589.59	87798.59	82499.59	84152.59	80690.59	77396.59	73080.59	62005.59	71339.59	65539.59	61913.59	48811.59	51763.59	40325.59	42439.59	43169.59	33313.59	32745.59	32780.59	32024.59	18845.59	18872.59	24860.59	16584.59	15386.59	13804.59	12240.59	10515.59	9578.588	8549.588	7858.588	6282.588	5964.588	5166.588	4762.588	4056.588	4327.588	3846.588	3737.588	3303.588	2904.588	2950.588	2646.588	2191.588	1993.588	2190.588	1891.588	1571.588	1412.588	1110.588	852.5881	855.5881	850.5881	694.5881	823.5881	674.5881	538.5881	643.5881	106.7588	371.5881	705.5881	646.5881	564.5881	310.5881	545.5881	47.58807	-9.41193	59.58807	467.5881	604.5881	389.5881	199.5881	536.5881	212.5881	196.5881	375.5881	330.5881	140.5881	374.5881	54.58807	93.58807	267.5881	391.5881	-145.412	-230.412	182.5881	136.5881	-244.412	-125.412	26.58807	-185.412	-123.412	-270.412	-168.412	-150412	-101412	-315412	-405412	-203412	-297412	-250412	-375412	-154412	-319412	-427412	-305412	-398412	-357412	-389412	-319412	-239412	1158807	5258807	2058807	5858807
Y≥	5000	10000	15000	20000	25000	30000	35000	40000	45000
X	22.54972	24.18639	25.00278	26.36639	26.36639	26.36639	27.45611	27.45611	28.27444

<tbody>
</tbody>

Data in row 2 and 3 sorted by Row 3 from Largest to Smallest.
Formula in B6 is =INDEX($B$2:$OK$2,MATCH(B5,$B$3:$OK$3,-1)) copied right till J6


Note: data adjusted as format is european and I am using US format hence , are .

 

[Aladin Akyurek]

Why not post way smaller sample which is immediately visible along with the desired results based on that sample instead of question marks?

 

[bartdv]

Would yu please define the rows?
X = row...
Y = row...
Y≥ = row...
X = row...

Whould there be a posibility not to sort backwards?
The real worksheet contains for 1 X row, 92 different Y rows. For each Y row I need to calclate the above.

sorry if I misunderstand your solution.
Bart