Matching Two Tables in Excel

February 17, 2012 at 03:05:45
Specs: Windows XP
I have two tables containing similar information. What I'd like to is compare the text in the first column and if they match, subtract the data for the following 12 columns.

So basically table 1 is a list of companies (column A) by their total spend over a period of 12 months (Columns (B-M),

Table 2 is list of companies (column A) who have spent on product X over the last 12 months (Columns B-M). but it has all the companies who haven't spent on product X deleted.

I'd like to compare these two tables to create a third table which has a list of companies minus their spend on product X. If they have spent anything at all

I hope this is clear, please let me know if you need any more information

See More: Matching Two Tables in Excel

Report •

February 17, 2012 at 06:34:36
You didn't say where Table1 and Table2 were located, so I'll assume that Table1 is in A1:M50 of a sheet named Table1 and that Table2 starts in A1 of a sheet named Table2.

In Column N of both tables put a SUM formula so that you total up the last 12 months of spending.

In O1 of Table 2 place this formula and drag it down to the bottom of Table 2:

=VLOOKUP(A1, Table1!$A$1:$N$50, 14, 0) - N1

The VLOOKUP will find a match for Table2 Ax in Table1 and return the value in Table1 Nx (the SUM) and then subtract the value in Table2 Nx giving you the difference.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question