Writing formula for IF(AND(CONTAINS with a VLOOKUP.

Matthew R

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

Paul Newcome

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)

Paul Newcome

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

Paul Newcome

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)

Matthew R

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!

Matthew R

Paul Newcome

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

Matthew R

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!

Paul Newcome

Happy to help. 👍️