Vlookup and sumif formula is failing

[sipa]

The following formula is failing to return any stock value if the first stock item in column B is zero. If anyone can help to correct it that would be great.


=IF(IF(ISERROR(VLOOKUP(H8,Stock!$A$2:$B$23,2,FALSE)),0,IF(VLOOKUP(H8,Stock!$A$2:$B$23,2,FALSE)-SUMIF($H$7:$H7,$H8,$E$7:$E7)>E8,E8,VLOOKUP(H8,Stock!$A$2:$B$23,2,FALSE)-SUMIF($H$7:$H7,$H8,$E$7:$E7)))<0,0,IF(ISERROR(VLOOKUP(H8,Stock!$A$2:$B$23,2,FALSE)),0,IF(VLOOKUP(H8,Stock!$A$2:$B$23,2,FALSE)-SUMIF($H$7:$H7,$H8,$E$7:$E7)>E8,E8,VLOOKUP(H8,Stock!$A$2:$B$23,2,FALSE)-SUMIF($H$7:$H7,$H8,$E$7:$E7))))

Thank you

[samba_ravi]

pls attach sample file

[joeu2004]

The following formula is failing to return any stock value if the first stock item in column B is zero. If anyone can help to correct it that would be great.

Please Login or Register  to view this content.

What do you mean by "failing to return any value"?

As written, the formula always returns zero, E8 or the positive difference VLOOKUP(H8,Stock!$A$2:$B$23,2,FALSE)-SUMIF($H$7:$H7,$H8,$E$7:$E7). Those are all values per se. Do you mean the formula always returns zero?

We will probably need to see an example Excel file that demonstrates the problem in order to explain why the formula always returns zero. There are oh-so-many possibilities. But someone might get lucky with a wild guess.

You can attach the file to a response using this forum's API. Alternatively, upload the file to a file-sharing website and post the public/shared URL in a response.

FYI, the formula can be simplified as follows:

Please Login or Register  to view this content.

I do not expect that improvement to change the behavior.

[sipa]

Thank you for your interest in my current problem.

I've attached a sample workbook to try to show what I'd like to achieve.
I think my previous approach is completely wrong, hopefully you can help.

[samba_ravi]

See the attached file
one supporting column

[sipa]

Hi, thanks for the revision, it does work.

Is there any way to avoid using a helper column ? if so, that would be perfect.

[samba_ravi]

D3=INDEX(ROW(INDIRECT(MIN(Stock!$D$2:$D$23)&":"&MAX(Stock!$D$2:$D$23))),MATCH(TRUE,INDEX(INDEX(SUMIFS(Stock!$B$2:$B$23,Stock!$A$2:$A$23,$A3,Stock!$D$2:$D$23,"<="&ROW(INDIRECT(MIN(Stock!$D$2:$D$23)&":"&MAX(Stock!$D$2:$D$23)))),0)>=SUMIF(A$3:A3,A3,B$3:B3),0),0))

COPY TOWARDS DOWN

[sipa]

nflsales,

Thank you very much, your solution works perfectly!
I will mark the thread as solved, and add to your reputation.

Thanks again

[daddylonglegs]

I used this formula to get the same results:

=MAX(Stock!D:D)-SUMPRODUCT(0+(SUMIFS(Stock!B:B,Stock!A:A,A3,Stock!D:D,"<="&ROW(INDIRECT("1:"&MAX(Stock!D:D)-MIN(Stock!D:D)+1))+MIN(Stock!D:D)-1)>=SUMIF(A$3:A3,A3,B$3:B3)))+1

[samba_ravi]

Thanks @sipa and @ Daddylonglegs for adding reputation

[sipa]

Thank you daddylonglegs, for your contribution too. Iit always amazes me how there are so many different solutions there seem to be to a single problem.

You guy's are awesome!

Of the two solutions offered, which would be faster ?