IF formula with INDEX and MATCH that doesn't work

Dear all,

I'm using the forum for the first time.
I've always looked for answers to my excel problems on various websites, but I cannot find the specific problem that I have.

Here's the link to the file:
FOLLOW-UP - test.xlsx

In this file, there are 2 worsheets:
- worksheet "國家" for countries (in Chinese) - with 2 "countries" columns (with the full name, and with the abbreviations of the countries "UAE", "USA", "UK")
- worksheet "test" on which the "region" column should return the region information from the first worksheet according to the given "country".
The formula is not working when it needs to match information from the second column, it doesn't return the corresponding "region".

Thanks in advance for your attention!

Thank you so much for your swift answer!
My issue was on the "region" B colum, where it returns a "N/A" to me. The D column is for you to see the difference between the different formulas I used.
I would like to use a formula I can copy across the whole workbook.

I've replaced the Chinese with English.
FOLLOW-UP - test.xlsx

Here are the formulas:
1 Europe Romania =IF(MATCH(C4,Countries!B:B),INDEX(Countries!A:A,MATCH(C4,Countries!B:B,0)),INDEX(Countries!A:A,MATCH(C4,Countries!F:F,0)))
2 Europe Spain =IFS(MATCH(C5,Countries!B:B),INDEX(Countries!A:A,MATCH(C5,Countries!B:B,0)),MATCH(C5,Countries!F:F),INDEX(Countries!A:A,MATCH(C5,Countries!F:F,0)))
3 Europe UK =IF(MATCH(C6,Countries!F:F),INDEX(Countries!A:A,MATCH(C6,Countries!F:F,0)))
4 #N/A UAE =IF(MATCH(C7,Countries!B:B),INDEX(Countries!A:A,MATCH(C7,Countries!B:B,0)),INDEX(Countries!A:A,MATCH(C7,Countries!F:F,0)))
5 #N/A USA =IFS(MATCH(C8,Countries!B:B),INDEX(Countries!A:A,MATCH(C8,Countries!B:B,0)),MATCH(C8,Countries!F:F),INDEX(Countries!A:A,MATCH(C8,Countries!F:F,0)))

1.2.3. are working, because they are matching only one element.
I cannot use 3.: it will only work for the 3 countries in column F ("UAE", "USA", "UK).
That's why I need 1. and 2. to work, no matter which column is the reference.

TEST

B4=IFERROR(IF(ISNUMBER(MATCH(C4,Countries!B:B,0)),INDEX(Countries!A:A,MATCH(C4,Countries!B:B,0)),INDEX(Countries!A:A,MATCH(C4,Countries!F:F,0))),"")

Copy down

Another way,
B4

Please Login or Register  to view this content.

copy down.

Hi! Thank you both of you for your answer.
Unfortunately, it seems to not be working either.

Please Login or Register  to view this content.

=> Returns a blank case

Please Login or Register  to view this content.

=> Returns #N/A, even in cases that used to work.

Upload a sample file with error message and mock-up results.

Hi again!
I wanted to say that all your options ARE working!
I realized just now that the "Countries" worksheet in the original file is in Chinese, forgot to change it in the formula...

Again, I apologize for misleading all of you ^^"
And I thank you so much for your help!!!

You are welcome.
--------------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.