# Multiple cells to lookup and find corresponding value

#### tds3871

##### New Member
I've tried several methods of doing this simply but nothing is working for me. I've tried variations on vlookup, lookup, match and offset.

Here's the problem:

I've got one row of names. There are 12 - 14 names, but they are repeated in different cells, making the row in one workbook 300 - 400 names long, and in another workbook, 400+ names. The next row over is a cell containing dollar values.

I need to write a simple formula that looks for one name in the row of names, then finds all occurrences of it. Next, it will look at the next row over to see the dollar value associated with that name, then add all dollar values up.

For instance:

A B
1 Anderson \$10.00
2 Best \$5.00
3 Anderson \$7.50
4 Smith \$8.16
5 Anderson \$15.00
6 Best \$4.56
7 Anderson \$10.00

I need the formula to look in row A and find all occurrences of "Anderson". For each occurrence it needs to look in row B and find the corresponding dollar amount, then add all of them together.
In this case, the formula would return \$42.50 for "Anderson"

Any help?

Last edited:

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### VoG

##### Legend
Try

=SUMIF(A:A,"Anderson",B:B)

#### tds3871

##### New Member
Excellent- That hit the spot! Thank you so much!

#### tds3871

##### New Member
What would happen if the \$ values were, say, 2 or 3 cells over? How would that affect the formula?

#### VoG

##### Legend
What would happen if the \$ values were, say, 2 or 3 cells over? How would that affect the formula?

Do you mean

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Anderson</td><td > </td><td > </td><td style="text-align:right; ">£10.00</td><td > </td><td style="text-align:right; ">£42.50</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Best</td><td style="text-align:right; ">£5.00</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Anderson</td><td style="text-align:right; ">£7.50</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Smith</td><td style="text-align:right; ">£8.16</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Anderson</td><td > </td><td style="text-align:right; ">£15.00</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Best</td><td style="text-align:right; ">£4.56</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Anderson</td><td style="text-align:right; ">£10.00</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F1</td><td >=SUMIF(A:A,"Anderson",B:B)+SUMIF(A:A,"Anderson",C:C)+SUMIF(A:A,"Anderson",D:D)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Replies
0
Views
284
Replies
9
Views
381
Replies
5
Views
433
Replies
0
Views
619
Replies
8
Views
2K

1,190,656
Messages
5,982,133
Members
439,757
Latest member
85Sarah2005

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back