Hi Saliha

The value in cell B42 (23) is different from the others in column B (8).

Sunny

Hi SunnyKow

yes it is different i have mistakenly updated, but i have to updated based on "B" column & "C" column opening.

Saliha

Hello Saliha,

In my view you make it more difficult than it needs to be by having this Opening Qty column, there is no need for it as you have the number you want in the Closing Qty column. You just need to write another formula for the Closing Qty.

Try with following SUMPRODUCT formula in cell N4 and copy down.
=SUMPRODUCT(($J$4:$J4-$M$4:$M4)*($B$4:$B4=$B4)*($C$4:$C4=$C4))

By summing the Closing Qty per the two criterias you no longer need to have the Opening Qty column. See if this works for you.

Br,
Anders

Hi Anders

Thanks for your reply, but its showing wrongly opening data's displaying, i have attached for your reference. 

regards/Saliha

Hello Saliha,

The formula I gave is to be used in column N, the Closing Qty column. I suggest you delete the Opening Qty column. I am sorry if I was not clear about that.

Is there any reason to why you need the Opening Qty column other than to keep the current formula in column N?

Br,
Anders

Hi Saliha

See if this helps.

Sunny

Hello SunnyKow

Exactly i need this formula thanks for you, but i need lookup value b4 & c4 which is you have updated C column only.

can you please help to modify B column and C column.

=IFERROR(LOOKUP(2,1/($C$4:$C34=C35),$N$4:$N34),0)

Can you please tell me what is the logic you have use this (2,1/

Saliha

Hi Saliha

The normal LOOKUP search for the 1st occurrence of the value.

This LOOKUP searches for the last occurrence of the value.

I don't understand your question : "I need lookup value b4 & c4 which is you have updated C column only"

Please provide examples and the expected results.

Sunny

Hello Saliha,

A modified formula with two criterias using the LOOKUP function. You can replace the 1E+100 with 3 if you want. To be put in cell K4. Here is a good article for some guidance in the logic used in the formula.
=IFERROR(LOOKUP(1E+100,(1/($B$3:$B3=$B4))+(1/($C$3:$C3=$C4)),$N$3:$N3),0)

You can also use XLOOKUP to get the same result, easier to read. To be put in cell K4.
=XLOOKUP($B4&$C4,($B$3:$B3)&($C$3:$C3),$N$3:$N3,0,0,-1)

But again, based on your sample file I don't see any reason to have this Opening Qty column, if the goal is to have correct Closing Qty value based on these two criterias then that can be achieved without having the Opening Qty column as a helper column.

Br,
Anders

Hi Sunny

Thanks for your explanation.

Hi Anders

perfectly using this formula and many thanks you for this.

Saliha