Using INDEX MATCH Function with a Multi-Select drop-down column

Emily McClary

Hello,

I am trying to use an INDEX MATCH function to fill in the Transmission Region for the Sites Under Consideration, but am running into a problem when there is more than one site selected. I believe I need to add the HAS function, but have tried several ways and can't seem to make it work. Any help is appreciated!

jmyzk_cloudsmart_jp

Assuming that the sites under consideration belong to the same region, you need to use one of the sites as the MATCH function's search value.

To get one of the sites from multiple dropdown lists, you could use a formula like this;

=IFERROR(LEFT([Site Under Consideration]@row, FIND(CHAR(10), [Site Under Consideration]@row) - 1), [Site Under Consideration]@row)

I don't know if this is the best way, but it works.

https://app.smartsheet.com/b/publish?EQBCT=2e5ca16d579d4775aba6eb93dd7506a1

https://app.smartsheet.com/b/publish?EQBCT=c0d967a80d994fd38ebf9af4d5b6cc17

jmyzk_cloudsmart_jp

Assuming that the sites under consideration belong to the same region, you need to use one of the sites as the MATCH function's search value.

To get one of the sites from multiple dropdown lists, you could use a formula like this;

=IFERROR(LEFT([Site Under Consideration]@row, FIND(CHAR(10), [Site Under Consideration]@row) - 1), [Site Under Consideration]@row)

I don't know if this is the best way, but it works.

https://app.smartsheet.com/b/publish?EQBCT=2e5ca16d579d4775aba6eb93dd7506a1

https://app.smartsheet.com/b/publish?EQBCT=c0d967a80d994fd38ebf9af4d5b6cc17

Emily McClary

@jmyzk_cloudsmart_jp Thank you for the quick response! I have tried that, but unfortunately it's only Matching one of the Sites Under Consideration and placing it in the Region column (see below). I need it to reference the Transmission Region that is connected to the Site on the other sheet.

Emily McClary

@jmyzk_cloudsmart_jp Thank you! I looked again at the files you shared and was able to figure it out! Thank you soooo much!

jmyzk_cloudsmart_jp

😀