Ask Your Question

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

asked 2020-04-27 05:49:51 +0100

flywire gravatar image

updated 2020-04-28 05:04:02 +0100

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?

image description

C:\fakepath\lookup-part-of-text-excel.xlsx - array formula will not drag down

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2020-04-28 20:39:16.383212


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.

keme gravatar imagekeme ( 2020-04-27 08:12:26 +0100 )edit

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.

flywire gravatar imageflywire ( 2020-04-27 12:03:44 +0100 )edit

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

keme gravatar imagekeme ( 2020-04-27 12:22:30 +0100 )edit

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.

flywire gravatar imageflywire ( 2020-04-27 15:30:59 +0100 )edit

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 :-) )

keme gravatar imagekeme ( 2020-04-27 19:03:54 +0100 )edit

Yes I use LO Calc with excel filetypes. The formula in the last comment works on a single cell but will not drag.

flywire gravatar imageflywire ( 2020-04-27 23:33:26 +0100 )edit

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.

erAck gravatar imageerAck ( 2020-04-28 02:51:43 +0100 )edit

It should work as expected. It is an array function but the match returns a single value. Dragging it down will update search text.

flywire gravatar imageflywire ( 2020-04-28 03:06:16 +0100 )edit

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".

erAck gravatar imageerAck ( 2020-04-28 03:20:12 +0100 )edit

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.

flywire gravatar imageflywire ( 2020-04-28 03:30:28 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-04-28 05:02:59 +0100

flywire gravatar image

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:

  • copy the formula cell (the entire cell, not just the formula) to clipboard, then select the target cell range (maybe Shift+Ctrl+Down Arrow), then paste
edit flag offensive delete link more

Question Tools

1 follower


Asked: 2020-04-27 05:49:51 +0100

Seen: 185 times

Last updated: Apr 28 '20