Forum Discussion
Match formula with alternate simple math calculation
Tables are of the greatest value when there exists the possibility of adding data to them. The structured references they provide are dynamic so there is no need to work through the solution adjusting downstream formulas. Since a defined name is simply a name given to a formula, it is possible to create names to refer to the structured references if that shortens the formula or makes it more readable.
The use of names for some formulas does not impact the other, more traditional, formulas using direct referencing to single cells (as relative references). If the workbook were mine, though, I would get rid of every last direct reference, but most other contributors to this forum are happy to retain the traditional approach.
That said, I have used a number of techniques for the lookup that you may wish to consider. Firstly there is the use of CHOOSE to select the appropriate Table. This is narrowed down by using XLOOKUP to return a column from the table (though there are instances where it is better to lookup the row first and return an entire record),. An advantage of the second lookup is that there is no need to wrap the function with IFERROR because it has a built-in error handling capability.
Good luck.