# Excel - INDEX Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a formula which I am trying to append, I would like to add d2:d301 to it. I've inherited the formula so not sure how to add to it current formula is =iferror(if(row()<>2,INDEX(arrayformula(filter(\$P\$2:\$P301,\$C\$2:\$C301<>"",row(\$C\$2:\$C301)=max(if(\$C\$2:\$C301=C302,row(\$C\$2:\$C301),0)))) ,1),0),0)
Solved by A. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 08/07/2018 - 04:28
Hi..
User 08/07/2018 - 04:29
hi
Excelchat Expert 08/07/2018 - 04:29
do you have the data where the formula is being used?
Excelchat Expert 08/07/2018 - 04:29
it will help me in debugging the formula better..
User 08/07/2018 - 04:29
yes but can't share it
User 08/07/2018 - 04:30
has company confidential info
Excelchat Expert 08/07/2018 - 04:30
ohh.. ok.. i understand.. no problem..
Excelchat Expert 08/07/2018 - 04:30
can you explain the context of this formula?
Excelchat Expert 08/07/2018 - 04:31
also are you having this formula in google sheet?
User 08/07/2018 - 04:32
two accounts owners have same product and need file to recognise they are seperate owners colume D has owner name, colume c is product
User 08/07/2018 - 04:32
Excelchat Expert 08/07/2018 - 04:33
User 08/07/2018 - 04:34
column p tracts all costs of product associated with the buy or sell
Excelchat Expert 08/07/2018 - 04:34
i have got an idea of the formula.. but to debug it will need some data..
User 08/07/2018 - 04:35
ok can you see the sheet to the right?
Excelchat Expert 08/07/2018 - 04:36
yes..
User 08/07/2018 - 04:36
ok I ll type in there
Excelchat Expert 08/07/2018 - 04:41
Hi are you there??
User 08/07/2018 - 04:44
dont worry about sector "ref" it copied over bad. it's fine on mine
Excelchat Expert 08/07/2018 - 04:44
ok.. in which cell are you using this formula?
User 08/07/2018 - 04:45
colunm n is where formula is
Excelchat Expert 08/07/2018 - 04:46
Thx.. for the data.. with this info.. let me link the question posted earlier..
Excelchat Expert 08/07/2018 - 04:46
give me a few mins..
Excelchat Expert 08/07/2018 - 04:49
can you please updated the data for column P.. without the formula?
Excelchat Expert 08/07/2018 - 04:49
i see from the formula its a key column...
User 08/07/2018 - 04:50
you just want raw numbers
Excelchat Expert 08/07/2018 - 04:50
yes..
Excelchat Expert 08/07/2018 - 04:54
ok.. i kinda got the data..
User 08/07/2018 - 04:55
but previous cost should read 0, 0, cause owner is different
User 08/07/2018 - 04:55
for n2 and n3
Excelchat Expert 08/07/2018 - 04:56
i've not yet.. done the formula..
Excelchat Expert 08/07/2018 - 04:56
so now you need to get the previous cost for the same owner?
User 08/07/2018 - 04:56
yes just explaining what I think it
User 08/07/2018 - 04:57
yes each owner should get their own, but right now it sums up regardless of owner it ids just the product
Excelchat Expert 08/07/2018 - 05:00
=iferror(if(row()<>2,INDEX(arrayformula(filter(\$P\$2:\$P2,\$C\$2:\$C2<>"",\$D\$2:\$D2=D2,row(\$C\$2:\$C2)=max(if(\$C\$2:\$C2=C2,row(\$C\$2:\$C2),0)))) ,1),0),0)
Excelchat Expert 08/07/2018 - 05:00
can you please paste this updated formula in cell N2?
Excelchat Expert 08/07/2018 - 05:00
Excelchat Expert 08/07/2018 - 05:00
and drag this formula all the way till cell 302..
Excelchat Expert 08/07/2018 - 05:01
drag the formula = at the bottom right corner of the cell.. click that and drag it downwards until cell 302
User 08/07/2018 - 05:02
ok going now
User 08/07/2018 - 05:06
not quite right it add the numbers look way too big.
User 08/07/2018 - 05:07
what i need it to do is filter product, the owner, and then add the cum cost
Excelchat Expert 08/07/2018 - 05:08
also should it be for the same product?
Excelchat Expert 08/07/2018 - 05:08
from cell C1?
User 08/07/2018 - 05:08
yes
Excelchat Expert 08/07/2018 - 05:09
the last formula you shared was checking if product column is not blank..
Excelchat Expert 08/07/2018 - 05:09
ohh ok.. let fix that problem also.
Excelchat Expert 08/07/2018 - 05:09
one question... will the product column be blank??
Excelchat Expert 08/07/2018 - 05:10
based on the data i dont think so..
User 08/07/2018 - 05:10
no product will be not be blank
User 08/07/2018 - 05:10
nor owner
Excelchat Expert 08/07/2018 - 05:10
got it..
Excelchat Expert 08/07/2018 - 05:10
can you please use this updated formula???
Excelchat Expert 08/07/2018 - 05:11
iferror(if(row()<>2,INDEX(arrayformula(filter(\$P\$2:\$P2,\$C\$2:\$C2=C2,\$D\$2:\$D2=D2,row(\$C\$2:\$C2)=max(if(\$C\$2:\$C2=C2,row(\$C\$2:\$C2),0)))) ,1),0),0)
Excelchat Expert 08/07/2018 - 05:11
repeat the same step from N2 till N302
User 08/07/2018 - 05:14
no not right. sorry
Excelchat Expert 08/07/2018 - 05:15
hmm.. for sampling.. i'm updating the product and account holder to same ..and testing..
User 08/07/2018 - 05:16
ok
Excelchat Expert 08/07/2018 - 05:18
from the data value of cell N3 should have been \$1619.93?
Excelchat Expert 08/07/2018 - 05:18
and value of cell N4 should have been 1834.95?
Excelchat Expert 08/07/2018 - 05:18
correct?
User 08/07/2018 - 05:19
with the original formula yes, but I need it to say 0, then when it is sold in row 4 the previous cost should read 28590
User 08/07/2018 - 05:20
andn row 5 previous cost s/b 1619.93
Excelchat Expert 08/07/2018 - 05:21
hmmm.. you did not mention about using the Buy/Sell ... also.. the formula doesnt take ref. from column "Transaction value with F/X"
Excelchat Expert 08/07/2018 - 05:22
i have only updated the previous formula to include "account Held" column and "Product" column..
User 08/07/2018 - 05:22
Excelchat Expert 08/07/2018 - 05:23
User 08/07/2018 - 05:24
buy sell doen't effect formula, the transacted units have + or -ve that drive it in column m
User 08/07/2018 - 05:24
coumn g
Excelchat Expert 08/07/2018 - 05:25
hmm.. we are left with only 4mins for this session...
Excelchat Expert 08/07/2018 - 05:26
so if i understand the whole requirement correctly.. for a Buy/Sell against an account holder.. you need to the previous max value from column P?
Excelchat Expert 08/07/2018 - 05:26
correct?
User 08/07/2018 - 05:26
yes
Excelchat Expert 08/07/2018 - 05:26
User 08/07/2018 - 05:26
account and product.
Excelchat Expert 08/07/2018 - 05:26
=iferror(if(row()<>2,INDEX(arrayformula(filter(\$P\$2:\$P2,\$C\$2:\$C2=C2,\$B\$2:\$B2=B2,\$D\$2:\$D2=D2,row(\$C\$2:\$C2)=max(if(\$C\$2:\$C2=C2,row(\$C\$2:\$C2),0)))) ,1),0),0)
Excelchat Expert 08/07/2018 - 05:27
i have now included the criteria for account and product...
User 08/07/2018 - 05:27
account holder can buy many products and then sell them later
Excelchat Expert 08/07/2018 - 05:27
the new formula includes the criteria for Type also.. Buy/Sell
Excelchat Expert 08/07/2018 - 05:28
we are running out of time..
User 08/07/2018 - 05:28
right orginal formula only accounted for product not account holder
Excelchat Expert 08/07/2018 - 05:28
marking the session as done..
Excelchat Expert 08/07/2018 - 05:28
please validate the formula.. as previously..
User 08/07/2018 - 05:28
can you help?
Excelchat Expert 08/07/2018 - 05:28
and provide a feedback

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.