Is there any way to get an error text from the formula in Google Spreadsheets?

For ex., I see the following error -

В диапазоне функции VLOOKUP значение "zzz" не найдено.

(the value 'zzz' not found)

I would like to extract 'zzz' from the error text.

I can not refer to the cell text, since the formula here looks like

=JOIN(", ";ARRAYFORMULA(VLOOKUP(SPLIT(A1;" ");'mapping table'!A:B;2;FALSE)))

Upd. here is the sample table - https://docs.google.com/spreadsheets/d/1gsY68pmymEUzVWZm8z5G5zuShWzkBEt3Y1Wbsu0jtZs/edit?usp=sharing, the expected value is given in cell D1. I would like to get it from the error text.

  • It sounds like cell A4 contains search keys, and you want to return search results, or if there is no match, the search key itself. Is this an XY problem? Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Aug 17, 2021 at 14:55
  • @doubleunary, I've updated the question and added the sample spreadsheet there.
    – LA_
    Aug 17, 2021 at 18:22

1 Answer 1


Use iferror() to catch the value that causes an error, like this:

=arrayformula( textjoin(", "; true; iferror( left( vlookup( split(A1; " "); 'mapping table'!A1:B; 2; false ); 0 ); split(A1; " ") ) ) )

  • Thanks. In my sample table your formula returns value1, value2, zzz, value3, when I need to get just zzz.
    – LA_
    Aug 17, 2021 at 18:38
  • Edited the answer per your requirements. Aug 17, 2021 at 19:15
  • Wow! Many thanks!
    – LA_
    Aug 18, 2021 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.