Unable to resolve #N/A error

Functions attempted: VLOOKUP, XLOOKUP, INDEX and IFS

COL D looks up the value in the adjacent cell in COL C.
VLOOKUP in D enters value in array PtsPCT (N8-O48).

COL C and N8-N48 are formatted as Numbers
COL D and O8-O48 are formatted as Percentiles

All functions attempted resulted with a #N/A error. This is especially maddening as other VLOOKUP, XLOOKUP, INDEX and IFS functions I've used in other sheets have worked perfect today.

Your issue is that the lookup field needs to be in Ascending Order and not descending order as it is now.

vlookup formulas expect the search range to be sorted in ascending order

I akso adjusted your formula to this...
=ROUNDDOWN(N9-0.05,2)

edit: Alan, there is also a problem with the calc in the looklup table, I fixed that using rounddown

Your issue is a formatting issue, this happens a lot in excel when copying and pasting things around. I simply took your Eval Points and copied that into notepad, then copied it from there back into your Eval Points section and that solved it.
I also added IFERROR to both column D and H to prevent other errors you had happening (when there was no Eval Points value in the table)

(I forgot to use your named ranges, but you should be able to fix that)

See attached.

At the risk of muddying the water rather than clarifying, the main issue I see is floating point error. In your PtsPCT table, your left lookup value column is calculated by repeatedly subtracting a fraction (0.05). Repeatedly adding/subtracting a fraction is one of the long standing, classic demonstrations of floating point error (I have textbooks going back to the '70s that use this example). I'll put a link at the end to an old (1991) but still rather thorough treatment of the subject of floating point arithmetic at the end.

You can see the error by entering something like =(C9-N22) into a convenient cell and formatting as scientific. You will get a small, non-zero number (something like 1E-15), which shows that the two values are not exactly the same. Your VLOOKUP() function is calling for an exact match, and, when Excel searches for an exact match, it is searching for something exact to the last bit. A difference in the least significant bit is enough for Excel to declare "no match" and return N/A.

Solutions to floating point error vary. In spreadsheets, most solutions involve rounding functions like FDibbins used. Passing the data through a text editor (or other text representation) like NewYears1978 did is another strategy. Whatever strategy you choose to use, you as the programmer need to be aware of floating point errors and program your sheet to handle it.

Are you required to use a lookup function for this? I notice that your change in awd% and change in eval points is constant. It would be real easy to reduce the lookup table to a simple algebra expression (refresh your memory from your early algebra classes on point-slope form for straight line equations: https://www.purplemath.com/modules/strtlneq2.htm ). For this specific problem, an algebraic expression like that will be much less susceptible to floating point errors. Would that be allowed?


More than you want to know about floating point errors: https://docs.oracle.com/cd/E19957-01..._goldberg.html

Then edit the post!

There is no time limit as far as I am aware, unless something has changed.