If cell in Sheet 1 matches cell in Sheet 2 or Sheet 3, pull another cell from Sheet 2 or Sheet 3
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
-
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
-
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)))
-
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)))
-
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
-
@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?
-
Are you able to provide a screenshot for context?
-
@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.
-
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?
-
Hi @Paul Newcome , yes we can do a drop down instead of a comma
-
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 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?
-
Happy to help. 👍️
That would be a SUMIFS.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 217 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!