Forum Discussion
Return value from 2 criteria lookup array, based on list of horizontal and vertical lookup value.
=VLOOKUP(F$1&$E2,CHOOSE({1,2},$A$2:$A$17&$B$2:$B$17,$C$2:$C$17),2,0)Maybe with this formula as shown in the attached file. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
=SUMPRODUCT(($A$2:$A$17=F$1)*($B$2:$B$17=$E2)*$C$2:$C$17)An alternative could be this formula.
=INDEX($C$2:$C$17,MATCH(F$1&$E2,$A$2:$A$17&$B$2:$B$17,0))Another alternative could be INDEX and MATCH. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
OliverScheurich Thank you for the effort. It works perfectly when both the data source and task are in the same sheet but the source of data is in different sheet and the task has to be done on a different sheet. I tried all three formulas with some modifications but couldn't make it. Can you please check where did I make a mistake... dummy file is attached in the link below:
https://filebin.net/cypmw8lspepkxu7x
Thankyou again 🙂
You are welcome. The dates in your sheet "Task" in range B1:AE1 are formatted as text. Therefore these dates aren't recognized as numbers and can't be used for calculations. The dates in your sheet "Datas" in range A2:A35334 are formatted as numbers and can be used for calculations.
In sheet "Task" the dates are left-aligned in the cells and in sheet "Datas" the dates are right-aligned. Text values are left-aligned in the cells and number values are right-aligned in the cells.
The solution is to enter the dates in your sheet "Task" in the same format as in sheet "Datas".
This works in my spreadsheet as you can see in the attached file. I have to enter all three formulas (INDEX, SUMPRODUCT and VLOOKUP) with ctrl+shift+enter because i don't work with Office365 or 2021.
OliverScheurich
Thank you so much for replying.
I got it 🙂
Tried another formula that works=SUMIFS(Datas!$C$2:$C$22,Datas!$A$2:$A$22,Task!F$1,Data!$B$2:$B$22,Task!$E2)