CODEWITHSUNDEEP
excelexcel-formula
user3191826
user3191826

Reputation: 1

Match and return value in new column

What is the best function to use if:

  1. I want to add 2013 sales (Sheet1) to an existing sheet (Sheet2) under column 2013, and
  2. If the customer does not exist in the 2013 sheet (Sheet2), to be able to insert it accordingly (from Sheet1).

Sheet1:

CUSTOMER    2013    
ABC         25      
BBB         10      
DDD         40      
XYZ         20      
YYY         30      
ZZZ         15

Sheet2:

CUSTOMER    2010    2011    2012    2013
AAA         1       2       3   
ABC         5       10      15  
BBB         4       5       6   
FFF         3       6       9   
XYZ         4       6       8   
ZZZ         2       4       6

Upvotes: 0

Views: 101

Answers (1)

barry houdini
barry houdini

Reputation: 46451

Try using SUMIF, e.g. if 2013 figures are in sheet called 2013 with Customers in column A and Sales in B then in summary sheet where customer is in A2 use this formula in 2013 column row 2 copied down

=SUMIF('2013'!A:A,A2,'2013'!B:B)

If the customer doesn't exist in 2013 sheet you get zero

The alternative is VLOOKUP with IFERROR, i.e.

=IFERROR(VLOOKUP(A2,'2013'!A:B,2,0),0)

See this example

Upvotes: 1

Related Questions