Formula Help - Stock control

[Aucesar]

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

[#2]

@Aucesar 

=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.

 

[#3]

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

[#4 · Accepted solution]

@Aucesar 

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.

[#5]

@OliverScheurichThanks, missed it! Worked like a charm!