Numbers - Lookup function

Question

Level 1

15 points

Numbers - Lookup function

Hi,

I have a numbers document and would like to retrieve data from a table based on 2 criteria.

The usual function is =LOOKUP(A1 ,AddressBook :: Client Num,AddressBook :: Total Paid)

I would like the formula to match Client Num and match say a date range, and then add the totals all together.

Something like this: =LOOKUP(A1, Client) and LOOKUP(Date, is between 010108 and 013108) SUM(Clients totals)

Not sure if that makes sense but any help would be much appreciated.

MacBook Pro, Mac OS X (10.5.3)

Posted on Jul 2, 2008 3:32 AM

Reply

Answer 1

KOENIG Yvan

Level 8

41,825 points

Jul 2, 2008 4:52 AM in response to JSAQ

No particular problem whis your first formula
=LOOKUP(A1, Client)
which will return a client's number or nothing

The second formula is wrong.

The LOOKUP() function is described this way:

The LOOKUP function finds a match to your search value in one range, then retrieves the value in the cell with the same relative position in a second range.
LOOKUP(search-value, search-range, [result-range])
search-value: The text or numeric expression you want to search for in the search range.
search-range: The range of cells that you want to search for the search value.
result-range: Optional; If omitted, the result is returned from the last row or column of search-range, whichever is the longer dimension.

It's clear that what is searched is the cells whose contents matches a value not those whose contents matches a conition.

To do what you ask for, you must use an auxiliary column.

assuming that your dates are in column B
the formula
=AND(B>=DATE(2008,1,1),B<=DATE(2008,1,31))
will do the trick which would give you the ability to identify the range fitting your needs.

<img src="http://homepage.mac.com/koenigyvan/.Pictures/forum/2008 07/2008_0702b.jpg">

The formula in the cell D of the footer is :

=SUMIF(C,TRUE,D)

Yvan KOENIG (from FRANCE mercredi 2 juillet 2008 13:50:12)

I don't know why the screenshot is not displayed.

Reply

Answer 2

JSAQ Author

Level 1

15 points

Jul 2, 2008 7:15 AM in response to KOENIG Yvan

KOENIG Yvan thanks for the reply, but doesn't quite do what I want, I need it all to be in a single function.

Is that possible?

Essentially this is what I'm trying to do:

I would like to pull the relevant data/numbers for a client during a particular month and sum the numbers for the total income from that client in that month

i'll try formulate in word:

IF (client number is equal to invoice::client num) AND (invoice:: invoice date contains MONTH) SUM (invoice:: invoice totals)

Would that be workable?

This is what i have so far but no luck of course:
=IF(A2=Invoices_data :: CLIENT NUM)=AND(Invoices_data :: INVOICE DATE>=DATE(2008,1,1),Invoices_data :: INVOICE DATE<=DATE(2008,1,31))=SUM(Invoices_data :: INVOICE TOTAL)

Message was edited by: JSAQ

Reply

Answer 3

Jul 2, 2008 8:25 AM in response to JSAQ

JSAQ,

The thing that may not be clear in the manual is that SUMIF can only test on a one-Cell basis. You may include multiple conditions for one cell versus a literal, but you may not compare multiple cells within one SUMIF statement. This is why Yvan recommends that you add a column to do the multi-column logic. The added column may then be hidden from view if desired.

Regards,

Jerry

Reply