Forum Discussion
KAR FUNG LEE
Oct 29, 2025Copper Contributor
Formula in Excel Spreadsheet not work
Hi Sir, Picture 1 shows the desired condition, where the row of the spreadsheet turns orange when the SPT (N) is more than 50. However, it will not work where the SPT (N) at row 47...
Riny_van_Eekelen
Oct 29, 2025Platinum Contributor
The use of an approximate match in VLOOKUP requires the lookup range to be sorted in ascending order. Yours is not and the CF formula will never give the correct answer. And when you think it does, that's just a coincidence.
What is the logic for the conditional format? Can you describe it in words?
The value of 50 seems to your breaking point and you want to return the next lowest value. Correct? That would be 22, while your VLOOKUP returns 20. This is due to the fact that VLOOKUP performs a so-called binary search.