LookUp and Match a value located in a cell with multiple values and return single and/or multiple va
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!
Help Article Resources
Check out the Formula Handbook template!