Populate cell with the relevant Name Range a value belongs to

[Lewis.Henderson88]

Hi.

I have received a batch of contract data which includes the country in which a contract was executed among other details - the dataset does not capture what continent/region the country belonged to. I am required to compare the dataset I have received with full year actual financials which splits the data by continent/region.

In the original dataset I need to list next to each country what region it belongs to based on specified Named Ranges (see below) e.g. Cell A1 equals "UK" I then want a formula for cell A2 that would generate "Europe" and so on.

1) Europe
2) North_America
3) Central_America
4) South_America
5) Middle_East_Africa
6) Asia_Pacific
7) ROW

[JohnTopley]

So table with Country/Region Relationship:

=VLOOKUP(A2,X2:Y1000,2)

where X:Y range contains Country (In X) /Region (in Y)

????

[Lewis.Henderson88]

Please see attachment for guidance.

[JohnTopley]

Changed table layout (sorry!)

in D3 and copy down

=IFERROR(VLOOKUP(C3,Regions!$B$3:$C$93,2,0),"")

[JohnTopley]

If you want to keep the original table then

in D3

=IF(SUMPRODUCT((Regions!$D$3:$J$34=$C3)*COLUMN(Regions!$D$3:$J$34))=0,"",INDEX(Regions!$A$2:$J$2,SUMPRODUCT((Regions!$D$3:$J$34=$C3)*COLUMN(Regions!$D$3:$J$34))))

Example is in G3 of the attached.