Apr 21 2019 09:44 AM
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),"")
Apr 21 2019 10:25 AM
@excel-headache , you missed one quote here
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.