Lookup problems

Hello,

I am trying to make a document that gets a search word from a cell, and then search multiple cells for that search word, and if any of the searchwords names appear in the cells, then returns the value of a specific cell.

You can see what i am trying to do here - http://www.anacropolis.dk/numbers/Cellcheck.numbers

My problem is currently that LOOKUP returns the value fine when i am only checking one cell, but fails when i am checking multiple cells.

Any tips/trick are most appreciated.

Thanks!

Regards...Jklarsen

Macbook Pro, Mac OS X (10.6.5), Numbers 2.0.4..

Posted on Dec 8, 2010 5:59 AM

19 replies

Badunit

Level 7

30,714 points

Dec 13, 2010 9:26 AM in response to Badunit

I should have mentioned that if the client's name is first word in the sentence, it is a simpler formula to parse it out.

jklarsen Author

Level 1

13 points

Dec 13, 2010 10:58 AM in response to Badunit

The reasons for this advanced request is that i am trying to sort my bank transactions in an easy way, to see what each client has payed, and the bank statements are always in different word order, so i can never be sure in which order the searchword will be in, therefore the need for a "word" in cell text search formula.
I have done this in excel, but i would rather use numbers, but i am probably missing some key functions.

My excel formula looks like this -
=IF(ISNUMBER(LOOKUP(9,99999999999999E+307;SEARCH(" "&C$4:C$90&" ";" "&$C2397&" ")));$D2397;"")

Translated to the current Numbers sheet,
$C2397 is my searchwords
$D2397 is the amount
&C$4:C$90 is the clientlist#1

The nice guys over at the excel support forum helped me with that one, and it works quite similar to what you guys have been helping me with, with the one difference that i think it is building an array/list of the words in the string, and then checking each word on the cell against the clientlist#1 cells.

Does this makes sense to you in a numbers kind of way?

Best...Jan

Badunit

Level 7

30,714 points

Dec 13, 2010 11:25 AM in response to jklarsen

That appears to be an Excel array function, and your description of it fits that definition. The formula appears to be doing a search of each client's name, trying to find it in the SearchWords string. It is a whole bunch of lookups all in one concise formula. Numbers doesn't have array functions. With Numbers you can only search the string for one client at a time. It would take 87 formulas to duplicate the Excel formula you posted (plus one more to consolidate the results into a single result).

jklarsen Author

Level 1

13 points

Dec 13, 2010 11:48 AM in response to Badunit

87 might be a bit over the top 😉

Anyway thank you very much for your efforts!

Best...Jan

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Lookup problems