0

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.

2
  • 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 '21 at 14:55
  • @doubleunary, I've updated the question and added the sample spreadsheet there.
    – LA_
    Aug 17 '21 at 18:22
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; " ") ) ) )

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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