I have three lists of countries by region (EU, NA, CIS) and would like to program another cell that reports back which region a country belongs to based on the these lists.

This is how my sheet looks like:

I would like to input countries in the B column and have column A reporting back what region (EU, NA or CIS) these countries belong to. Not quite sure which fuction would allow me to do that. If anyone is able to help, it would be greatly appreciated.

CodePudding user response：

Maybe I overcomplicated things:

Formula in B1:

=FILTER(D1:F1,BYCOL(D2:F,LAMBDA(a,COUNTIF(a,B2))))

CodePudding user response：

I've just seen you posted an answer yourself. Here it goes another approach:

=INDEX(D1:F1,1,MOD(MATCH(B2,FLATTEN(D2:F),0)-1,COUNTA(D1:F1)) 1)