Combining Tables in Numbers '09

Question

Level 1

0 points

Combining Tables in Numbers '09

I'm looking to make a formula that multiplies column U of Table 1 with a value from Table 2 at column C whose row is determined by using column P of my row in Table 1 and finding the row in Table 2 that contains that value in column A.

So basically, I have a Table 1 with sales data (actually; iTunes reports). They contain a number (Table1::Column U) that represents how much a customer payed in their currency (Table1::Column P). I have another table that maps currencies (Table2::Column A) to a value that is EUR/[That currency] (Table2::Column C). So all I want to do now is add a column to Table 1 that calculates the EUR value for the customer's cost.

Any assistance is appreciated; any detailed explanation so that I may learn, even more so.

MacBook Pro, Mac OS X (10.6.3)

Posted on Apr 26, 2010 2:47 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Jan 5, 2011 12:50 PM in response to lhunath

lhunath wrote:
I'm looking to make a formula that multiplies column U of Table 1 with a value from Table 2 at column C whose row is determined by using column P of my row in Table 1 and finding the row in Table 2 that contains that value in column A.

So basically, I have a Table 1 with sales data (actually; iTunes reports). They contain a number (Table1::Column U) that represents how much a customer payed in their currency (Table1::Column P). I have another table that maps currencies (Table2::Column A) to a value that is EUR/[That currency] (Table2::Column C). So all I want to do now is add a column to Table 1 that calculates the EUR value for the customer's cost.

If I understand your description correctly, A LOOKUP table and the VLOOKUP function should provide a solution.

In Table 1 (on the left), I've used column B for the amount paid, C for the currency in which payment was made, and D to calculate and display the equivalent amount in Euros.

The LOOKUP Table is on Table 2. Currencies are listed in column B and the amount of that currency equivalent to one Euro in column C.

You can find an explanation of VLOOKUP in the iWork Formulas and Functions User Guide, which you can download through the Help menu in Numbers.

Regards,
Barry

Reply

Answer 2

KOENIG Yvan

Level 8

41,807 points

Jan 5, 2011 12:50 PM in response to lhunath

Do you know that resources delivered by Apple contain valuable informations ?
Every user may download, for FREE, *_Numbers User Guide_* and *_iWork Formulas and Functions User Guide_*

In this one we may learn which functions are available.

One of them is VLOOKUP.

In standard cells of column W, I inserted the simple formula :
=IFERROR(U*VLOOKUP(P,Table 2 :: $A:$C,3,FALSE),"")

details in *_iWork Formulas and Functions User Guide_*

Yvan KOENIG (VALLAURIS, France) mardi 27 avril 2010 10:33:34

Reply

Answer 3

lhunath Author

Level 1

0 points

Jan 5, 2011 12:51 PM in response to lhunath

Wonderful! Excellent answers and great resources; I will be digging through these. VLOOKUP does indeed look like exactly what I need.

Cheers.

Reply

Answer 4

KOENIG Yvan

Level 8

41,807 points

Jan 5, 2011 12:51 PM in response to lhunath

Oops

I must apologize.
I didn't saw Barry's post when I posted mine. 😟

Yvan KOENIG (VALLAURIS, France) mardi 27 avril 2010 17:15:22

Reply