LookUp and Match a value located in a cell with multiple values and return single and/or multiple va

MarcRNelson
MarcRNelson ✭✭✭
edited 12/09/19 in Formulas and Functions

Hi,

I am hoping someone can help with this formula. I was able to get this working using VLOOKUP when there were fewer complexities.

In Sheet 2 when there is a value in the "Lot Number" cell,

  • The formula should look at Sheet 1 to see if the "Lot Number" matches and exists in any of the cells located under the Lot Number column.
  • If the "Lot Number" does exist add the "Event Ref No" associated with the "Lot Number" to Sheet 2. 
  • If the "Lot Number" is listed in more than one cell in Sheet 1 under the Lot Number column, the formula should capture each "Event Ref No" that is associated with the lot number, and concatenate them into the "Event Ref No" Cell associated with the lot number.
  • If the lot number does NOT exist in Sheet 1 then "NO Event Ref No Found" should be added in the associated "Event Ref No" cell in Sheet 2.

In Sheet 2 when there is blank or no value in the "Lot Number" cell,

  • If the Lot number in Sheet 2 is blank do not do return any value.  This is because in Sheet 1 there may be an Event Ref No but the Lot Number may be listed right away so I do not want it to start pulling values for blanks. 

 

Oh boy, oh boy!

 

Image.PNG

Tags:
· Share on FacebookShare on Twitter

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    Yeah, you don't want to use vlookup.

    https://help.smartsheet.com/function/collect

    use 

    Join(Collect())

    =join(collect([Even Ref Number]:[Event Ref Number],[Lot Number]:[Lot Number],[Lot Number Searching For]@row),",")

    Of course you are going to have to change the ranges to your sheet references and correct column names. I attempted to make this at least somewhat clear what you need to use. Make sure to have this part working before moving on, as the rest is based off this.

    Now we have the heart of your equation we can tackle the easier part. We can add some if statements that are somewhat redundant. for right now, I am going to refer to the above equation as FORMULAX, to make this shorter and easier to understand. Make sure you play around with the above formula and understand it before going to this part.

     

    =if([Lot Number Searching For] = "", "", if(FORMULAX = "", "No Event Ref No Found", FORMULAX))

    Collect is one of the most versatile and strongest formulas that smartsheet has. It is also one of the hardest to understand at first. Be sure to take your time, and read the help page if you have any problems implementing this formula.

    · Share on FacebookShare on Twitter
  • Paul Eckerstorfer
    Paul Eckerstorfer ✭✭

    Hi Marc,

    Did you solve your Problem? If so, with which formular did you get multiple Event Ref No in your Sheet 2? e.g. 12345 results in 6540, 9499, 2222!

    I am facing the same problem.

    Paul

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!