Lookup problems

JK,

I looked at your Numbers document and I don't understand what isn't working. Could you be more specific?

Jerry

Still a bit unclear on your request.

You are searching multiple cells for a specified search term in your Numbers document, and it works. Only when the search term you specify isn't found in the search range you specify do you get an error. You can use IFERROR or ISERROR to handle the result differently.

If you are asking "how do I search for multiple search criteria at once in a single function", I don't know of any way.

Jerry

Your lookup in G5 is searching in References column C but "Ib" is in column B of that table. Similar problem with your formula in H5 which is searching in column D of References. Lookup will do a "close match" which may explain why the formula in H5 isn't giving you an error.

I'm not sure what it is you are trying to do. Typically, LOOKUP is used to find something in one column of a lookup table and return something from another column of the lookup table. Your formula is looking in the lookup table (References) but returning a value from Table 1.

JK,

A good place to start would be to clean up your table by getting rid of unused columns and rows.

Both of your tables could use the Header Row for the column titles. Those titles should be kept out of the body rows. Also, neither table needs or should have a Header Column, as far as I can tell. If you tidy this up, your formulas will be easier to write and to visually confirm.

To Badunit's point, your splitting the function across tables is unconventional, but is not the cause of your error. In fact, I thought it was a rather clever way to effect an IF case for the amount. The error, as Badunit mentions, is caused by the searched-for value not being in the range where you looked.

Jerry

The problem appears to be misuse of the LOOKUP function. It doesn't work when your lookup range is one size but the return-value range is a different size. I am guessing it is trying to find the second value in cell D5 to match Hans being the second value in the lookup table and, of course, there is no second value to be returned.

Alternative formula:
=IF(ISERROR(LOOKUP(B5,B20:B23)),"no match",D5)

The formula above may match to a "close match". To make it be exact matches only,
=IF(ISERROR(VLOOKUP(B5,B20:B23,1,0)),"no match",D5)

!http://i618.photobucket.com/albums/tt262/jpb45un2/a5bfa56a.png!

Jan,

This might be what you are looking for. Here are the formulas I used.

Found/Not found: =IF(ISBLANK(A), "", IF(COUNTIF(ClientLists :: A:C, A)>0, "Found", "Not Found"))

Client#1: =IF(COUNTIF(ClientLists :: A, $A)>0, $C, "")

Fill across, then Fill Down all four columns.

Footer row, Client1: =SUM(E)

Fill Across.

Is this close? (Sorry my amount column wasn't wide enough in the screen shot.)

Jerry

(I didn't look back to see the 2 previous posts, but I'll leave this as is.)

Message was edited by: Jerrold Green1

COUNTIF is a better function for this task. Shoulda thought of that myself.

I don't think that is possible in a simple formula. You can find "Kim" in the string "payed by Kim" but you can't do it the other way around. You will have to parse the SearchWords into individual words. "Payed by kim" will be parsed into "payed", "by", and "Kim". You would have to do a countif on each of those words then have a formula that looks at those results and returns the match. This sounds to me to be a lot of work.

Alternative to all that parsing and matching, you could require the SearchWords to start with (or end with) the customer's name. Three of your four examples have the client as the last word and the one that wasn't could have been that way. With that structure, you can easily get the client name from the string. Here is a post that shows how to get the last word of a sentence:

http://discussions.apple.com/thread.jspa?messageID=12486148&#12486148

Do that in in another column and use that column as the client name for the rest of your formulas.