Nest If Index Match - Referencing two sheets

rsheehan

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.

Genevieve P.

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