Forum Discussion

Aucesar's avatar
Aucesar
MCT
Mar 21, 2023
Solved

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 re...
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Mar 22, 2023

    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.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Mar 21, 2023

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.

 

  • Aucesar's avatar
    Aucesar
    MCT
    Mar 22, 2023

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

     

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Mar 22, 2023

      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.

Resources