SOLVED

# Formula Help - Stock control

Copper Contributor

# Formula Help - Stock control

Hi need help to build a formula to control our inventory but I´m running circles.

- First time the item is added it needs to verify and return from the inventory sheet

- 2nd and others should return from "result column"

- I tried to used MAX and VLOOKUP no success.

Thanks for any kind of help

4 Replies

# Re: Formula Help - Stock control

``=IF(COUNTIF(\$B\$2:B2,B2)=1,VLOOKUP(B2,\$I\$2:\$J\$5,2,FALSE),INDEX(\$F\$2:F2,LARGE(IF(\$B\$2:B2=B2,ROW(\$B\$2:B2)-1),2)))``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. In the example the value for a product that is added for the first time is taken from the "inventory sheet" in columns I and J.

# Re: Formula Help - Stock control

@OliverScheurichAlmost there, 1st part worked Thanks so far, 2nd part give me #NUM error, any tip?

best response confirmed by Aucesar (Copper Contributor)
Solution

# Re: Formula Help - Stock control

I assume you don't work with Office365. If so you have to enter the formula as a matrixformula with ctrl+shift+enter. This puts the curly brackets (highlighted in yellow in the screenshot) around the formula in older versions of Excel e.g. in my Excel 2013.

# Re: Formula Help - Stock control

@OliverScheurichThanks, missed it! Worked like a charm!

1 best response

Accepted Solutions
best response confirmed by Aucesar (Copper Contributor)
Solution

# Re: Formula Help - Stock control

I assume you don't work with Office365. If so you have to enter the formula as a matrixformula with ctrl+shift+enter. This puts the curly brackets (highlighted in yellow in the screenshot) around the formula in older versions of Excel e.g. in my Excel 2013.