how to if$and properly

[lu1zuk]

sobe e desce teste.xlsx
	O	P	Q	R	S	T	U
2	Sobe ou Desce?	Cargo		Horas metas		Pontos
3	Mantem	Comandante de exercito	Aspirante 869353363815411760	30	Aspirante	10	869353363815411760
4	Mantem	Capitao tenente	2Tenente 869353363815411761	35	2 Tenente	20	869353363815411761
5	Mantem	Major	1Tenente 869353363823816714	40	1 Tenente	30	869353363823816714
6	Mantem	Capitao tenente	Soldado 901201962765279232	45	Soldado	10	901201962765279232
7	Mantem	Comandante de exercito	Taifero 901201959556616212	50	Taifero	10	901201959556616212
8	Mantem	Soldado	Cabo 901201955903406171	55	Cabo	10	901201955903406171
9	Mantem	1 Tenente	Capitao 869353363823816715	60	Capitao	10	869353363823816715
10	Mantem	Comandante de exercito	Capitao Tenente 869353363823816716	65	Capitao tenente	10	869353363823816716
11	Mantem	General de Divisao	Capitao de Corveta 869353363823816717	70	Capitao de Corveta	10	869353363823816717
12	Mantem	Taifero	Capitao de Fragata 869353363823816718	75	Capitao de fragata	10	869353363823816718
13	Mantem	2 Tenente	Capitao de Mar e Guerra 869353363823816719	80	Capitao de Mar e Guerra	10	869353363823816719
14	Mantem	General de Divisao	Major 869353363823816720	80	Major	10	869353363823816720
15	Mantem	1 Tenente	Sub Tenente 901201936555069510	85	Sub Tenente	10	901201936555069510
16	Mantem	2 Tenente	Tenente Coronel 869353363823816721	95	Tenente Coronel	10	869353363823816721
17	Mantem	Tenente Brigadeiro	Sub Oficial 901201929600901120	95	Sub Oficial	10	901201929600901120
18	Mantem	Capitao	Coronel 869353363823816722	100	Coronel	10	869353363823816722
19	Mantem	2 Tenente	Guarda Marinha 901201926312566845	100	Guarda Marinha	10	901201926312566845
20	Mantem	General de Exercito	General de Brigada 869353363832180797	130	General de Brigada	10	869353363832180797
21	Mantem	Major	Gen Divisao 869353363832180799	150	General de Divisao	10	869353363832180799
22	Mantem	Major	Gen Exercito 869353363832180801	200	General de Exercito	10	869353363832180801
23	Mantem	Aspirante	Com Exercito 869353363832180805	250	Comandante de exercito	10	869353363832180805
24	Mantem	General de Exercito	Contra Almirante 869353363874131971	500	Contra Almirante	10	869353363874131971
25	Mantem	Major	Tenente Brigadeiro 869353363874131972	500	Tenente Brigadeiro	10	869353363874131972
26	Mantem	Aspirante
27	Mantem	Aspirante	Sobe
28	Mantem	Aspirante	Sobe
29	Mantem	Contra Almirante	10
30	Mantem	Aspirante	250		Comandante de exercito
31	Mantem	Taifero	==SE(P3<>"";SE(L3>=ÍNDICE(R$3:R$25;CORRESP(P3;S$3:S$25;0));SE(M3>=ÍNDICE(T$3:T$25;CORRESP(P3;S$3:S$25;0));"Sobe";"Mantem")))
32	Mantem	Capitao de Mar e Guerra	VERDADEIRO
Planilha1

Cell Formulas
Range	Formula
O3:O32	O3	=IF(P3<>"",IF(M3>=INDEX(R$3:R$25,MATCH(P3,S$3:S$25,0)),"Sobe","Mantem"),"")
P3	P3	=IFERROR(@INDEX(S$3:S$25,AGGREGATE(15,6,(ROW(S$3:S$25)-ROW(S$3)+1)/ISNUMBER(SEARCH(U$3:U$25,E3)),1)),"")
P4:P32	P4	=IFERROR(INDEX(S$3:S$25,AGGREGATE(15,6,(ROW(S$3:S$25)-ROW(S$3)+1)/ISNUMBER(SEARCH(U$3:U$25,E4)),1)),"")
S13:S14	S13	=LEFT(Q13,FIND(8,Q13)-2)
U3:U25	U3	=RIGHT(Q3,18)
Q27	Q27	=IF(P3<>"",IF(L3>=INDEX(R$3:R$25,MATCH(P3,S$3:S$25,0)),IF(M3>=INDEX(T$3:T$25,MATCH(P3,S$3:S$25,0)),"Sobe","Mantem")))
Q28	Q28	=IF(P3<>"",IF(L3>=INDEX(R$3:R$25,MATCH(P3,S$3:S$25,0)),IF(M3>=INDEX(T$3:T$25,MATCH(P3,S$3:S$25,0)),"Sobe","Mantem")))
Q29	Q29	=INDEX(T$3:T$25,MATCH(P3,S$3:S$25,0))
Q30	Q30	=INDEX(R$3:R$25,MATCH(P3,S$3:S$25,0))
Q32	Q32	=IF(P3<>"",IF(AND(INDEX(R$3:R$25,MATCH(P3,S$3:S$25,0))),(M3>=INDEX(T$3:T$25,MATCH(P3,S3:S$25,0)))))

The idea is to use an IF-AND arg where its need the 2 to be fullfiled to say "subiu" or "desce"
If L3 corresponds to the index$match in and p3 also corresponds to the next index$match, if true "up" if false "down". I'm trying to do it but whenever I get to the point of the second logical test I can't "close" it itself
i tryed but failed.

 

[Fluff]

How about

=IF(P3<>"",IF(AND(L3>=INDEX(R$3:R$25,MATCH(P3,S$3:S$25,0)),M3>=INDEX(T$3:T$25,MATCH(P3,S$3:S$25,0))),"Sobe","Mantem"),"")

 

[lu1zuk]

How about

=IF(P3<>"",IF(AND(L3>=INDEX(R$3:R$25,MATCH(P3,S$3:S$25,0)),M3>=INDEX(T$3:T$25,MATCH(P3,S$3:S$25,0))),"Sobe","Mantem"),"")

Thanks! How if i want another if, not included in the if$and, i could use it in the true/false location just normal, right?

 

[Fluff]

I'm afraid I have no idea what you are saying.