Need Help with INDEX(COLLECT w/ CONTAINS

Thom555

Hi All,

I'm trying to populate a column with a global Region value (AMER, APJ, EMEA) based on a local region code contained in an appliance name.

I have two sheets with data.

Sheet 1 contains a column of appliance names and a second column for the Region I want it to populate.
Sheet 2 has two columns, 1 for the Data Center Code (SMF, AMS, etc) and the second with the global Region (AMER, EMEA, etc)

I would like it to populate the global region into sheet 1 based on the Data Center Code contained in the appliance name.

I've tried a few of the other posts n the forum and feel like I'm close but I cant get it to work right.

Mark.poole

@Thom555

The issue here would be you have no identifying information to compare to. You would either need to add the Data center code to sheet 1. Either by pulling it Off of the name or by manually entering it. That said

=Index({Global Region Ref},Match([Data Center Code]@row,{Data Center Code Ref},0))

Mark.poole

@Thom555

The issue here would be you have no identifying information to compare to. You would either need to add the Data center code to sheet 1. Either by pulling it Off of the name or by manually entering it. That said

=Index({Global Region Ref},Match([Data Center Code]@row,{Data Center Code Ref},0))

Thom555

Thanks @Mark.poole. That helped alot. I also found there was some issues with the data itself causing matching errors. But I got it to work! Thanks!