Vlookup table array with strings

Trying to do get value from another sheet that contains commas and spaces but the Index formula does not work, do you have any other suggestion?


Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    INDEX is probably the formula you want to use, can you explain why it does not work? What does it do?

    It looks like sheet 1 Product code contains one code while sheet 2 contains multiple codes. Is the issue that you are trying to match a cell with one code against a cell with many codes in and therefore there is no match?

    If so, using INDEX with COLLECT and CONTAINS will help.

    =INDEX(COLLECT({Product Status Log Comments}, {Product Status Log Codes}, CONTAINS(@cell, [Master Code]@row)), 1)

    Note - I do not know which column you have called Range 3 and Range 4 so have included the column names in my cross-sheet references - you will need to adjust those.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    INDEX is probably the formula you want to use, can you explain why it does not work? What does it do?

    It looks like sheet 1 Product code contains one code while sheet 2 contains multiple codes. Is the issue that you are trying to match a cell with one code against a cell with many codes in and therefore there is no match?

    If so, using INDEX with COLLECT and CONTAINS will help.

    =INDEX(COLLECT({Product Status Log Comments}, {Product Status Log Codes}, CONTAINS(@cell, [Master Code]@row)), 1)

    Note - I do not know which column you have called Range 3 and Range 4 so have included the column names in my cross-sheet references - you will need to adjust those.

  • Thank you, your formula worked perfectly!

  • KPH
    KPH ✭✭✭✭✭✭

    Good to hear. Thanks for letting me know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!