updating data in one table from data in another in Numbers

Question

updating data in one table from data in another in Numbers

Hi,

I know enough about Numbers to know that I really don't know enough about Numbers. Which is why I'm here for help.

I have a table that records various sales data: order number, sales tax, coupons used, etc. The information in it will be used over the course of 10 months so the table gets long and is updated frequently. The updated data is pulled from a CSV file download. The table also contains information that is hand entered and a couple columns with simple formulas.

What I want to do:

Using the 'Sales 2002" table and the Order # as the unique identifier, I'd like be able to update the data in that table (across multiple columns) with data in the downloaded file, call it "Sales Summary".

I think a VLOOKUP is the way to go, but I get lost in some of the specifics.

Here is 'Sales 2002":

And here is "Sales Summary"

Columns to update on Sales 2002 are B, E, F, G, H, I. The data to update is not in the same column in Sales Summary (e.g. E gets its data from C) -- is that a problem?

Currently the two tables are on the same tab in a Numbers file, although I'd like them on separate tabs if doable.

Thank you for any help or pointers.

MacBook Pro 15″, macOS 10.14

Posted on Jun 17, 2022 4:08 PM

Reply

Answer 1

Best reply

Ralf-F

Level 4

2,615 points

Jun 17, 2022 11:44 PM in response to jana_erickson

Hi Jana,

it is no issue to have the two table in different sheets / tabs, this will make it clean because you see only the data that you currently work on.

Here my example, I have only dummy values you would have to add yours.

Sheet Sales-Summary has on table Data that contains all your base data.

Sheet Sales 2002 has one table

I would use XLOOKUP, because it is easier to use.

Please read the help that Numbers will show you as soon as you use this function.

Formula for cell B2: IF($A2="","",XLOOKUP($A2,'Sales-Summary'::Data::$A,'Sales-Summary'::Data::B,"??",0,1))

XLOOKUP will search for the value in cell A2 search will be done in your data table on the second sheet in column A

If the value is found it will return the value from column B of the same row.

The IF function is needed to prevent that the system will search / display an empty order number.

For these sections you should select preserve column, $ sign will be added

Formula for cell E2: IF($A2="","",XLOOKUP($A2,'Sales-Summary'::Data::$A,'Sales-Summary'::Data::C,"??",0,1))

Now pick the litte dot and drag the formula to the rest of your cells

Now just drag all formulas down to the end of your table

As soon as you enter a new order number the values will be shown.

If you enter an order number that is not in your data list you will see ??

Based on your region the , or the ; will be used to separate the different sections of a formula.

Hope this will solve your question, please let me know if something in unclear.

Ralf

Reply

Answer 2

Jun 18, 2022 7:05 AM in response to Ralf-F

Wow, Ralf this is sooooo helpful!

Your walkthrough is so clear. I can't thank you enough for the help and the detail. I'll be working on it later this weekend and will let you know how it goes.

Thank you again!

Jana

Reply

Answer 3

Jun 19, 2022 12:56 PM in response to jana_erickson

Hi Ralf,

Just wanted to follow up and let you know the XLOOKUP works fabulously! I'm thrilled to have learned it and am already planning more ways it can make my life easier.

Thanks again!

Reply