Can you Index-Match referencing multiple sheets?

Options
Reagan
Reagan ✭✭
edited 06/20/22 in Formulas and Functions

Hi! I am a newbie to Smartsheet and jumping in head first.

I am trying to use Index-Match to bring in information to a master sheet, but I need the function to look at multiple reference sheets.

Master Sheet

Highlighted in pink are the columns I am referring to. I would like the User to manually input a number into the 'ASN' column and the 'Network Name' auto-populates


Reference Sheet

I created three reference sheets named by region (AMER, APAC, EMEA) with two columns. The reference sheets have to be separate due to number of rows.



**I tried a simple Index-Match on one reference sheet, and it didn't work. And combining what doesn't work into an IF statement, well seems a bit mad. Below you will find my function statement. I feel like its not total circle, like I am missing the piece that brings the information back to the Master.

=INDEX({AMER ASN Filtered range 3}, MATCH([ASN \[MANUAL INPUT\]]@row,}, {AMER ASN Filtered Range 1}), 1))

Any help would be greatly appreciated.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Reagan

    The formula below has the correct syntax for an INDEX/MATCH.

    =INDEX({AMER ASN Filtered range 3}, MATCH([ASN \[MANUAL INPUT\]]@row, {AMER ASN Filtered Range 1}, 0))

    The zero within the MATCH function indicates the data is unsorted. You need this in the function.

    Does this work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!