Following Spreadsheet Tips and Tools for Finance Professionals Lookup Part of Text: Randomly Placed Text in Cell which is returning N/A when it doesn’t find a match.
It’s an array formula:
For Not Found the function - result is: INDEX - #N/A, MATCH - #N/A, ISERROR - True, SEARCH - #VALUE!
How is a default set to return for no match?
lookup-part-of-text-excel.xlsx - array formula will not drag down
What do you want in your transaction list if there is no match in the price lookup table?
Note that this kind of “freetext lookup” carries a great potential for errors (false positives). Avoid such constructs for invoicing, budgeting, … come to think of it, avoid it for everything, if you can.
You’ve just been awarded the gold Cynical Bastard badge. 
You seem to have overlooked the link is a tutorial. Don’t worry, if it’s right it saves a heap of time and every record will be verified visually anyway.
The empty string at the bottom of the list didn’t work, dunno why. Hopefully, someone can see how to fix the formula.
You’ve just been awarded the gold Cynical Bastard badge.
Didn’t see that badge coming.
You seem to have overlooked the link is a tutorial.
Nope. Skimmed through the entire piece and got the gist of it. Didn’t test that the formulas created for Excel actually work for Calc, but I guess that is your job.
The main objective of my comment above was to find out what your question really is:
How is a default set to return for no match?
It is not obvious to me what you are asking. Might have something to do with English not being my first language.
The closing “Note” is just a friendly alert, no derogatory intention from me. You are still free to assume it as such, in which case I am yours truly, the Cynical Bastard.
It may still be useful for you to respond to my opening question. Help us to help you! Better yet: attach a file with sample data to your question. That relieves us from creating a (possibly flawed) dataset to try and devise a (most likely flawed) solution.
Sorry, I read the last paragraph of your original reply as humour.
My actual use case is a best-guess (textual) classification of bank transactions. Twenty keywords can classify about half the transactions.
Using the example given above, the intention is to find the price for most and if not found display (say) “unlisted”. Some classification errors are expected which would be corrected manually.
Should be {=IFERROR(INDEX($I$31:$I$34, MATCH(FALSE, ISERROR(SEARCH($H$31:$H$34, $D31)), 0)),"Unlisted")}. I added a file to question but it is returning Err:508 when I drag array formula down.
Sorry, I read the last paragraph of your original reply as humour.
Don’t be sorry! There is an element of humor, kind of a caricature statement, but there is a serious thought behind it. Your use case is sensible, and I can see why you felt that I was ridiculing your work. I apologize!
Now with more detail and a sample file I will look into it, unless someone else beats me to it, which is likely since I am posting from phone right now, and will be away from the computer for the next 4 hours or so.
One more question:
I see that your file is saved in Excel format. Can you confirm that you are using LibreOffice Calc, and can you confirm that the formula works as entered? (Oops, that was two questions right there instead of one )
Yes I use LO Calc with excel filetypes. The formula in the last comment works on a single cell but will not drag.
I don’t understand your original question, but I get Err:508 when pulling the formula down only in LibreOffice 6.2 but not in 6.3.6 or 6.4.3. However, pulling such array formula down does not do what you might expect, it just repeats the first element of the array, which is the standard behaviour of a single element array. Instead, copy the formula cell to clipboard and paste it to the target location(s), the $D31 will be adjusted to $D32 then and so on.
It should work as expected. It is an array function but the match returns a single value. Dragging it down will update search text. Array formula not working
Yes it does return a single value. Array functions can return single values and only use arrays in their inner workings. What do you expect? Even the tutorial says “We can copy this down and we have results for all our lookups”.
Firstly, agree entering formula into each cell gives correct result as you say. I expect dragging formula down should be the same as entering it in each cell. I know it has worked because I have a sheet with thousands of lines and I couldn’t have entered them manually.
Again: copy the formula cell (the entire cell, not just the formula) to clipboard, then select the target cell range, then paste. One operation on an arbitrary amount of cells. I doubt it ever worked by pulling down the formula cell, because as you said it results in Err:508 in earlier LibreOffice versions and in later versions repeats the 1x1 array element.
Agree, thank you. Opened up the original template, readme says drag (tested - it’s wrong). Process you describe works. Can you point me to the docs for this? What is best way to close this question?
As the tutorial/template was made for Excel, pulling/dragging such 1x1 array formula might work differently in Excel, I don’t know.
Trap the error and display alternate text as shown:
{=IFERROR(INDEX($I$31:$I$34, MATCH(FALSE, ISERROR(SEARCH($H$31:$H$34, $D31)), 0)),"Unlisted")}
Note: This array formula must be typed without curly braces and entered with Ctrl-Shift-Enter.
Thanks to @erAck for comment on how to copy to range:
