- 546K Members
- 2,443 Online
- 652K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Excel formula help

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-24-2018 10:41 AM

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

Labels:

25 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-31-2018 01:56 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-31-2018 08:17 PM - edited 10-31-2018 09:58 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-31-2018 08:46 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2018 08:50 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2018 12:18 PM

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

Dean

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2018 09:13 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2018 11:14 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2018 11:24 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2018 11:38 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018 12:17 AM

SolutionThis is the difficult part!

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

Best Response confirmed by
testing out (Occasional Contributor)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018 12:22 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018 12:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018 12:45 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018 12:58 AM - edited 11-02-2018 12:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018 01:02 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018 09:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018 12:25 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018 01:03 PM

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

Dean

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018 01:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018 07:46 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-29-2018 03:22 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2018 06:15 PM

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.

Please see the attached file.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Related Conversations

UNIQUE, FILTER function no more available

Fabien_M
in
Excel
on
02-21-2020
49
Views

0 Likes

5 Replies

Pulling specific data from multiple worksheets into one

AmberDDowney
in
Excel
on
02-21-2020
26
Views

0 Likes

1 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft