SOLVED

Matching data in one column based on data in another column

%3CLINGO-SUB%20id%3D%22lingo-sub-3121581%22%20slang%3D%22en-US%22%3EMatching%20data%20in%20one%20column%20based%20on%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121581%22%20slang%3D%22en-US%22%3EI%20have%20a%20column%20of%20actual%20temperatures.%20I%20have%20another%20column%20of%20a%20range%20of%20temperatures%20and%20a%20further%20column%20with%20associated%20electrical%20load%20for%20each%20temperature%20in%20the%20range.%20What%20I%20require%20is%20for%20excel%20to%20look%20at%20my%20actual%20temperature%20and%20create%20a%20column%20of%20electrical%20load%20data%20that%20matches%20the%20electrical%20load%20for%20that%20specific%20temperature%20from%20the%20range.%20Tried%20vlookup%20with%20no%20luck.%20Thanks%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3121581%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121695%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20data%20in%20one%20column%20based%20on%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121695%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298217%22%20target%3D%22_blank%22%3E%40MNOP_%3C%2FA%3E%26nbsp%3BI%20suspect%20the%20lookup%20range%20with%20the%20%22range%20of%20temperatures%22%20looks%20something%20like%3CBR%20%2F%3E60%20-%2069%3CBR%20%2F%3E70%20-%2079%3CBR%20%2F%3E80%20-%2089%3CBR%20%2F%3Ebut%20excel%20doesn't%20understand%20those%20ranges%20as%20numbers%2C%20just%20text.%3CBR%20%2F%3EI%20suggest%20splitting%20those%20into%202%20columns%20call%20them%20Range%20Low%20and%20Range%20High%20(or%20whatever%20you%20like)%3CBR%20%2F%3EThen%20you%20can%20use%20any%20of%20the%20Lookup%20functions.%20(note%20the%202nd%20column%20is%20really%20just%20for%20viewing%20because%20the%20lookup%20formula%20just%20uses%20the%201%20column%20because%20it%20assumes%20it%20is%20in%20order%20and%20continuous)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3122264%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20data%20in%20one%20column%20based%20on%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122264%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%20Thank%20you%20so%20much%20for%20your%20reply.%20its%20actually%20not%20a%20range.%20A%20have%20a%20list%20of%20temp%20values%20(column%20A)%20that%20has%20a%20load%20profile%20(column%20B).%20Now%20need%20to%20match%20the%20actual%20temp%20(column%20C)%20with%20a%20matched%20load%20from%20the%20load%20data.%20For%20example%2C%26nbsp%3B%20the%20first%20row%20at%2015%20deg%20should%20pop%20up%20the%20number%2024214%20kW.%20I%20have%20over%208700%20rows%20to%20do%20this%20for%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20again%20for%20your%20help%20and%20excel%20example.%20Very%20kind%20of%20you.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22676px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3ETEMP%20data%20reference%20points%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3ETemperature%20Variable%20Load%20(kW)%26nbsp%3B%20%26nbsp%3Breference%20data%20points%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3EActual%20Time%20variable%20Temp%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3EActual%20Matched%20Load%20%3F%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E-3%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23180.1%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E15%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E-2%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23215.0%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E14%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E-1%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23256.4%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E14%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23311.9%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E15%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23381.7%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E18%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23479.9%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E21%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23615.5%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E23%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23603.4%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E25%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23627.4%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E27%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23691.4%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E28%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23739.9%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E28%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23817.0%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E28%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23848.1%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E28%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23885.3%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E28%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E11%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23930.9%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E12%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E23998.0%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E25%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E13%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E24061.4%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E22%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E14%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E24133.4%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E21%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E15%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E24214.0%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E20%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E16%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E24303.5%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E19%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E17%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E24401.6%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E18%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E18%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E24500.2%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E17%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E19%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E24660.1%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E17%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E20%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E24748.4%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E16%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E21%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E24956.2%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E15%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E22%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E25172.2%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E15%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E23%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E25435.7%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E14%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E24%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E25720.3%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E-3%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E25%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E26042.8%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E18%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E26%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E26413.1%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E21%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E27%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E26825.1%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E21%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E28%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E27018.3%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E21%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E29%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E27135.9%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E30%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E27258.9%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E21%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E31%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E27386.1%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E21%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E32%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E27518.6%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E-2%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2258.9896px%22%3E33%3C%2FTD%3E%3CTD%20width%3D%22186.771px%22%3E27657.5%3C%2FTD%3E%3CTD%20width%3D%22222.583px%22%3E21%3C%2FTD%3E%3CTD%20width%3D%22206.656px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3122330%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20data%20in%20one%20column%20based%20on%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298217%22%20target%3D%22_blank%22%3E%40MNOP_%3C%2FA%3E%26nbsp%3BAssuming%20the%20reference%20temperature%20-3%20is%20in%20cell%20A2%2C%20try%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DVLOOKUP(C2%2C%24A%242%3A%24B%2438%2C2%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%2C%20of%20you%20are%20using%20Excel%20MS365%20or%202021%2C%20try%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DXLOOKUP(C2%3AC38%2CA2%3AA38%2CB2%3AB38)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor
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

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

 

@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 ? 
-323180.115 
-223215.014 
-123256.414 
023311.915 
123381.718 
223479.921 
323615.523 
423603.425 
523627.427 
623691.428 
723739.928 
823817.028 
923848.128 
1023885.328 
1123930.90 
1223998.025 
1324061.422 
1424133.421 
1524214.020 
1624303.519 
1724401.618 
1824500.217 
1924660.117 
2024748.416 
2124956.215 
2225172.215 
2325435.714 
2425720.3-3 
2526042.818 
2626413.121 
2726825.121 
2827018.321 
2927135.910 
3027258.921 
3127386.121 
3227518.6-2 
3327657.521 
best response confirmed by MNOP_ (New Contributor)
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)

@MNOP_ 

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

 

An alternative could be above formula.

Thank you! This works like a dream
Thanks Quadruple. Will test it as well.