If cell in Sheet 1 matches cell in Sheet 2 or Sheet 3, pull another cell from Sheet 2 or Sheet 3

Options

Hello,

I am trying to create a formula to achieve the below.

If PO number from Smartsheet 'US payment request' matches a PO number from Smartsheet 'PO Service and HR', or Smartsheet 'PO Hardware', return the Vendor name from Smartsheet 'PO Service and HR' or Smartsheet 'PO Hardware'. Thank you for the help and let me know if additional information is needed.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Ok. In that case it does end up with a different formula than the INDEX, but it being able to use the multi-select certain helps keep it from getting overly complex.

    =JOIN(COLLECT({HR Sheet Vendor Column}, {HR Sheet PO Number Column}, @cell = [PO Number]@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Hardware Sheet Vendor Column}, {Hardware Sheet PO Number Column}, [PO Number]@row), CHAR(10))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!