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

Copper Contributor

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.