Populate column with names by project

Andrea Katz

I'm wondering what the best way to do the following would be:

I have about 100 districts and each one is assigned to one of four people. I want to populate a column with the names of the people associated with each district. Would this be best as a nested If statement?

Thanks!

Paul Newcome

Do you mean a column separate from what you have in your screenshot?

Can you provide more details as to how you have everything set up and what your desired end result is? THat will help greatly with finding you a solution.

Generally speaking though... If you have a list that is longer than 3 or 4 selections at most, I tend to lean more towards creating a table and using an INDEX/MATCH combo (a more flexible version of a VLOOKUP).

Andrea Katz

Sorry, I meant populate the District Planner column. It is mostly blank, I manually typed in those names. INDEX/MATCH sounds like it would be a better bet.

I will be typing in the district abbreviations and would like the district planner column to auto-populate with the names of the planners associated with the district.

Paul Newcome

Ok. The first step would be to create your table. In one column you would have your District Abrev. and in the other you would have the Names.

=INDEX(range to pull from, row number, optional column number)

=MATCH(value for comparison, range, match type)

INDEX will pull your data, but you need a numerical value in the second portion to tell the function which row to pull from. Since we are only pulling from a single column, we can ignore that 3rd portion since it is optional.

MATCH produces a numerical value based on where within a range a specific value is found. If you are looking down a single column, this number will be the row number. We will use this to automate the second portion of the INDEX function.

=INDEX(Name:Name, MATCH(Substation@row, [District Abrev.]:[District Abrev.], 0))

Andrea Katz

Hi, I can't get this to work for me. I think maybe VLOOKUP would be a better bet but I can't get that to work either and I'm not sure what I'm doing wrong.

I'm trying to look up by "Substation" column between two tables to populate the name associated with the substation.

for search_value I'm clicking on the first entry in the Substation column in the table I want to populate.

for lookup_table I'm clicking "Reference Another Sheet" and selecting the Substation and Planner columns

for column_num I'm typing 4, as I'm trying to pull the names from the 4th column of the lookup table.

it's giving me #INCORRECT ARGUMENT SET and I can't figure out what I'm doing wrong.

Thanks!

Paul Newcome

@Andrea Katz Try replacing the 4 with a 2.

To use a more flexible INDEX/MATCH, it would look something like this...

=INDEX({Substations Planner Column}, MATCH(Substation@row, {Substations Substation Column}, 0))