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

Alexsys

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.

Paul Newcome

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))

Paul Newcome

You are going to need something along the lines of

=IFERROR(INDEX({HR Sheet Vendor Column}, MATCH([PO Number]@row, {HR Sheet PO Number Column}, 0)), INDEX({Hardware Sheet Vendor Column}, MATCH([PO Number]@row, {Hardware Sheet PO Number Column}, 0)))

Alexsys

Hi @Paul Newcome ,

I entered in the following formula and it is showing unsparseable, any ideas?

=IFERROR(INDEX({PO list service and HR Range 1}, MATCH([Corresponding PO # if applicable]@row, {PO list service and HR Range 2}, 0))),INDEX({PO list Hardware Purchase Range 1}, MATCH([Corresponding PO # if applicable]@row, {PO list Hardware Purchase Range 2}, 0)))

Paul Newcome

Right off I see that you have an extra closing parenthesis that needs removed just before the second INDEX function.

You have

))), INDEX

You should have

)), INDEX

Alexsys

@Paul Newcome That worked thank you so so much! One other question, if I have two PO numbers in the Payment Request sheet like (HMSUS230435, HMSUS230774) - is there a way to have it search for both? Or do I need to structure it differently?

Paul Newcome

Are you able to provide a screenshot for context?

Alexsys

@Paul Newcome See how the line with two PO's pulls as no match? I would like it if it could pull both of the vendor names.

Paul Newcome

Are you able to change that to a multi-select dropdown and use line breaks in between selections instead of commas? If not, what is the maximum number of entries that could be in a single cell?

Alexsys

Hi @Paul Newcome , yes we can do a drop down instead of a comma

Paul Newcome

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))

Alexsys

@Paul Newcome thank you that worked. One more question so sorry. I am trying to create another formula that does the following:

If PO number on Smartsheet 'PO Hardware' matches a PO number on Smartsheet 'Payment Request', it will sum all applicable invoices for me. Any idea on that?

Paul Newcome

Happy to help. 👍️

That would be a SUMIFS.

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