Feb 05 2022 03:37 PM
Feb 05 2022 05:31 PM
@MNOP_ I suspect the lookup range with the "range of temperatures" looks something like
60 - 69
70 - 79
80 - 89
but excel doesn't understand those ranges as numbers, just text.
I suggest splitting those into 2 columns call them Range Low and Range High (or whatever you like)
Then you can use any of the Lookup functions. (note the 2nd column is really just for viewing because the lookup formula just uses the 1 column because it assumes it is in order and continuous)
Feb 06 2022 03:31 AM
@mtarler Thank you so much for your reply. its actually not a range. A have a list of temp values (column A) that has a load profile (column B). Now need to match the actual temp (column C) with a matched load from the load data. For example, the first row at 15 deg should pop up the number 24214 kW. I have over 8700 rows to do this for :)
thanks again for your help and excel example. Very kind of you.
TEMP data reference points | Temperature Variable Load (kW) reference data points | Actual Time variable Temp | Actual Matched Load ? |
-3 | 23180.1 | 15 | |
-2 | 23215.0 | 14 | |
-1 | 23256.4 | 14 | |
0 | 23311.9 | 15 | |
1 | 23381.7 | 18 | |
2 | 23479.9 | 21 | |
3 | 23615.5 | 23 | |
4 | 23603.4 | 25 | |
5 | 23627.4 | 27 | |
6 | 23691.4 | 28 | |
7 | 23739.9 | 28 | |
8 | 23817.0 | 28 | |
9 | 23848.1 | 28 | |
10 | 23885.3 | 28 | |
11 | 23930.9 | 0 | |
12 | 23998.0 | 25 | |
13 | 24061.4 | 22 | |
14 | 24133.4 | 21 | |
15 | 24214.0 | 20 | |
16 | 24303.5 | 19 | |
17 | 24401.6 | 18 | |
18 | 24500.2 | 17 | |
19 | 24660.1 | 17 | |
20 | 24748.4 | 16 | |
21 | 24956.2 | 15 | |
22 | 25172.2 | 15 | |
23 | 25435.7 | 14 | |
24 | 25720.3 | -3 | |
25 | 26042.8 | 18 | |
26 | 26413.1 | 21 | |
27 | 26825.1 | 21 | |
28 | 27018.3 | 21 | |
29 | 27135.9 | 10 | |
30 | 27258.9 | 21 | |
31 | 27386.1 | 21 | |
32 | 27518.6 | -2 | |
33 | 27657.5 | 21 |
Feb 06 2022 04:19 AM
Solution@MNOP_ Assuming the reference temperature -3 is in cell A2, try this:
=VLOOKUP(C2,$A$2:$B$38,2,FALSE)
or, of you are using Excel MS365 or 2021, try this:
=XLOOKUP(C2:C38,A2:A38,B2:B38)
Feb 06 2022 04:57 AM
Feb 06 2022 05:08 AM
Feb 06 2022 05:09 AM
Feb 06 2022 04:19 AM
Solution@MNOP_ Assuming the reference temperature -3 is in cell A2, try this:
=VLOOKUP(C2,$A$2:$B$38,2,FALSE)
or, of you are using Excel MS365 or 2021, try this:
=XLOOKUP(C2:C38,A2:A38,B2:B38)