Index / match with rounding up for multiple points

[Erik Ahrens]

I am trying to figure out how to find in a large table of data the closest result based upon sets of two input values. Nothing I have tried even gets me off of #N/A. Bottom table is a condensed version of the main data table on its own sheet. I have named B2:F2 as Temp, A2:A6 as DewPoint and B2:F6 as DataRange to avoid sheet names in the formula. On other sheets I have lists of historical data and I want to have Excel find the closest points and report back the value. In the example a Temp entry of 92.8 would look to chart for the next highest value (93.2 on C1) and the DewPoint entry of 44.0 would look to it's next highest value (44.6 on A4) and report back the result from C4. Thank you in advance for your assistance!

Temp	DewPoint	Result
92.8	44.0	60.0

<tbody>
</tbody>

	91.4	93.2	95.0	96.8	98.6
41.0	58.1	58.5	58.9	59.2	60.3
42.8	58.8	59.2	59.6	59.9	61.0
44.6	59.6	60.0	60.3	60.7	61.7
46.4	60.4	60.8	61.1	61.5	62.5
48.2	61.2	61.6	62.0	62.3	63.3

<tbody>
</tbody>

 

[Robert Mika]

I am trying to figure out how to find in a large table of data the closest result based upon sets of two input values. Nothing I have tried even gets me off of #N/A. Bottom table is a condensed version of the main data table on its own sheet. I have named B2:F2 as Temp, A2:A6 as DewPoint and B2:F6 as DataRange to avoid sheet names in the formula. On other sheets I have lists of historical data and I want to have Excel find the closest points and report back the value. In the example a Temp entry of 92.8 would look to chart for the next highest value (93.2 on C1) and the DewPoint entry of 44.0 would look to it's next highest value (44.6 on A4) and report back the result from C4. Thank you in advance for your assistance!

Temp	DewPoint	Result
92.8	44.0	60.0

	91.4	93.2	95.0	96.8	98.6
41.0	58.1	58.5	58.9	59.2	60.3
42.8	58.8	59.2	59.6	59.9	61.0
44.6	59.6	60.0	60.3	60.7	61.7
46.4	60.4	60.8	61.1	61.5	62.5
48.2	61.2	61.6	62.0	62.3	63.3

Hello and welcome:

Excel Workbook
	A	B	C	D	E	F
1	Temp	DewPoint	Result
2	92.8	44	60
3
4
5		91.4	93.2	95	96.8	98.6
6	41	58.1	58.5	58.9	59.2	60.3
7	42.8	58.8	59.2	59.6	59.9	61
8	44.6	59.6	60	60.3	60.7	61.7
9	46.4	60.4	60.8	61.1	61.5	62.5
10	48.2	61.2	61.6	62	62.3	63.3
Sheet1

 

[Erik Ahrens]

Thank you very much! That works like a champ - never thought about manipulating the data as you showed, was always trying to get MATCH to do it automatically with 1, 0, -1 function set. I really appreciate it, you have saved me hours and hours.

 

[Robert Mika]

Thank you very much! That works like a champ - never thought about manipulating the data as you showed, was always trying to get MATCH to do it automatically with 1, 0, -1 function set. I really appreciate it, you have saved me hours and hours.

You are welcome