Problem with index/match formula - not returning expected results

Good Day all,

I have been attempting to build an index/match formula to transfer data from one excel sheet to another.
However, the formula I am using is not returning the expected results.

I have 2 sheets (TheCompetitors and TheQualifications).
The Competitors sheet lists 12 competitors (1 to 12) in cells A1 to A13, and The Status of these competitors in B1 to B13. (a numerical number, from 1 to 50).

I want the details of the status column in TheCompetitors sheet to be updated into TheQualifications sheet (in Cells C4 to C15).

In TheQualifications sheet, I have inputed the following formula into cells C4 to C15, however, I am not getting the correct results.

The formula is: =INDEX(TheCompetitors!$A$2:$A$11,MATCH(B4,TheCompetitors!$B$2:$B$11,0)+0,0)))

Cells B4 to B15 lists the competitors Number in best to worst order.

I know this request is very cryptic, however, I don't have the expertise to insert an image or a spreadsheet.. I apologise for my lack of expertise.


Any assistance will be very much appreciated.

Kind Regards..gsdanger

Good Day nflsales,

Thank you for your prompt response.
Firstly, the formula I typed in my original post should have read : =IF(B4="","",INDEX(TheCompetitors!$A$2:$A$11,MATCH(B4,TheCompetitors!$B$2:$B$11,0)+0,0))
I have attached an example spreadsheet (small). Hopefully this will clarify what I am trying to do.
Kind Regards..gsdanger

Thanks Ford for your input.
I used the formula and it worked just fine...Thanks. I also took you suggestion and used the vlookup option rather than the clunky if statements.
That work a treat also.

Thank you for you assistance. I appreciate it very much.

I will now close this thread as I consider a solution has been found.

Kind Regards..
gsdanger