Could you elaborate more on what you want to happen? I'm not even sure if you need formulas to read info from DATOS, or if DATOS should read from Hoja3
Could you perhaps explain through words what the formula is trying to do then? If all we have is your formula, we can only use the logic which is in the formula, and apparently it's wrong. You show that some cells should result in "PE", but PE is not listed on DATOS.
But, trying to take a guess, I think the formula in I7 needs to be:
=INDEX(DATOS!T:T,SUMPRODUCT(ROW(DATOS!$U$2:$U$25)*(DATOS!$U$2:$U$25<=D7)*(DATOS!$V$2:$V$25>=D7)*(DATOS!$S$2:$S$25=A7)))
Can you clarify your conditions? You stated two conditions for when hour entrance = hour real. Either the first is >= and the other is <, or first is > and other is <=. Should not be both.
So, possible outcomes are either difference in minutes, A, NME, or a lookup from DATOS?
Still need clarification on conditions. Hour entrance will either be >=, or < hour real. There is no third option. What order should we be checking the conditions?
1-hour entrance> hour real
condition calculate difference minutes
2 if hour entrance < hour real
condition "A"
3 if hour entrance = hour real
condition "NME"
4 - also ,read status from DATOS between START DATE and final date ,put in same condition,should be as the result of the column j
So, should the logic then be:
1. Check if there is a record on DATOS with matching name and Date in between start/final. If true, return that status.
2. If False for #1, then check the following:
1-hour entrance> hour real
condition calculate difference minutes
2 if hour entrance < hour real
condition "A"
3 if hour entrance = hour real
condition "NME"
In I2, put this formula:
=IFERROR(INDEX(DATOS!T:T,(1/SUMPRODUCT(ROW(DATOS!$U$2:$U$25)*(DATOS!$U$2:$U$25<=D2)*(DATOS!$V$2:$V$25>=D2)*(DATOS!$S$2:$S$25=A2)))^-1),IF(H2=B2,"NME",IF(B2<H2,"A",B2-H2)))