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

edit

What do you want in your transaction list if there is no match in the price lookup table?

• If there should always be a match, returning an error (as it seems to do now) is the sensible thing.
• If you can accept rows without matching words, you first have to decide what to return. Do you want number zero returned, a text value, or something else?
  • A row with an empty string for "Item" in the lookup table should match anything. Try it! Put the return value (zero, some alert text, or whatever value you need returned)

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.

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. 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 has worked because I have a sheet with thousands of lines and I couldn't have entered them manually.