Formula for IFERROR(Index(Collect based on 3 different unique columns.

Matthew R

Good Morning Smartsheet Community,

Current Formula: =IFERROR(INDEX(COLLECT({Pass/Fail}, {Sales Order Number}, @cell = SO@row, {Unit Part Number}, @cell = Part@row), 1), "")

Possible New Formula: =IFERROR(INDEX(COLLECT({Pass/Fail}, {Sales Order Number}, @cell = SO@row, {Unit Part Number}, @cell = Part@row, {Line Number}, @cell = Line@row), 1), "")

I simply need this formula to display data from a column on another sheet. We have (3) unique identifiers that I need to be recognized so that we track information correctly.

Sales Order/Unit Part Number/Line Number

It seems when I add the line number portion to the end it does not track things at all from my data sheet.

Apologies for this being a little vague, I have no problem sending pictures of my issue if needed.

Thank you for any help!

Christian G.

Hi @Matthew R

Can you send a screeshot of the columns you are refering?

Matthew R

The first picture is the three columns on my data sheet I need to all match from my "Source" sheet if you will. Ultimately, I need the formula to display whether the item Passed/Failed based on those 3 columns matching my "Source" sheet information. We do not always need all 3 columns filled out but in the times we do it is acting a little funky on the formula side of things.

Please let me know if you need anything else!

Christian G.

@Matthew R Are the columns alway filled from left to right? in your exemple there is always a "Unit Part number".

If it's the case for all of the lines, why do you need to check the other 2 columns ?

Matthew R

We have specific orders that are specially coded for different reasons. We also have a column that designates the type of inspection it is, if it is an order that is Coded then we would indicate it that way. I will send you a picture of that column as well.

Perhaps I could just use that column in the formula to get where I need to be?

Apologies if I am not explaining well enough.