# How do I use INDEX & MATCH to do this?

#### daveyy

##### New Member
Hi,

Any assistance that can be offered is greatly appreciated.

In col A I have some codes
aaa
bbb
ccc
ddd etc

I wish to place a formula in col B that will return the cost that applies to the code. The formula will need to match the cost from the row where the code appears but the cost could be in either of 3 colums.

aaa £10.00
bbb £00.00 £20,00
ccc £00.00 £00.00 £30.00
ddd £25.00 £00.00 £00.00

Hopefully I have explained this so you can understand what I need, I can't get HTML Maker to work which would have made it a lot easier to understand.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

##### MrExcel MVP
"The formula will need to match the cost from the row where the code appears but the cost could be in either of 3 colums. "

Will it always be the case that, for the row in question, the cost records are either zero or greater than zero and that they'll only be one value greater than zero?

#### daveyy

##### New Member
Yes. There will only be 1 amount to find in the colums. Nothing else will apply to the reference.

#### Peter_SSs

##### MrExcel MVP, Moderator
daveyy

I am assuming that the values are numbers formatted as currency. Is this what you want?

Formula in B1 (copied down) is:
=SUMPRODUCT(--(\$A\$8:\$A\$11=A1),\$B\$8:\$B\$11+\$C\$8:\$C\$11+\$D\$8:\$D\$11)
Mr Excel.xls
ABCDE
1aaa10.00
2bbb20.00
3ccc30.00
4ddd25.00
5
6
7
8aaa10.00
9bbb0.0020.00
10ccc0.000.0030.00
11ddd25.000.000.00
12
Code Cost

#### Domenic

##### MrExcel MVP
Also, using Peter's example...

=SUMPRODUCT((A\$8:A\$11=A1)*(B\$8:D\$11))

or

=INDEX(B\$8:D\$11,MATCH(A1,A\$8:A\$11,0),MATCH(TRUE,INDEX(B\$8:D\$11,MATCH(A1,A\$8:A\$11,0),0)>0,0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

#### Peter_SSs

##### MrExcel MVP, Moderator
Also, using Peter's example...

=SUMPRODUCT((A\$8:A\$11=A1)*(B\$8:D\$11))
So concise!

##### MrExcel MVP
daveyy said:
Yes. There will only be 1 amount to find in the colums. Nothing else will apply to the reference.
Book3
ABCD
1Table
2aaa10
3bbb020
4ccc0030
5ddd2500
6
7
8
9
10aaa10
11ccc30
12ddd25
Sheet1

B10, copied down:

=SUM(INDEX(\$B\$2:\$D\$5,MATCH(A10,\$A\$2:\$A\$5,0),0))

#### daveyy

##### New Member
Thanks guys. Problem solved. Your help is much appreciated.

Replies
4
Views
172
Replies
6
Views
146
Replies
1
Views
237
Replies
36
Views
528
Replies
0
Views
371

1,195,849
Messages
6,011,955
Members
441,657
Latest member
Diupsy

### 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