SOLVED

Excel formula help

Copper Contributor

Hi all,

I'm trying to create an excel formula and don't know how to start.  I know what I want it to do, but don't know how to make it work.  Please see the attached PDF.  Thanks!

 

Dean

 

25 Replies

Hi,

 

I will point you in the right direction!

You just need to one of the lookup functions such as VLOOKUP.

So, please check out this link!

 

Regards,

Haytham

That helped, but it didn't give me the necessary directions on what I wanted to do.  It just showed me an example and the example didn't match what I was trying to accomplish.

So I'm going to try again.  If you have to direct me to a video showing me how to do it, vs explaining it to me or showing me out right how the formula will need to work, please don't respond.  Thank you.

 

De

Hi,

 

On the PDF you attached, let me understand that you're trying to do!

You typed the last name in the cell next to Round 1 in the second sheet, and if this name is matched with the last name in the first sheet, then you want to return the Head Coach in the corresponding cell in the first sheet??

Yes, that is correct.  Anytime I put a name in the round, it should automatically associate the coach name and then put the head coach name in the other page.  Can you do that?  Thanks!

Dean

Well, but the data in the second sheet is not organized in a way that is easy to depend on in the VLOOKUP function.

To make the solution easier, you need to create a single table for all data in orders to be able to use the VLOOKUP or other lookup functions.
Otherwise, the solution will be difficult!

Can you combine them in a table (tabular form) as the data in the first sheet?

Thank you so much for your response and thanks for taking the time to look at this. If I need to break things out further, how would it be organized? I need to page two to remain the way it is. If you want to change page one, let me know how it would be organized? Page two is where each coach will know what player they get and the number next to the player is already a vlookup. Thanks!!

Dean

Okay, I'll try out!

But I need the workbook to apply the solution!

So if the data isn't sensitive, please attached it here in a reply.

 

For better understanding your data, I need you to explain more what the first name and last name in sheet 1, and what the round names and numbers in sheet 2?

In general, what is this data?

 

In addition, please note that the possible solution will depend on the name in Round 1 only, and will match it with the names in the Last Names column of Sheet 1.

And if there is a match, it will return the Head Coach in the corresponding cell of the Coach column in Sheet 1.

I think you want to apply this for each team table, so I'll try that out as well.

 

Regards

Here is the file.  Thanks!

 

Dean

Hi,

 

I've created the formula below in the column Coach of the sheet Teams.

=IFERROR(HLOOKUP(C2,CHOOSE({1;2},'6th-8th Draft'!$B$4:$N$4,'6th-8th Draft'!$B$1:$N$1),2,0),
HLOOKUP(C2,CHOOSE({1;2},'6th-8th Draft'!$B$17:$N$17,'6th-8th Draft'!$B$14:$N$14),2,0))

 

Please check out the attached workbook.

Wow, that's pretty good, but for some reason when I inputed some other names, it wouldn't populate.  Can you take a look?

 

Dean

It looks like it only did it for the first row.  If I change the name on the first row it works, but when I go to round 2 or later, it doesn't work.  That's the trouble-shooting that I've found.  Thanks.


Dean

best response confirmed by testing out (Copper Contributor)
Solution

This is the difficult part!

Anyway, I've updated the workbook with another formula.

Holy cow!  That is just amazing!  You are super helpful and an incredible excel master.  I am so appreciative of your work!  Is there anything I can do to elevate your status?

 

Dean

I do have a question.  If I want to add an additional team or if I want to add more players, is it a simple copy/paste or is there much more to do?  Could you write the formula down in your reply so I can see what it looks like?  When I click in the box, it only showed the iferror.  Thanks.

 

Dean

No thank you!

But the good practice in communities is to mark the best response, in order to close the conversation and let other people know that you got what you want and the problem is solved.

In this community, there is a big button for this regard under each reply.

 

Regards

You can add other teams, but you need to expand the formula.

This is the formula, and to see it click F2.

=IFERROR(
INDEX('6th-8th Draft'!$B$1:$N$1,IFERROR(MATCH(C2,'6th-8th Draft'!$B$4:$N$4,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$5:$N$5,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$6:$N$6,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$7:$N$7,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$8:$N$8,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$9:$N$9,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$10:$N$10,0),
                                   MATCH(C2,'6th-8th Draft'!$B$11:$N$11,0))))))))),
INDEX('6th-8th Draft'!$B$14:$N$14,IFERROR(MATCH(C2,'6th-8th Draft'!$B$17:$N$17,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$18:$N$18,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$19:$N$19,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$20:$N$20,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$21:$N$21,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$22:$N$22,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$23:$N$23,0),
                                   MATCH(C2,'6th-8th Draft'!$B$24:$N$24,0))))))))))

 

I've used Two INDEXs and several MATCHs.

Index 1 used for team 1 to 4.

Index 2 used for team 5 to 8.

 

And the Match functions are used for rounds.

Wow!! Ok it’ll take me some time to decipher this! Again, thanks!!

Hi Again,

So I tried to add another team and wasn't able to do it.  Could you help with this and then maybe I'll understand it better?  Thanks!

 

Dean

Hi,

 

In this case, all you have to do is expanding the ranges in the second part of the formula as follows:

=IFERROR(
INDEX('4th-5th Draft'!$B$1:$N$1,IFERROR(MATCH(C37,'4th-5th Draft'!$B$4:$N$4,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$5:$N$5,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$6:$N$6,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$7:$N$7,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$8:$N$8,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$9:$N$9,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$10:$N$10,0),
                                   MATCH(C37,'4th-5th Draft'!$B$11:$N$11,0))))))))),
INDEX('4th-5th Draft'!$B$14:$R$14,IFERROR(MATCH(C37,'4th-5th Draft'!$B$17:$R$17,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$18:$R$18,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$19:$R$19,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$20:$R$20,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$21:$R$21,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$22:$R$22,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$23:$R$23,0),
                                   MATCH(C37,'4th-5th Draft'!$B$24:$R$24,0))))))))))

 

Please note that the Range is now from B to R instead of B to N as the previous one.

The reason why I updated only the second Index is that you've added the new team next to the second group of teams, not below it as a new group.

 

If you keep adding the new teams this way (to the right), I can make the formula dynamic for you as follows:

=IFERROR(
INDEX('4th-5th Draft'!$1:$1,IFERROR(MATCH(C2,'4th-5th Draft'!$4:$4,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$5:$5,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$6:$6,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$7:$7,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$8:$8,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$9:$9,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$10:$10,0),
                                   MATCH(C2,'4th-5th Draft'!$11:$11,0))))))))),
INDEX('4th-5th Draft'!$14:$14,IFERROR(MATCH(C2,'4th-5th Draft'!$17:$17,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$18:$18,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$19:$19,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$20:$20,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$21:$21,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$22:$22,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$23:$23,0),
                                   MATCH(C2,'4th-5th Draft'!$24:$24,0))))))))))

 

This is done by targeting the entire rows instead of a specific range of them.

Now you can add as many teams as you want to the right of the first or the second group.

 

Hope that helps

1 best response

Accepted Solutions
best response confirmed by testing out (Copper Contributor)
Solution

This is the difficult part!

Anyway, I've updated the workbook with another formula.

View solution in original post