Referencing Another Sheet - VLOOKUP vs INDEX vs ??

Options

Hello,

I need to populate a cell with a number that is in another sheet based on an independent UID found in a different column that will be on both sheets. Not sure which direction to go on this --

For each line form-generated line, D. needs to look at C., find/match the data to A., then populate D with B when:

A. Production Sheet 1/Column 1: SKU Tag # (UID)

B. Production Sheet 1/Column 2: Starting Qty (Manually Inputted by Operator)

C. Packaging Sheet 2/Column 1: SKU Tag # (UID)

D. Packaging Sheet 2/Column 2: Starting Qty (Formula Column)

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Bessie Koch

    It sounds like an INDEX(MATCH will work for you!


    For example, you could put this in Sheet 2 Column 2:

    =INDEX({Sheet 1 Column 2}, MATCH([Column 1]@row, {Sheet 1 Column 1}, 0))


    The first {Range} is the column that has the data you want to bring back from the other sheet. Then in the MATCH function you first list the value in the current sheet, then search for it in the second {Range} from your other sheet.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Bessie Koch
    Bessie Koch ✭✭
    Answer ✓
    Options

    This worked beautifully! Thank you so much!

    A follow-up question...is there a way to embed an IF/THEN to an INDEX/MATCH? I got "#NO MATCH" on some and realized that there are two sheets feeding into the one.


    A. Production Sheet 1/Column 1: SKU Tag # (UID)

    B. Production Sheet 1/Column 2: Ending Qty (Manually Inputted by Operator)

    C. Production Sheet 2/Column 1: SKU Tag # (UID)

    D. Production Sheet 2/Column 2: Ending Qty (Manually Inputted by Operator)

    E. Packaging Sheet 3/Column 1: Fruit Name

    F. Packaging Sheet 3/Column 2: SKU Tag # (UID)

    G. Packaging Sheet 3/Column 3: Starting Qty (Formula Column)


    If E = "Bananas", then F needs to find a match in A and populate G with B

    If E = "Apples", then F needs to find a match in C and populate G with D

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Bessie Koch

    It sounds like an INDEX(MATCH will work for you!


    For example, you could put this in Sheet 2 Column 2:

    =INDEX({Sheet 1 Column 2}, MATCH([Column 1]@row, {Sheet 1 Column 1}, 0))


    The first {Range} is the column that has the data you want to bring back from the other sheet. Then in the MATCH function you first list the value in the current sheet, then search for it in the second {Range} from your other sheet.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Bessie Koch
    Bessie Koch ✭✭
    Answer ✓
    Options

    This worked beautifully! Thank you so much!

    A follow-up question...is there a way to embed an IF/THEN to an INDEX/MATCH? I got "#NO MATCH" on some and realized that there are two sheets feeding into the one.


    A. Production Sheet 1/Column 1: SKU Tag # (UID)

    B. Production Sheet 1/Column 2: Ending Qty (Manually Inputted by Operator)

    C. Production Sheet 2/Column 1: SKU Tag # (UID)

    D. Production Sheet 2/Column 2: Ending Qty (Manually Inputted by Operator)

    E. Packaging Sheet 3/Column 1: Fruit Name

    F. Packaging Sheet 3/Column 2: SKU Tag # (UID)

    G. Packaging Sheet 3/Column 3: Starting Qty (Formula Column)


    If E = "Bananas", then F needs to find a match in A and populate G with B

    If E = "Apples", then F needs to find a match in C and populate G with D

  • Genevieve P.
    Options

    Hi @Bessie Koch

    Yes!

    We can use an IFERROR function to say that "If there's an error, look into sheet 2 instead".

    =IFERROR(First Formula, Second Formula)

    Or:

    =IFERROR(INDEX({Sheet 1 Column 2}, MATCH([Column 1]@row, {Sheet 1 Column 1}, 0)), INDEX({Sheet 2 Column 2}, MATCH([Column 1]@row, {Sheet 2 Column 1}, 0)))


    See: IFERROR Function

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Bessie Koch
    Options

    Thank you so much!! Working on testing this :)

  • Bessie Koch
    Options

    Tested this and it works great! Thank you very much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!