How to get error text from Google Spreadsheets?

Question

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. — doubleunary, Commented Aug 17, 2021 at 14:55
@doubleunary, I've updated the question and added the sample spreadsheet there. — LA_, Commented Aug 17, 2021 at 18:22

doubleunary · Accepted Answer · 2021-08-17 19:14:16Z

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; " ") ) ) )

answered Aug 17, 2021 at 14:55

doubleunary

15.7k9 gold badges23 silver badges59 bronze badges

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

Add a comment |

1 Answer 1