Home

using the Hlookup but get problem. the warming window jumped up

%3CLINGO-SUB%20id%3D%22lingo-sub-478390%22%20slang%3D%22en-US%22%3Eusing%20the%20Hlookup%20but%20get%20problem.%20the%20warming%20window%20jumped%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478390%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20am%20using%20the%20Hlookup%20formula%3A%20it's%20on%20below%2C%20don't%20know%20where%20has%20the%20problem%20as%20i%20am%20not%20the%20first%20time%20to%20use%20it.%20and%20those%20are%20no%20problems.%20why%20this%20has%20problem%3F%20the%20warming%20windows%20shows%20this%20formula%20has%20problem.%20Please%20kindly%20help%3C%2FP%3E%3CP%3E%3DIFERROR(HLOOKUP(O4%2C%7B%220%22%2C%220.11%22%2C%220.13%22%2C%220.14%22%2C%220.15%22%2C%220.17%22%2C%220.18%22%2C%220.19%22%2C%220.2%22%2C%220.21%22%2C%220.22%22%2C%220.23%22%2C%220.24%22%2C%220.25%22%2C%220.27%22%2C%220.28%22%2C%220.29%22%2C%220.3%22%2C%220.31%22%2C%220.32%22%2C%220.33%22%2C%220.35%22%2C%220.36%22%2C%220.37%22%2C%220.38%22%2C%220.39%22%2C%220.4%22%2C%220.41%22%2C%220.42%22%2C%220.43%22%2C%220.44%22%2C%220.45%22%2C%220.46%22%2C%220.47%22%2C%220.48%22%2C%220.49%22%2C%220.5%22%2C%220.51%22%2C%220.52%22%2C%220.53%22%2C%220.54%22%2C%220.55%22%2C%220.56%22%2C%220.57%22%2C%220.58%22%2C%220.59%22%2C%220.6%22%2C%220.61%22%2C%220.62%22%2C%220.63%22%2C%220.64%22%2C%220.65%22%2C%220.66%22%2C%220.67%22%2C%220.68%22%2C%220.69%22%2C%220.7%22%2C%220.71%22%2C%220.72%22%2C%220.73%22%2C%220.74%22%2C%220.75%22%2C%220.76%22%2C%220.77%22%2C%220.78%22%2C%220.79%22%2C%220.8%22%2C%220.81%22%2C%220.82%22%2C%220.83%22%2C%220.84%22%2C%220.85%22%2C%220.86%22%2C%220.87%22%2C%220.88%22%2C%220.89%22%2C%220.9%22%2C%220.91%22%2C%220.92%22%2C%220.93%22%2C%220.94%22%2C%220.95%22%2C%220.96%22%2C%220.97%22%2C%221%22%2C%221.03%22%2C%221.04%22%2C%221.05%22%2C%221.06%22%2C%221.07%22%2C%221.08%22%2C%221.09%22%2C%221.1%22%2C%221.11%22%2C%221.12%22%2C%221.13%22%2C%221.14%22%2C%221.15%22%2C%221.16%22%2C%221.17%22%2C%221.18%22%2C%221.19%22%2C%221.2%22%2C%221.21%22%2C%221.22%22%2C%221.23%22%2C%221.24%22%2C%221.25%22%2C%221.26%22%2C%221.27%22%2C%221.28%22%2C%221.29%22%2C%221.3%22%2C%221.31%22%2C%221.32%22%2C%221.33%22%2C%221.35%22%2C%221.36%22%2C%221.37%22%2C%221.38%22%2C%221.39%22%2C%221.4%22%2C%221.41%22%2C%221.42%22%2C%221.43%22%2C%221.44%22%2C%221.45%22%2C%221.46%22%2C%221.47%22%2C%221.48%22%2C%221.5%22%2C%221.52%22%2C%221.53%22%2C%221.54%22%2C%221.55%22%2C%221.56%22%2C%221.57%22%2C%221.58%22%2C%221.59%22%2C%221.6%22%2C%221.61%22%2C%221.62%22%2C%221.63%22%2C%221.64%22%2C%221.65%22%2C%221.67%22%2C%221.68%22%2C%221.69%22%2C%221.7%22%2C%221.71%22%2C%221.72%22%2C%221.73%22%2C%221.74%22%2C%221.75%22%2C%221.76%22%2C%221.77%22%2C%221.78%22%2C%221.79%22%2C%221.8%22%2C%221.81%22%2C%221.82%22%2C%221.83%22%2C%221.84%22%2C%221.85%22%2C%221.86%22%2C%221.87%22%2C%221.88%22%2C%221.89%22%2C%221.9%22%2C%221.91%22%2C%221.92%22%2C%221.93%22%2C%221.94%22%2C%221.95%22%2C%222%22%2C%222.05%22%2C%222.06%22%2C%222.07%22%2C%222.08%22%2C%222.1%22%2C%222.11%22%2C%222.13%22%2C%222.14%22%2C%222.15%22%2C%222.16%22%2C%222.17%22%2C%222.19%22%2C%222.2%22%2C%222.22%22%2C%222.25%22%2C%222.29%22%2C%222.33%22%2C%222.38%22%2C%222.4%22%2C%222.43%22%2C%222.44%22%2C%222.5%22%2C%222.57%22%2C%222.6%22%2C%222.63%22%2C%222.67%22%2C%222.71%22%2C%222.75%22%2C%222.8%22%2C%222.83%22%2C%223%22%2C%223.2%22%2C%223.25%22%2C%223.33%22%2C%223.5%22%2C%223.67%22%2C%224%22%2C%224.5%22%2C%225%22%2C%226%22%2C%227%22%2C%228%22%2C%229%22%2C%2210%22%3B%22332%22%2C%222%22%2C%223%22%2C%224%22%2C%221%22%2C%228%22%2C%224%22%2C%221%22%2C%2219%22%2C%222%22%2C%224%22%2C%222%22%2C%221%22%2C%2238%22%2C%229%22%2C%221%22%2C%2213%22%2C%226%22%2C%225%22%2C%223%22%2C%2290%22%2C%2210%22%2C%2213%22%2C%223%22%2C%2232%22%2C%2213%22%2C%2245%22%2C%2213%22%2C%2221%22%2C%2232%22%2C%2234%22%2C%2223%22%2C%2217%22%2C%2221%22%2C%2222%22%2C%222%22%2C%22229%22%2C%228%22%2C%2227%22%2C%2234%22%2C%2230%22%2C%2240%22%2C%2261%22%2C%2258%22%2C%2259%22%2C%2235%22%2C%2289%22%2C%2234%22%2C%2253%22%2C%2283%22%2C%2265%22%2C%2251%22%2C%2221%22%2C%22230%22%2C%2257%22%2C%2277%22%2C%2283%22%2C%22128%22%2C%2241%22%2C%2284%22%2C%2255%22%2C%22170%22%2C%2280%22%2C%2276%22%2C%2290%22%2C%2291%22%2C%22132%22%2C%2282%22%2C%2287%22%2C%22135%22%2C%2252%22%2C%2263%22%2C%22117%22%2C%2262%22%2C%22127%22%2C%2299%22%2C%2287%22%2C%2269%22%2C%2285%22%2C%22113%22%2C%2279%22%2C%2249%22%2C%2299%22%2C%2256%22%2C%22823%22%2C%2233%22%2C%22121%22%2C%2278%22%2C%2250%22%2C%2283%22%2C%22111%22%2C%2269%22%2C%2281%22%2C%2292%22%2C%2251%22%2C%2299%22%2C%22112%22%2C%2276%22%2C%2233%22%2C%22128%22%2C%2276%22%2C%2263%22%2C%22107%22%2C%2258%22%2C%2275%22%2C%2247%22%2C%2255%22%2C%22154%22%2C%2237%22%2C%2273%22%2C%2236%22%2C%2285%22%2C%2261%22%2C%2248%22%2C%2240%22%2C%22196%22%2C%2245%22%2C%2252%22%2C%2214%22%2C%2281%22%2C%2224%22%2C%2294%22%2C%2222%22%2C%2251%22%2C%2267%22%2C%2257%22%2C%2234%22%2C%2224%22%2C%2240%22%2C%2219%22%2C%22241%22%2C%2216%22%2C%2226%22%2C%2223%22%2C%2232%22%2C%2249%22%2C%2244%22%2C%2234%22%2C%2213%22%2C%2253%22%2C%2210%22%2C%2218%22%2C%2246%22%2C%2228%22%2C%2212%22%2C%22108%22%2C%228%22%2C%2222%22%2C%2220%22%2C%2240%22%2C%223%22%2C%2220%22%2C%224%22%2C%2260%22%2C%224%22%2C%226%22%2C%2217%22%2C%226%22%2C%2237%22%2C%222%22%2C%227%22%2C%2228%22%2C%222%22%2C%227%22%2C%2220%22%2C%222%22%2C%2211%22%2C%2217%22%2C%229%22%2C%224%22%2C%226%22%2C%224%22%2C%227%22%2C%226%22%2C%22230%22%2C%223%22%2C%226%22%2C%224%22%2C%222%22%2C%222%22%2C%224%22%2C7%22%2C%228%22%2C%222%22%2C%221%22%2C%2210%22%2C%221%22%2C%2221%22%2C%222%22%2C%2225%22%2C%224%22%2C%2236%22%2C%223%22%2C%2213%22%2C%224%22%2C%222%22%2C%2253%22%2C%223%22%2C%227%22%2C%221%22%2C%2221%22%2C%222%22%2C%2216%22%2C%226%22%2C%222%22%2C%2287%22%2C%223%22%2C%222%22%2C%2210%22%2C%2215%22%2C%222%22%2C%2234%22%2C%223%22%2C%2221%22%2C%2210%22%2C%223%22%2C%222%22%2C%221%22%2C%221%22%7D%2C2%2C0)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-478390%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478431%22%20slang%3D%22en-US%22%3ERe%3A%20using%20the%20Hlookup%20but%20get%20problem.%20the%20warming%20window%20jumped%20up%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324619%22%20target%3D%22_blank%22%3E%40excel-headache%3C%2FA%3E%26nbsp%3B%2C%20you%20missed%20one%20quote%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20654px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109753iC9F3AAB39979AAF8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWorking%20with%20such%20constants%20hardcoded%20into%20the%20formula%20is%20always%20source%20of%20errors.%20Much%20better%20to%20use%20ranges%20instead%20of%20constants%20(and%20in%20this%20case%20numbers%20instead%20of%20texts).%20You%20test%20your%20formula%20ones%20and%20after%20that%20it%20works%2C%20you%20have%20quite%20low%20chances%20to%20put%20wrong%20data%20in%20to%20the%20cell%20which%20crashes%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
excel-headache
Occasional Visitor

i am using the Hlookup formula: it's on below, don't know where has the problem as i am not the first time to use it. and those are no problems. why this has problem? the warming windows shows this formula has problem. Please kindly help

=IFERROR(HLOOKUP(O4,{"0","0.11","0.13","0.14","0.15","0.17","0.18","0.19","0.2","0.21","0.22","0.23","0.24","0.25","0.27","0.28","0.29","0.3","0.31","0.32","0.33","0.35","0.36","0.37","0.38","0.39","0.4","0.41","0.42","0.43","0.44","0.45","0.46","0.47","0.48","0.49","0.5","0.51","0.52","0.53","0.54","0.55","0.56","0.57","0.58","0.59","0.6","0.61","0.62","0.63","0.64","0.65","0.66","0.67","0.68","0.69","0.7","0.71","0.72","0.73","0.74","0.75","0.76","0.77","0.78","0.79","0.8","0.81","0.82","0.83","0.84","0.85","0.86","0.87","0.88","0.89","0.9","0.91","0.92","0.93","0.94","0.95","0.96","0.97","1","1.03","1.04","1.05","1.06","1.07","1.08","1.09","1.1","1.11","1.12","1.13","1.14","1.15","1.16","1.17","1.18","1.19","1.2","1.21","1.22","1.23","1.24","1.25","1.26","1.27","1.28","1.29","1.3","1.31","1.32","1.33","1.35","1.36","1.37","1.38","1.39","1.4","1.41","1.42","1.43","1.44","1.45","1.46","1.47","1.48","1.5","1.52","1.53","1.54","1.55","1.56","1.57","1.58","1.59","1.6","1.61","1.62","1.63","1.64","1.65","1.67","1.68","1.69","1.7","1.71","1.72","1.73","1.74","1.75","1.76","1.77","1.78","1.79","1.8","1.81","1.82","1.83","1.84","1.85","1.86","1.87","1.88","1.89","1.9","1.91","1.92","1.93","1.94","1.95","2","2.05","2.06","2.07","2.08","2.1","2.11","2.13","2.14","2.15","2.16","2.17","2.19","2.2","2.22","2.25","2.29","2.33","2.38","2.4","2.43","2.44","2.5","2.57","2.6","2.63","2.67","2.71","2.75","2.8","2.83","3","3.2","3.25","3.33","3.5","3.67","4","4.5","5","6","7","8","9","10";"332","2","3","4","1","8","4","1","19","2","4","2","1","38","9","1","13","6","5","3","90","10","13","3","32","13","45","13","21","32","34","23","17","21","22","2","229","8","27","34","30","40","61","58","59","35","89","34","53","83","65","51","21","230","57","77","83","128","41","84","55","170","80","76","90","91","132","82","87","135","52","63","117","62","127","99","87","69","85","113","79","49","99","56","823","33","121","78","50","83","111","69","81","92","51","99","112","76","33","128","76","63","107","58","75","47","55","154","37","73","36","85","61","48","40","196","45","52","14","81","24","94","22","51","67","57","34","24","40","19","241","16","26","23","32","49","44","34","13","53","10","18","46","28","12","108","8","22","20","40","3","20","4","60","4","6","17","6","37","2","7","28","2","7","20","2","11","17","9","4","6","4","7","6","230","3","6","4","2","2","4",7","8","2","1","10","1","21","2","25","4","36","3","13","4","2","53","3","7","1","21","2","16","6","2","87","3","2","10","15","2","34","3","21","10","3","2","1","1"},2,0),"")

 

 

 

1 Reply

@excel-headache , you missed one quote here

image.png

Working with such constants hardcoded into the formula is always source of errors. Much better to use ranges instead of constants (and in this case numbers instead of texts). You test your formula ones and after that it works, you have quite low chances to put wrong data in to the cell which crashes the formula.