# Lookup Part of Text: Randomly Placed Text in Cell [closed]

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:

- {=INDEX($I$31:$I$34, MATCH(FALSE, ISERROR(SEARCH($H$31:$H$34, $D31)), 0))}

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?

C:\fakepath\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.

Didn't see that badge coming.

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:

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 ...(more)

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.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. https://ask.libreoffice.org/en/questi...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

hasworked because I have a sheet with thousands of lines and I couldn't have entered them manually.