Auto Populate a Cell Using Data from Another Sheet

Question

Hi,

I have a list of Organisations and Contacts from my team on Sheet 1.

On Sheet 2 I utilise a dropdown for contacts to list the Organisation they have had a meeting with. When they select the Organisation from the dropdown I would like it to AutoPopulate the Contact in the next column.

What is the simplest way to do this please?

Answer 1

Hi,

You can try this:

To lock the range Sheet1!A2:B100 in your VLOOKUP formula so it doesn't change when you drag it down, you need to use absolute references by adding dollar signs ($) to the range. This fixes the row and column references.

Modify your VLOOKUP formula like this: =VLOOKUP(C2,Sheet1!$A$2:$B$100,2,FALSE)

Explanation:

• $A$2:$B$100: The dollar signs lock both the columns (A and B) and rows (2 and 100), so the range stays constant when you drag the formula.
• C2: This remains a relative reference, so it adjusts to C3, C4, etc., as you drag down.
• 2: Specifies the second column in the range for the return value.
• FALSE: Ensures an exact match.

Steps to Apply:

1. Update the formula in the first cell to include the $ signs as shown above.
2. Drag the formula down, and the range Sheet1!$A$2:$B$100 will remain fixed.
3. Verify that the formula in cell 25 (or any other row) still references Sheet1!$A$2:$B$100.

I hope the information helps!

If you would like more information or support, please let me know!

Schneider-P MSFT | Microsoft Community Support Specialist

Answer 2

Hi,

We would greatly appreciate your feedback to know If the issue has been resolved, kindly mark it as an answer and upvote. This helps others in the community facing similar problems. Your understanding and patience are highly valued as we strive to provide the best possible service.

Thank you very much for your cooperation and understanding.

Answer 3

fixed it. Thank you

Answer 4

Thank you so much for the response, this has partially worked, and I cannot thank you enough.

My next issue is this:

within the VLOOKUP formula, I have dragged and filled the formula in cells as you suggested, but as I have dragged down it has changed the search area in this section, once again adding a row each time, I need the LOOKUP area to remain the same?

• Sheet1!A2:B100: The range in Sheet 1 where Column A has organizations and Column B has contacts.

By the time I reach CELL 25 it now reads =VLOOKUP(C2,'Totals don't edit or delete!'!**A27:B692,**2, FALSE) NOT A2:B667 is there a way to lock this part of the formula?

Thanks in advance.

Answer 5

Dear LucyNot84, 

Welcome to the community!    

Here are some suggestions to troubleshoot your issue:   

First of all, you should Set up the Dropdown in Sheet 2:  

• Select the cell in Sheet 2 where you want the dropdown.
• Go to Data > Data Validation > List, and set the source to the range of organizations in Sheet 1 (e.g., =Sheet1!A2:A100).

Then, you Add the VLOOKUP Formula in Sheet 2:  

• In the cell where you want the Contact to appear (e.g., B2), enter the following formula:  =VLOOKUP(A2, Sheet1!A2:B100, 2, FALSE)

with:  

• A2: The selected organization from the dropdown in Sheet 2.
• Sheet1!A2:B100: The range in Sheet 1 where Column A has organizations and Column B has contacts.
• 2: Returns the value from the second column of the range (Contact name).
• FALSE: Ensures an exact match for the organization name.

After that, you can Copy the Formula Down:  

• Drag the formula in B2 down for all rows where you have dropdowns in Column A.

Besides that, you can find more information at the following link:VLOOKUP function - Microsoft Support 

Please understand that our initial reply may not always immediately resolve the issue. However, with your help and more detailed information, we can work together to find a solution. 

I hope the information helps! If there is any update or I misunderstand you, please feel free to let me know. I will continue to assist you. Your understanding and co-operation are highly appreciated. 

Best Regards,       
Schneider-P MSFT | Microsoft Community Support Specialist