Nest If Index Match - Referencing two sheets

Options

I am trying to search for a date if there is a matching address in one sheet and if it is not on that sheet, checking to see if that date and associated address are in a seperate sheet. the two sheets I am referencing work together through auto archives. the current formula that is written is;


=IF((INDEX({On Lot Const. Install Archive Tree Install Ref}, MATCH(Address@row, {On Lot Address Archive Ref}),0)>0),(Index{On Lot Const. Install Archive Tree Install Ref}, MATCH(Address@row, {On Lot Address Archive Ref}),0),IF((Index({Current On Lot Tree Install Ref},Match(Address@row,{On Lot Address Ref},0))>0),(Index({Current On Lot Tree Install Ref},Match(Address@row,{On Lot Address Ref},0))),"No Date"))


If the address matches, there should be a value either on the first sheet or the archive sheet.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @rsheehan

    Instead of using IF, I would recommend using the IFERROR Function!

    An INDEX(MATCH function will return an error if there's no match, so you can say that if there's an error (meaning if there is no match) then use the second INDEX(MATCH instead.

    Try this:

    =IFERROR(INDEX({On Lot Const. Install Archive Tree Install Ref}, MATCH(Address@row, {On Lot Address Archive Ref}, 0)), INDEX({Current On Lot Tree Install Ref}, Match(Address@row, {On Lot Address Ref}, 0)))


    If there are no matches on either sheet, you'll see an error. We can get rid of that with another IFERROR function:

    =IFERROR(IFERROR(INDEX({On Lot Const. Install Archive Tree Install Ref}, MATCH(Address@row, {On Lot Address Archive Ref}, 0)), INDEX({Current On Lot Tree Install Ref}, Match(Address@row, {On Lot Address Ref}, 0))), "No Date")


    Let me know if this makes sense and works for you.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!