I am sure there is a simple formula for this but I have not been able to find a solution anywhere.

I am trying to fill a cell based on a list in a different tab. In my main sheet, each row has a country assigned (multiple from each country). I am trying to assign the next column a region, based on the corresponding country. (eg - Nigeria = Africa, Spain = Europe).

in Column F the country is included, Column G needs to show the corresponding region. The region is one of 5 possible options from Sheet B.

Appreciate any help here!

Sheet A

Sheet B

1 Answer 1


I have a draggable formula that works, but it's certainly not the best:

    AND(Result!A2<>"", COUNTIF(INDEX(Result!A2='Main Sheet'!$A$2:$E),true)),
    INDEX('Main Sheet'!$A$1:$E$1,
          MAX(INDEX((Result!A2='Main Sheet'!$A$2:$E)*(COLUMN('Main Sheet'!$A$2:$E))))

image the main sheet: main

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.