Question description:
This user has given permission to use the problem statement for this
blog.
I have 10 groups of 10 names in a column, and need a formula for Excel to output a name when someone randomly chooses first a group, and then one of the names in that group, but does both things at the same time
Solved by Z. C. in 33 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/06/2018 - 06:22
Hello
Welcome to Gotit pro,
your problem will be highly answered
here.
May I ask how may I help you?
Excelchat Expert
05/06/2018 - 06:23
hello
User
05/06/2018 - 06:23
Hi. I have 100 names, in 10 groups of 10 down column A
User
05/06/2018 - 06:24
like this, for example using two sets
Excelchat Expert
05/06/2018 - 06:24
ok,
User
05/06/2018 - 06:25
i need a formula that pulls a letter from my list based on which group is chosen (option 1) and which letter of that chosen group is chosen (option 2). Right now, it should be "o"
Excelchat Expert
05/06/2018 - 06:25
then what is the option 1 and option 2?
User
05/06/2018 - 06:26
does that make sense?
User
05/06/2018 - 06:27
I'm thinking it's a VLOOKUP problem, but don't know how to account for the variables to narrow it down
Excelchat Expert
05/06/2018 - 06:27
if i select in option1 as 2, the answer was letter B right?
Excelchat Expert
05/06/2018 - 06:27
then if the value on option 2 was 5, the value was o?
Excelchat Expert
05/06/2018 - 06:28
is in it correct?
User
05/06/2018 - 06:28
one sec
User
05/06/2018 - 06:28
option 1 chooses which group
Excelchat Expert
05/06/2018 - 06:28
ok :)
User
05/06/2018 - 06:28
option two chooses which name in the previously chosen group
Excelchat Expert
05/06/2018 - 06:28
oh ok I get it
User
05/06/2018 - 06:28
so 2, 5 should give "o", while 1, 5 should give "e"
User
05/06/2018 - 06:29
cool, sorry
Excelchat Expert
05/06/2018 - 06:29
just five me a minute to work on this
User
05/06/2018 - 06:29
yea, of course
Excelchat Expert
05/06/2018 - 06:31
here it is :)
User
05/06/2018 - 06:32
so it's not a VLOOKUP at all?
Excelchat Expert
05/06/2018 - 06:32
we can use also vlookup, however in vlookup we need to add another column,
Excelchat Expert
05/06/2018 - 06:32
for as we can lookup the value that we want
User
05/06/2018 - 06:33
ok, one small question then
Excelchat Expert
05/06/2018 - 06:33
but in Index function, it was just easily lookup the value in row & column
Excelchat Expert
05/06/2018 - 06:33
ok
Excelchat Expert
05/06/2018 - 06:33
what is it?
User
05/06/2018 - 06:33
my actual sheet will have 100 names, 10 sets of 10.
Excelchat Expert
05/06/2018 - 06:34
ok, could i have that data?
Excelchat Expert
05/06/2018 - 06:34
so we can check for the formula for that one
User
05/06/2018 - 06:35
one sec :)
Excelchat Expert
05/06/2018 - 06:35
ok :)
User
05/06/2018 - 06:36
so the formula still works, which means you are amazing, i just need to know how to make the selection range bigger
Excelchat Expert
05/06/2018 - 06:36
oh it has a number of group
User
05/06/2018 - 06:36
yep, 10 of them
Excelchat Expert
05/06/2018 - 06:37
we will just need here to add additional nested if
Excelchat Expert
05/06/2018 - 06:37
give me a second and I will show it to you :)
User
05/06/2018 - 06:37
you're amazing
Excelchat Expert
05/06/2018 - 06:37
thanks :)
User
05/06/2018 - 06:43
you still with me?
Excelchat Expert
05/06/2018 - 06:44
yes
Excelchat Expert
05/06/2018 - 06:44
im near on the solution now
User
05/06/2018 - 06:44
oh wow, ok. I knew this was going to be complicated, sorry!
Excelchat Expert
05/06/2018 - 06:45
done :)
Excelchat Expert
05/06/2018 - 06:45
have you seen it now?
Excelchat Expert
05/06/2018 - 06:46
r u still there?
User
05/06/2018 - 06:46
yep
Excelchat Expert
05/06/2018 - 06:47
ok, could you check it now :)
Excelchat Expert
05/06/2018 - 06:47
it this was the output that you want
User
05/06/2018 - 06:47
i am, and I'm getting a reference error
Excelchat Expert
05/06/2018 - 06:47
on which?
User
05/06/2018 - 06:48
wait, nevermind. I just copied what was in the sample screen instead of trying to copy and paste separately
Excelchat Expert
05/06/2018 - 06:48
ok :)
User
05/06/2018 - 06:48
one sec, just testing
Excelchat Expert
05/06/2018 - 06:48
ok :)
User
05/06/2018 - 06:51
it's working great :)
Excelchat Expert
05/06/2018 - 06:51
oh great :)
Excelchat Expert
05/06/2018 - 06:51
I'm glad it works well
Excelchat Expert
05/06/2018 - 06:52
what I did here, I've set a index function for each group
Excelchat Expert
05/06/2018 - 06:52
like this one
User
05/06/2018 - 06:52
i see that. that seems like a simple solution
Excelchat Expert
05/06/2018 - 06:52
IF(E3=1,INDEX(A1:A10,F3,1)
User
05/06/2018 - 06:52
and then nested the ifs into one formula
Excelchat Expert
05/06/2018 - 06:53
yes correctly :)
Excelchat Expert
05/06/2018 - 06:53
Have I answered your question?
User
05/06/2018 - 06:53
perfect. so what do i do now? as in, do i give you feedback or something?
Excelchat Expert
05/06/2018 - 06:54
yes, once we ended our session, you may send me rating and your feedbeack :)
User
05/06/2018 - 06:54
great, will do
Excelchat Expert
05/06/2018 - 06:54
5 star if i satisfied your question
Excelchat Expert
05/06/2018 - 06:55
:)
User
05/06/2018 - 06:55
10 star
Excelchat Expert
05/06/2018 - 06:55
wow thanks so much :)
Excelchat Expert
05/06/2018 - 06:55
Thanks for using Got It Pro. Please give your kind feedback for our service.
On your window, kindly don't just close the window, there is an end button there so you could end the
session properly & message will appear to ask for the feedback :) Have a good day ahead ^_^
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
