How do I use INDEX & MATCH to do this?

daveyy

New Member
Joined
Jan 22, 2005
Messages
18
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.

Many thanks for any help you can provide.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
"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?
 
Upvote 0
Yes. There will only be 1 amount to find in the colums. Nothing else will apply to the reference.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,223,483
Messages
6,172,525
Members
452,463
Latest member
Debz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top