INDEX (COLLECT) - From two separate source sheets

Rob Pritchard

We have a process where live jobs are created on a master sheet then moved to an archive sheet when finished. We have a number of other sheets which use VLookUps or INDEX(COLLECT) to pull required information in to another sheet.

How can I create a formula which will look at sheet 1 for the Job No and pull through the required information and when the job moves to archive the lookup still continues to find the Job No in sheet 2 and pulls through the same details ?

VLOOKUPS Only seem to work on one source sheet ?

In summary

Sheet 1 - Master Job List

Columns - Job No / Customer / Site Location

12345 / ABC Corp / Derby

Sheet 2 - Archive Job List

The above jobs is moved in to it

Sheet 3 - Review list

Currently uses Job No 12345 and VLOOKUP to pull through ABC Corp / Derby from the Master list but as soon as the Job moves to Archive the Job No is no longer left on the Master sheet and the Vlookup now needs to look at the Archive sheet ?

Hope this makes sense ?

jmyzk_cloudsmart_jp

Hi @Rob Pritchard

You can use IFERROR to first INDEX(MATCH or INDEX(COLLECT Sheet 1, and then, if error, INDEX(MATCH Sheet 2.😀

The first IFERROR is for the #NO MATCH at Sheet1 and Sheet2; no listing corresponds to the Job No in Sheet1 or Sheet2.

=IFERROR(IFERROR(INDEX({Sheet 1 - Master Job List Range Customer}, MATCH([Job No]@row, {Sheet 1 - Master Job List Range Job No}, 0)), INDEX({Sheet 2 - Archive Job List Range Customer}, MATCH([Job No]@row, {Sheet 2 - Archive Job List Range Job No}, 0))), "")

If I make the formula readable:

• IFERROR(
  • IFERROR(
    • INDEX({Sheet 1 - Master Job List Range Customer}, MATCH([Job No]@row, {Sheet 1 - Master Job List Range Job No}, 0)),
    • INDEX({Sheet 2 - Archive Job List Range Customer}, MATCH([Job No]@row, {Sheet 2 - Archive Job List Range Job No}, 0))),
• "")

If you are referencing only text values, you can use JOIN(COLLECT as follows;

=JOIN(COLLECT({Sheet 1 - Master Job List Range Customer}, {Sheet 1 - Master Job List Range Job No}, [Job No]@row)) + JOIN(COLLECT({Sheet 2 - Archive Job List Range Customer}, {Sheet 2 - Archive Job List Range Job No}, [Job No]@row))

The merit of this method is that you do not have to handle IFERROR because JOIN(COLLECT returns empty values if there is no match, and you can join two JOIN(COLLECT by the simple +.

In the published demo dashboard below, the Master Job List is editable. So, you can check how automation moves a row, and the formula works when you check the Archive checkbox.

https://app.smartsheet.com/b/publish?EQBCT=312657b70a7b4722b2c424b0ec868911