Forum Discussion

Hagop Ipdjian's avatar
Hagop Ipdjian
Copper Contributor
Apr 12, 2018

HOW TO: If cell contains specific text that is part of a different list then return specific text

I'm trying to setup a stock in and out spreadsheet with a master sheet that will have all the values automatically calculated. I need help on the formula that will look for the 'sku' in the other sheets and give me the value right next to it. i.e if i type apples in the master sheet i want the formula to find the text value of apple which in this case should be green.

 

I need a formula purely on a text and not =sumif that im currently using for my numerical values.

 

This what im currently using for the numerical side which as mentioned above is working fine =SUMIF('ABC UK'!$J$2:$J$200050,A3,'ABC UK'!$K$2:$K$200050).

 

Open for suggestions and new formulas. 

Formulas & Functions
Requests
  • Logaraj Sekar's avatar
    Logaraj Sekar
    Steel Contributor
    Apr 13, 2018

    Hi Hagop Ipdjian,

     

    From above i understood that, you have a 'ABC UK' named sheet with Column J having fruit names & Column K having numbers.

     

    I can't under stand what 'sku' means?

     

    And next, if you apple's respective value VLOOKUP is my choice. But if you have multiple values for apple repeating more than one time in a row SUMIF function (you using currently) is my choice .

     

    You specified 'should be green'. So you want the value with colored, right? Then i will suggest CONDITIONAL FORMATTING. But it need some requirements. On which basis it should be colored in green? (mention it).

     

    If i understood wrongly, let me know more.

    • Hagop Ipdjian's avatar
      Hagop Ipdjian
      Copper Contributor
      Apr 13, 2018

      Hi There,

       

      Firstly thank you for taking the time to read this and reply.

       

      I have attached an example file.

       

      I need help with Columns B and D which I've highlighted in the spreadsheet attached.

       

      I want those to be automatically filled in when i input any information in Column C.

       

      The data for this automation is found in Sheet called 'In'.

       

      I would like sheet 1 to act as my 'statement' if you like of what i have currently in stock.

       

      Once again thank you for your time.

       

        

       

       

Resources