LET function should better return #NAME? whenever undefined variable was used inside the formula
If you write the formula
The current insider version of the Excel in my computer outputs "not found" even if the value in the A1 actually exists in the range B1:B5.
This is because of the typo "targt".
I would like Excel to warn me that I made a typo.
One idea is to make whole formula return #NAME?, whenever the undefined name is used somewhere inside the formula.
Another idea is to highlight the undefined names when editing the formula.
This issue is not only about the LET function, but the LET function make use of names so I became aware of this problem.
Peter Bartholomew commented
1. A name should be less prone to 'fat finger trouble' than a direct cell reference because IntelliSense should offer the correct name once it is partially typed.
2. In order to see the #NAME? error then the IFERROR() function needs to be removed.
3. Switching to XLOOKUP will show #NAME? for a name error and any other user-defined string for a failed match (includes #N/A).
Manuel Rosendo Castro Iglesias commented
Dear aoki_taichi, we cannot blame any program for our own mistakes, nor pretend that they can correct them for us.
If when we create a formula the result is incorrect, the first thing to consider is the possibility of having made a mistake.
The purpose of the LET () function is precisely that, to be able to see in a transparent way what we write or say a formula, and detect these inconsistencies.
So my suggestion would help, because declaring the "name" of the calculation gives us a much clearer view.