Vlookup table array with strings

Cathy Rey

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?

KPH

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.

KPH

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.

Cathy Rey

Thank you, your formula worked perfectly!

KPH

Good to hear. Thanks for letting me know.