[ATTACH=CONFIG]62599[/ATTACH]
I have tried many time but no idea of how to use index and match to fill the table in right side.:?
index and match question
-
-
-
Re: index and match question
Try using SUMIFS instead.
-
Re: index and match question
Hi TMSzeto,
It would help us to look into your problem is you were to attach an Excel copy of your data, rather than a JPG picture, which we can't work on.
However, given the layout of your data, I don't think you will find a solution using INDEX and MATCH. There are at least a couple of solutions, using SUMIFS and SUMPRODUCT:
=SUMIFS($C$27:$C$48,$A$27:$A$48,$E28,$B$27:$B$48,F$27)
=SUMPRODUCT(--($A$27:$A$48=$E28),--($B$27:$B$48=F$27),$C$27:$C$48)You should be able to put either of those formulas into the first cell and fill down and right.
Hope this helps.
-
Re: index and match question
Sorry for my mistake:spin:
here attached data.
I have to fill each cell from F3 to K12
Is it impossible by use index and match?
My friend tell me it can be done by the use of
=INDEX(A3:C6,MATCH(F2,B3:B6,0),3) in Cell F4However, the formula need to be changed when the item00# change.
[ATTACH=CONFIG]62601[/ATTACH] -
Re: index and match question
That will work, but only for that cell. You would have to define a different range within every formula. What we would assume you are looking for is a generic formula, that searches all the data in your table range, and looks for both the item number and location to return a value.
There is also no way of us knowing whether your actual data would appear in exactly the format you have posted. If, in your larger data table, item002 appears further down, the formula you have posted would not work.
-
-
Re: index and match question
you can use this formula
=INDEX($C$2:$C$23,MATCH($E3&F$2,$A$2:$A$23&$B$2:$B$23,0))
note this is an array formula and needs to be conformed by Ctrl+Shift+Enter
also for some combimnations the value does not exist and this will return an NA error hence couch this formula within IFERROR -
Re: index and match question
Or use a Pivot Table
-
Re: index and match question
Quote from Batman;728442That will work, but only for that cell. You would have to define a different range within every formula. What we would assume you are looking for is a generic formula, that searches all the data in your table range, and looks for both the item number and location to return a value.
There is also no way of us knowing whether your actual data would appear in exactly the format you have posted. If, in your larger data table, item002 appears further down, the formula you have posted would not work.
I got your point.
Actually I want a generic formula because I have to repeat the work for each item =S
Thank you very much =]Quote from pangolin;728443you can use this formula
=INDEX($C$2:$C$23,MATCH($E3&F$2,$A$2:$A$23&$B$2:$B$23,0))
note this is an array formula and needs to be conformed by Ctrl+Shift+Enter
also for some combimnations the value does not exist and this will return an NA error hence couch this formula within IFERRORThank you very much, this is a magic:wow:
I have learnt a new thing.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!