Vlookup from another sheet to copy formula number

Options

Hello,

I am trying to determine how to get a cell copied into a sheet from another sheet based on certain criteria using a vlookup formula.

Here is the sheet i would want the formula to autofill in. When Gunma (Asahi) and Aussie lemonade are chosen, I would like the formula number to autofill pulling from another sheet.

So the formula would need to Vlookup into the sheet below and find the formula number based on the product "aussie" and Copacker "Asahi"



What formula would i enter in the formula number column to make it search the second sheet to find the correct formula number?

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @tchav

    Try the below:

    =INDEX(COLLECT({Formula},{Product},CONTAINS(@cell,Product@row),{copacker},CONTAINS(@cell,Plant@row)),1)

    Make sure to update the bolded cross sheet references

  • tchav
    tchav ✭✭✭✭
    edited 04/03/23
    Options

    I did this and it still isnt working

    =INDEX(COLLECT({HBC Master File by Site 20230324 Range 4}, {HBC Master File by Site 20230324 Range 2}, CONTAINS(@cell, Product@row), {HBC Master File by Site 20230324 Range 3}, CONTAINS(@cell, Plant@row)), 1)


    @Leibel S



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try switching the arguments around in both of the CONTAINS functions.

    CONTAINS(Product@row, @cell)

  • tchav
    tchav ✭✭✭✭
    edited 04/04/23
    Options

    @Paul Newcome @Leibel S It worked finally after some trial and error. Thank you for your help.


    One more question:

    What would I put in the formula to give an error if there are multiple of the same product listed in the sheet? Sometimes we have the same products with multiple different formulas and right now it is grabbing the first formula. I want there to be an error if there are multiple different formulas for the same product.


    =INDEX(COLLECT({HBC Master File by Site 20230324 Range 1}, {HBC Master File by Site 20230324 Range 2}, CONTAINS(@cell, Product@row), {HBC Master File by Site 20230324 Range 3}, CONTAINS(@cell, Plant@row)), 1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You would need to use an IF/COUNTIFS combo. Basically you would use a COUNTIFS to count how many rows there are matching the same range/criteria sets you have in your COLLECT (not including the very first range) then say IF that is greater than 1 then output "this error", otherwise run the INDEX/COLLECT.


    =IF(COUNTIFS(..........)> 1, "error message", INDEX/COLLECT)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!