1

I have a list of error message, and I want to consolidate them to their "user friendly" message:

      error        | friendly_error

failed with error1 | =VLOOKUP(A1, error_table, 1, false)
failed with error2 |
something else error3 |
error 4 failed with error5 |
failed with error1 |

And a table with the friendly values based on it containing of some keyword

contains | friendly_error
error1   | Message for error1
error2   | Message for error 2
etc...

Is there a command that can do this? Or do I need a bunch of if/else comments in a less organized way?

Vlookup would lookup the smaller value in a larger value, but I want to lookup a larger value to see if it contains a smaller value.

Right now I'm doing this, but it grows as I add more possible values:

=IF(ISNUMBER(SEARCH(G3,A1)),
    H3,
    IF(ISNUMBER(SEARCH(G4,A1)),
        H4, 
        IF (ISNUMBER(SEARCH(G5,A1)),
            H5,
            A1
        )
    )
)
2
  • 2
    Okay. Is there a question?
    – BruceWayne
    Jan 22, 2019 at 18:46
  • 1
    Remove "failed with " before performing the VLOOKUP Jan 22, 2019 at 19:09

2 Answers 2

2

Assuming the error types are the actual Excel error types, you could use Error.Type():

=VLOOKUP(ERROR.TYPE(A2),$E$1:$F$9,2,FALSE)

Where A2 is the formula returning the error #N/A, #REF!, etc.

enter image description here

Edit: Or, if I completely misunderstood, just replace your VLOOKUP() with:

=VLOOKUP(SUBSTITUTE(A1,"failed with ",""), error_table, 1, false)

Assuming A1has failed with error1 in it.

1
  • Thanks - but unfortunately they're not excel error types, and I won't be able to substitute text since it can be located anywhere within the text.
    – d-_-b
    Jan 22, 2019 at 19:25
1

You can use this which iterates the Errors and tests if it is a substring of the errors in A. Then returns the row number to the INDEX, which returns the correct Friendly Error.

=IFERROR(INDEX(H:H,AGGREGATE(15,6,ROW($G$1:$G$3)/(ISNUMBER(SEARCH($G$1:$G$3,A2))),1)),"")

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .