Nested If not working

Good afternoon all, I'm new to this forum and hoping some one can help.

I am having a specific problem and am very much hoping that you can help. I have added the simplified problem below; I am trying to get a cell on a sheet to return the name of the participant who won a given race (or came 2nd, 3rd, 4th etc) when the results are on one page, the participant name on a second and the formula on a third. I have included the formula that I have used with google forms (which uses formulas in a similar way to excel), however, the result always comes back as N/A if the first criteria is not met.

I hope that makes sense and hope you can offer a solution.



Sheet called Results

A B C
1 Team A Team B
2 Position 1 2



Sheet called Participants

A B C
1 Team A Team B
2 Participant Matt Laura



Sheet called Winners

A
1 1st Place
2 =if(‘Results’!B2=1,’Participants!B2,if((‘Results’!C2=1,’Participants!C2))


I very much hope some one can either see where i am going wrong or offer another suggestion.

Thanks

TJ

Sorry guys, formatted the original post to try to show the cell layout (hense the letters across the top and the numbers down the side as i cant add a table) and when i hit submit it just formatted it as a conversation, hopefully it still makes sense though.

TJ

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Guessing

in "Winners"

in B2

=IFERROR(INDEX(Participants!$A$2:$B$2,0,MATCH(INDEX(Results!$A$1:$B$1,MATCH($A2,Results!$A$2:$B$2,0)),Participants!$A$1:$B$1,0)),"")

See attached

Afternoon again all, i think the solution above partially solves my problem, however, having not used index before, I cant really work out who to make it do what i need.

I have therefore attached a spreadsheet in the format i will be using it with text showing what i am trying to achieve, hopefully that gives enough information for some one to work their magic.

I look forward to a solution!

TJ

in B9

=IFERROR(INDEX(Results!$B$8:$K$8,MATCH(LEFT(Winners!B$7,1)+0,Results!$B$11:$K$11,0)),"")

in C9

=IFERROR(INDEX(Participants!$B$8:$K$17,MATCH(Winners!$A9,Participants!$A$8:$A$17,0),MATCH(Winners!B9,Participants!$B$7:$K$7,0)),"")


Copy these as paid to D:E and F:G

in H9

=IFERROR(INDEX(Results!$B$8:$K$8,MATCH(LEFT(Winners!H7,1)+0,Results!$B$11:$K$11,0)),"")

copy across to M

in N9

=IFERROR(INDEX(Results!$B$8:$K$8,MATCH(LEFT(Winners!H7,2)+0,Results!$B$11:$K$11,0)),"")

Given your current layout results you are going to have modify every formula

The suggested format in "Results_1" is a better layout as it is easy to copy/paste any formulae.

.... better still, add "Participant" column to "Results_1" for each competition. Then the "WINNERS" is a more pleasing version of your "Results_1" sheet.

Further update:

Using single sheet for results ("Result_2")

"inners"

b9

=IFERROR(INDEX(Results_2!$B$4:$B$13,MATCH(LEFT(B$7,1)+0,INDEX(Results_2!$C$4:BJ14,,MATCH($A9,Results_2!$C$2:$BJ$2,0)+1),0)),"")

C9

=VLOOKUP(B9,Results_2!$B$4:$BJ$13,MATCH($A9,Results_2!$C$2:$BJ$2,0)+1,0)

Added hyperlinks to "Winners" column A to get to correct column in "Results_2"

JohnTopley, your solution took me a while to make work my exact spreadsheet but it does indeed solve the problem beautifully!

Thanks for the help!

TJ

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.