Oct 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
Oct 24 2018 10:55 AM
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
Oct 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
Oct 31 2018 08:17 PM - edited Oct 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??
Oct 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
Nov 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?
Nov 01 2018 12:18 PM
Nov 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
Nov 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.
Nov 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
Nov 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
Nov 02 2018 12:17 AM
SolutionThis is the difficult part!
Anyway, I've updated the workbook with another formula.
Nov 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
Nov 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
Nov 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
Nov 02 2018 12:58 AM - edited Nov 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.
Nov 02 2018 01:02 AM
Nov 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
Nov 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
Nov 02 2018 12:17 AM
SolutionThis is the difficult part!
Anyway, I've updated the workbook with another formula.