Index Match on Headers as Match array?

melimob

Hey all

In SS, am I able to do something similar as in excel where I can do an index match looking at header names?

E.g. Sheet 1

ColHeaders: Country | Apples | Oranges | Pears

RowValue1: UK | Yes-Some | No | Yes

RowValue2: USA | No | Yes - Some | Yes

In another metric sheet 2 I want to be able to show:

Fruit | Countries

Apples | UK

Pears | UK, US

Oranges | US

I want to search for the word "Yes" across multiple columns looking for Fruit in the header name and return the countries with a join distinct collect to replicate the above metric sheet.

Is this possible?

Many thanks in advance

Soumitra Bhowmick

Given this is your data structure, the below formula can help

=JOIN(COLLECT(Country1:Country2, Apples1:Apples2, OR(@cell = "Yes", @cell = "Yes - Some")), "|")

=JOIN(COLLECT(Country1:Country2, Pears1:Pears2, OR(@ )), "|")

Note I was not able to figure out how to dynamically select the column, so you might have to hard code that