Thread: Obtain last inmediatly value with certains conditions on changing column In XL07

1. Obtain last inmediatly value with certains conditions on changing column In XL07

Hi,

I have one worksheet. I am needing to match up column G with value on E3 and with value E4 and give consecutive numbers on column F depending if the matched column G with value on E3/E4 is on same date of column L and if column Q (Tipo de movimiento) appers "Ventas"; I have plenty blankcells and other values on column G.
I trying catch up with excel..

2. Try this formula in F2:

=IF(G2="","",IF(AND(ISNUMBER(MATCH(G2,E\$2:E\$3,0)),Q2="Ventas"),IF(LOOKUP(2,1/(F\$1:F1<>""),L\$1:L1)=L2,LOOKUP(10^10,F\$1:F1)+1,1),""))

copied down

3. Hi NBVC,

It helps me lot your formula but I have another last inquiry, as you can see there are 2 variables that I´m looking for (E2 and E3) you help me to take both of them like the same but they must be counted diferent.. I mean, if excel find the 1st E\$2will put 1 and continue counting E\$2 but if finds E\$3 has to count from 1 to any as the date permit.
Sorry for my non-well explanation but I'm caching up also with that..

4. Sorry for the late reply....

Try in G3 or your attachment:

=IF(ISNUMBER(MATCH(G3,\$E\$3:\$E\$4,0)),COUNTIFS(\$G\$3:\$G3,\$G3,\$L\$3:\$L3,\$L3,\$Q\$3:\$Q3,"Ventas"),"")

copied down.

P.S.

In case you are also interested in my initial interpretation again - combining E2 and E3 (i.e. Post # 2), I realized after creating my formula above, that my original formula can be simplified to a similar formula... i.e.

=IF(ISNUMBER(MATCH(G3,\$E\$3:\$E\$4,0)),SUMPRODUCT(COUNTIFS(\$G\$3:\$G3,\$E\$3:\$E\$4,\$L\$3:\$L3,\$L3,\$Q\$3:\$Q3,"Ventas")),"")

5. Hi NBVC,

The 1st one works perfectly, I only have to avoid obtaining a value on
column Q (Tipo de movimiento) if appers "Any other text".. but I think I can do it!
Thanks for your help! I´m a begginer in excels use but seems awesome things you can do and these kind of sites that can share
knowledge.
Have a great day!