Writing formula for IF(AND(CONTAINS with a VLOOKUP.
This is the formula I have come up with so far.
=IF(AND(CONTAINS(SO@row, {Sales Order Number}), CONTAINS(Part@row, {Unit Part Number})), IFERROR(VLOOKUP(Part@row, {VLOOK Part Pass/Fail}, 4, false), ""), IF(AND(CONTAINS(SO@row, {Sales Order Number}), Part@row = ""), IFERROR(VLOOKUP(SO@row, {VLOOK Pass/Fail}, 3, false), "")))
I am currently trying to work this formula to display what is stated in my "Pass/Fail" column on my Remote Sheet onto my Main Sheet based on unique "Sales Order" numbers and also the "Unit Part Number"
In picture 2 of my Main Sheet it is tracking the very first entry from my Remote Sheet as a "Passed with sign off" even though the "Sales Order 2" number does not match the "SO" number on my main sheet. "918FY" on my Main Sheet should read "Passed" like the "Pass/Fail" column states on the Remote Sheet. The Unit Part Number "HSAN-10-F" is the unique part number I am trying to track, it seems as though my Main Sheet grabs the very first "HSAN-10-F" it sees on my Remote Sheet and it displays what is in the "Pass/Fail" column for all of them on my Main Sheet.
Just wondering if anyone had any insight on my possible issues, I feel like it is something fairly simple that is holding me up.
Thank You All!!!
Remote Sheet
Main Sheet With Formula Below
Best Answers
-
You need an INDEX/COLLECT for this.
=INDEX(COLLECT({Pass/Fail Column}, {Sales Order Number Column}, @cell = SO@row, {Unit Part Number Column}, @cell = Part@row), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
For that you would use an IFERROR like so...
=IFERROR(INDEX(COLLECT({Pass/Fail Column}, {Sales Order Number Column}, @cell = SO@row, {Unit Part Number Column}, @cell = Part@row), 1), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You need an INDEX/COLLECT for this.
=INDEX(COLLECT({Pass/Fail Column}, {Sales Order Number Column}, @cell = SO@row, {Unit Part Number Column}, @cell = Part@row), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This is exactly what I was after, just looking to get rid of the "#INVALID VALUE" in the blank columns and have that just display as "Not Inspected"
Thank you so much for all your time and help Paul!
-
For that you would use an IFERROR like so...
=IFERROR(INDEX(COLLECT({Pass/Fail Column}, {Sales Order Number Column}, @cell = SO@row, {Unit Part Number Column}, @cell = Part@row), 1), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That is exactly what I was working on implementing with the formula, very good to hear I was down the right path for this part of the formula. The first part of the formula you were a world of help on, thank you very much for that. I never paid as much attention to (INDEX/(COLLECT functions as I should have until now, I appreciate the introduction to them, I will be using them often!
Thank You Paul!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!