CODEWITHSUNDEEP
excelformulas
redlt1
redlt1

Reputation: 11

Formula to match column value with another column and add rows

I'm trying to match a value in one column to another, then add the two values which are in different rows. Given:

     A      B     C      D

1  Mustang  300  Camaro  70
2  Corvette 700  F150    82
3  Charger  218  Impala  74
4  Firebird 200  Mustang 66

There is a match, A1 = C4. So, add values B1 and D4, in a new column E.

Here's what I've tried:

=(Index(A1:D4,Match(A1,C:C,0),D) + B1)

The match (if true) should return the row in C. Then I tried INDEX to use the matched row with the column I want to add to B1. I tried this for Row 1 and it returned nothing, which was correct. When I dragged it down the column though, it did not work with any.

Upvotes: 0

Views: 3968

Answers (2)

Tomamais
Tomamais

Reputation: 95

First, you have to lock the range to save your range criteria with "$":

e.g.: =(Index($A$1:$D$4,Match(A1,C:C,0),D) + B1)

Maybe you are looking for VLOOKUP formula. It returns a value the you are looking for, #NA otherwise. Maybe something like (at E1 cell):

=VLOOKUP(C1,$B$1:$B$4,1,FALSE)

The fact is, neither VLOOKUP or MATCH can return both coordinates to you (line and row). You have to work based on column by column.

All the best

Upvotes: 0

Code Different
Code Different

Reputation: 93141

Try this:

=D1+SUMIF(A:A,C1,B:B)

SUMIF returns the total of column B for rows in column A that matches cell C1. It works if the values in column A are unique.

Upvotes: 1

Related Questions