I would like to index n1 with formula based on column E wich represend the week based on column i,j and L, How can i bring it matching all this collumns?>
exercitiu.xlsx
I would like to index n1 with formula based on column E wich represend the week based on column i,j and L, How can i bring it matching all this collumns?>
exercitiu.xlsx
Alex
I work in WFM so if you tell me what metric you are trying to figure out, I can maybe help you better. Right now, not sure what math N1 is supposed to be doing.
Sorry but your explanation makes no sense (to me, at least). I dont understand where I, J and L (TBI OUTBOUBD and AHT) would be referenced to?
Walk me through how you would do this manually please.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hmm maybe I do see.
I would add a helper column on sheet 2 (I used col P, use whatever you want, you could also hide it if needed)
P1=I1&J1&L1
copied down.
Then on sheet 1...
N1=INDEX(Sheet2!N:N,MATCH(Sheet1!I1&Sheet1!J1&Sheet1!L1,Sheet2!P:P,0))
copied down
Of course my result on n1 i intend to extract from sheet2 with dependency with collumns i mnentioned. E I J & L. Can we make it without the hellper? and i also need collumn e involved in this!
Last edited by alexxl; 04-03-2024 at 02:28 AM.
Can we make it without the hellper? and i also need collumn e involved in this!
Are you still using Excel 2016?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Unfortunately my company limited my office 365 just in one place so i need to go with lower versions on this example.
Try this:
=INDEX(Sheet2!$N$1:$N$10000,MATCH(1,(Sheet2!$E$1:$E$10000=E1)*(Sheet2!$I$1:$I$10000=I1)*(Sheet2!$J$1:$J$10000=J1)*(Sheet2!$L$1:$L$10000=L1),0))
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Omg Yes i remembered! yesterday i could simply not see it. based on your previous help, the compound index, is made like this and it s array.
=IFERROR(INDEX(Sheet2!N:N,MATCH(1,(E2=Sheet2!E:E)*(KpiDataweek!I2=Sheet2!I:I)*(KpiDataweek!J2=Sheet2!J:J)*(L2=Sheet2!L:L),0)),"")
Thank you very much!
Last edited by alexxl; 04-03-2024 at 04:01 AM.
Yes, indeed - you have had help on this before!!!
See post #9.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks