SOLVED

New Contributor

# Matching data in one column based on data in another column

I have a column of actual temperatures. I have another column of a range of temperatures and a further column with associated electrical load for each temperature in the range. What I require is for excel to look at my actual temperature and create a column of electrical load data that matches the electrical load for that specific temperature from the range. Tried vlookup with no luck. Thanks
6 Replies

# Re: Matching data in one column based on data in another column

@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)

# Re: Matching data in one column based on data in another column

@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
best response confirmed by MNOP_ (New Contributor)
Solution

# Re: Matching data in one column based on data in another column

@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)``

# Re: Matching data in one column based on data in another column

=INDEX(\$B\$2:\$B\$38,MATCH(C2,\$A\$2:\$A\$38,0))

An alternative could be above formula.

# Re: Matching data in one column based on data in another column

Thank you! This works like a dream

# Re: Matching data in one column based on data in another column

Thanks Quadruple. Will test it as well.