index match formula
- Thread starter vincent
- Start date
Somendra Misra
Excel Ninja
Somendra Misra
Excel Ninja
We are very close
I have attached my comments in the "data field revised.xlsx" file.
Kindly have a look at it.
Regards
Vincent
Somendra Misra
Excel Ninja
@vincent
Use below formula in E7 enter with Ctrl+Shift+Enter
=IFERROR(IFERROR(INDEX(DataField!$I$3:$I$7,MATCH(1,(--($A7=DataField!$B$3:$B$7))*(--(DataField!$E$3:$E$7=Summary!E$5))*(--(DataField!$D$3:$D$7="call off")),0)),INDEX(DataField!$H$3:$H$7,MATCH(1,(--($A7=DataField!$B$3:$B$7))*(--(DataField!$E$3:$E$7=Summary!E$5))*(--(DataField!$D$3:$D$7="")),0))),"")
Than copy down and across....
Regards!
Use below formula in E7 enter with Ctrl+Shift+Enter
=IFERROR(IFERROR(INDEX(DataField!$I$3:$I$7,MATCH(1,(--($A7=DataField!$B$3:$B$7))*(--(DataField!$E$3:$E$7=Summary!E$5))*(--(DataField!$D$3:$D$7="call off")),0)),INDEX(DataField!$H$3:$H$7,MATCH(1,(--($A7=DataField!$B$3:$B$7))*(--(DataField!$E$3:$E$7=Summary!E$5))*(--(DataField!$D$3:$D$7="")),0))),"")
Than copy down and across....
Regards!
Somendra Misra
Excel Ninja
Dear Sir,
I request one more help. If I have multiple references in a single cell and corresponding values in another cell of Sheet 1, can I get the value returned in sheet 2 in individual cells? Kindly go through the attachment.
Regards
Vincent
Somendra Misra
Excel Ninja
Somendra Misra
Excel Ninja
@vincent I had come up with a solution, but it will require your data to arrange like in the pic attached. If you do so you can use below formula in Summary!E7 and copy accross and down. (Note : This is an ARRAY formula so dont just Enter it, ENter with Ctrl+SHift+Enter)
=IFERROR(IFERROR(INDEX(DataField!$I$3:$I$10,MATCH(1,(--($A7=LOOKUP(ROW(DataField!$B$3:$B$10), ROW(DataField!$B$3:$B$10)/(DataField!$B$3:$B$10<>0), DataField!$B$3:$B$10)))*(--(DataField!$E$3:$E$10=Summary!E$5))*(--(DataField!$D$3:$D$10="call off")),0)),INDEX(DataField!$H$3:$H$10,MATCH(1,(--($A7=LOOKUP(ROW(DataField!$B$3:$B$10), ROW(DataField!$B$3:$B$10)/(DataField!$B$3:$B$10<>0), DataField!$B$3:$B$10)))*(--(DataField!$E$3:$E$10=Summary!E$5))*(--(DataField!$D$3:$D$10="")),0))),"-")
Just advise if you have any issue.
Regards!
Somendra Misra
Excel Ninja
Faseeh
Excel Ninja
Hi vincet,
In your simple file, how did you came to know Opening quantities for the week, like 200000, 100000, 50000 etc etc. Secondly i have absorved that the qtty that you are calling off to produce is not equal to the difference of qtty in hand and the qtty needed. i mean if you need 400 units of a product and you have 50 in hands, you need to call for 350 units that needed to be distributed over weeks. Thirdly there must be an upper limit of weekly production, what is that limit? can you elaborate this?? Hope i have explained my point.
In your simple file, how did you came to know Opening quantities for the week, like 200000, 100000, 50000 etc etc. Secondly i have absorved that the qtty that you are calling off to produce is not equal to the difference of qtty in hand and the qtty needed. i mean if you need 400 units of a product and you have 50 in hands, you need to call for 350 units that needed to be distributed over weeks. Thirdly there must be an upper limit of weekly production, what is that limit? can you elaborate this?? Hope i have explained my point.