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

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.
    Christian G. ✭✭✭✭✭✭

    Hi @Matthew R

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

  • Matthew R
    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.
    Christian G. ✭✭✭✭✭✭
    edited 11/10/22

    @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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!