• 546K Members
• 2,443 Online
• 652K Conversations
SOLVED

Excel formula help

Highlighted
Occasional Contributor

Excel formula help

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
Highlighted

Re: Excel formula help

Hi,

I will point you in the right direction!

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

Regards,

Haytham

Highlighted

Re: Excel formula help

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

Highlighted

Re: Excel formula help

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??

Highlighted

Re: Excel formula help

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

Highlighted

Re: Excel formula help

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?

Highlighted

Re: Excel formula help

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
Highlighted

Re: Excel formula help

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

Highlighted

Re: Excel formula help

Here is the file.  Thanks!

Dean

Highlighted

Re: Excel formula help

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.

Highlighted

Re: Excel formula help

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

Highlighted

Re: Excel formula help

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

Highlighted
Solution

Re: Excel formula help

This is the difficult part!

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

Highlighted

Re: Excel formula help

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

Highlighted

Re: Excel formula help

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

Highlighted

Re: Excel formula help

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

Highlighted

Re: Excel formula help

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.

Highlighted

Re: Excel formula help

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

Re: Excel formula help

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

Highlighted

Re: Excel formula help

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

Highlighted

Re: Excel formula help

Great!  That's just amazing what you can do with Excel.  I need to learn more for sure!

Dean

Highlighted

Re: Excel formula help

Hi,

I have one more request.  Under the tab 6th-8th draft, I wanted to be able to add player requests that are linked with the player name to the player they want.  Is that possible?  So for example for Lauren Carter's team, in round 3, player Carter has a request for player Maunu, but that information is on the contact page.  Can that work?  Thanks!

Dean

Highlighted

Re: Excel formula help

Hi again,

You can use this formula for each request column:

`=IFERROR(INDEX(CONTACT!I:I,MATCH(B4,CONTACT!B:B,0))&"","")`

But it will return the full player name that corresponds to each member in the Contact sheet.

Highlighted

Re: Excel formula help

Oh my goodness.  That's incredible!  Thanks so much!

Dean

I have another problem that I wanted to see if you could help with.  If not, that's ok.  Attached is another Excel sheet.  I want to be able to track teams playing against teams without having to copy paste everything each time.  If I plug in a coaches name in game 1, I want the players rostered.  Then when I put in the points for each player, I want that to put the number into the Totals tab so that can track the players points during the season.  Is this something that Excel can do?  Thanks!

Dean

Highlighted

Re: Excel formula help

Hi,

I'm sorry, but this is not easy!

You need to rearrange the data in Teams sheet to make the players to next coach head.

You can hide these extra columns or make their font color White.

After that, you can use some sort of functions to link them to the Game 1 sheet.

Highlighted

Re: Excel formula help

to signify any formula first type =

Related Conversations
Automatic Selling price calculator
nic140274 in Excel on
0 Replies
Search fuction
Alvaro1990 in Excel on
1 Replies
UNIQUE, FILTER function no more available
Fabien_M in Excel on
5 Replies
Pulling specific data from multiple worksheets into one
AmberDDowney in Excel on
1 Replies
Excel
Svein Tore Kristensen in Excel on
0 Replies
Excel Function Question
Nick_Dirienzo in Excel on
2 Replies