Integrate error handling/value on error for functions such as VLOOKUP
While functions like IFERROR and ISERROR exist, it complicates things to have to wrap each function in an error checker.
We’re always looking for ways to make formulas smaller and easier – thanks for the suggestion! We’ll prioritize this according to the number of votes, so keep voting if you want to see this get done sooner!
John [MS XL]
Corey Becker commented
Personally, I think IFERROR is simple enough but the issue is that it hides all errors. So if the lookup returns no values it will return an error but what if the error is in the data itself? What if there is a #REF in your data? Your formula will return "not found" when in reality it is. Technically it is finding the result and returning the correct value, it just happens to be an error. So debugging becomes difficult with IFERROR. I think better error-handling in general should be a huge priority in the future.
I've tried building error-handling formulas before but that is a huge pain because you may want to ignore #DIV/0 errors and just make them 0 but really want to fix #REF errors. This is decent for debugging but takes some effort to configure for each scenario
You can then substitute your desired result for each type of error.
A better error checking interface that shows all errors that are currently "hidden" by iferror would be cool too. For example, within this interface you would be able to filter on all your #DIV/0 errors that you might not see because iferror is hiding them. I imagine this would be nearly impossible but who knows.
Kenneth Barber commented
I think that this is why they added the IFNA function.
My bone to pick with VLOOKUP and HLOOKUP is this. The ideal format for VLOOKUP is this: VLOOKUP("value",A:C,COLUMNS(A:C),FALSE). If column A should move to the right of column C, then even if you move it back, the references are broken, hence why people like me use INDEX(B:B,MATCH("value",A:A,0)).
VLOOKUP and HLOOKUP should also have an option to search in data sorted in descending order, much like what MATCH has.
It's very annoying to place a formula inside an IFERROR function if all your formulas are already written. Perhaps if there was a keyboard shortcut, you could press it and it sticks your formula in the 'value' section and then the cursor jumps to the 'value_if_error' argument. This would be much easier