Excel query - auto populating a cell based on different selections from a dropdown

Question

Hi all,

I've seen similar requests to this but am struggling to make sense of them for my specific requirement.

I have a list of team members in one column in a dropdown. Each person belongs to a specific team, and in the column directly to the right, i need the team they belong to to auto populate, based on the team member i have selected from the dropdown.

I've added screen shots of the page where all the data needs to be, highlighted the column that needs to auto fill in red. I've also added the list of the teams which I've currently listed on another tab.

If anyone could provide a quick step by step of how to achieve this I would really appreciate it.

Thank you!

Jo

Answer 1

You have a blank cell in C4 so it can't find the matching value. The value in C4 must match perfectly one of the values in the first column of the table array containing the names and team names.

If you want to insert the formulas before inserting the names then you need to handle the not found something like the following formula where it returns a zero length string if errors.

=IFERROR(VLOOKUP(C4,Teams!$A:$B,2,FALSE),"")

Answer 2

All working now, thanks so much for your help!

Jo

Answer 3

Hello,

Thank you so much for your reply.

Unfortunately, I'm getting an N/A come up when I try what you've suggested (may have done something wrong. See screen shots:

Also tried changing C4 to D4 and this just brought up and 0 in the Sales column.

Thank you.

Jo

Answer 4

Your Teams worksheet needs to be set up with the Team names beside each person and then in the Sales column use Vlookup.

the formula in cell D4 would be the following:

=VLOOKUP(C4,Teams!$A:$B,2,FALSE)

Screen Shot of Teams worksheet

Screen Shot of Worksheet with Names and Vlookup formula (The formula I have shown in cell F4 is what you insert in cell D4)