SOLVED

# How to Autofill one sheet based on a table made in another sheet.

Copper Contributor

# How to Autofill one sheet based on a table made in another sheet.

Hello, I am struggling to find the right formula that will let me fill Sheet 2 Column B based on the value in Column A. In the reference table made in another sheet I have all possible Column A values and the Column B values that match.

What is the best formula to use. It will need to see that the A column cell matches the A column cell in the other sheet and then fill the corresponding B,C,D and E column values. But just filling column B is most important.

Thank you for any help. I know EXCEL is very powerful and has the ability to do stuff like this. Until then I am copy/pasting values across which when some tables have 45 rows and there's 60 tables is a huge waste of time.

Sheet 2

Reference Table

3 Replies
best response confirmed by georgiadavis (Copper Contributor)
Solution

# Re: How to Autofill one sheet based on a table made in another sheet.

I asked my secret contact at Microsoft:

Sure, I can help with that. You can use the VLOOKUP function in Excel to achieve this. Here’s how you can do it:

1. Click on the cell in Sheet 2 Column B where you want the value from the reference table to appear.
2. Enter the following formula: =VLOOKUP(A1, 'ReferenceSheet'!A:E, 2, FALSE)

Here’s what this formula does:

• VLOOKUP(A1, 'ReferenceSheet'!A:E, 2, FALSE) looks for the value in cell A1 of Sheet 2 within column A of the reference sheet. It returns the value in the second column (column B) of the reference sheet for the row where it found a match.

Remember to press Enter after typing the formula. Excel should now display the corresponding value from the reference table in the selected cell of Sheet 2 whenever there’s a matching value in Column A.

If you want to fill columns C, D, and E as well, you can use similar formulas. Just change the third argument in the VLOOKUP function to 3 for column C, 4 for column D, and 5 for column E.

# Re: How to Autofill one sheet based on a table made in another sheet.

Thank you so **bleep** much, you have saved me and my team hours of work!

# Re: How to Autofill one sheet based on a table made in another sheet.

Seems like a problem using table references on another sheet.

1 best response

Accepted Solutions
best response confirmed by georgiadavis (Copper Contributor)
Solution

# Re: How to Autofill one sheet based on a table made in another sheet.

I asked my secret contact at Microsoft:

Sure, I can help with that. You can use the VLOOKUP function in Excel to achieve this. Here’s how you can do it:

1. Click on the cell in Sheet 2 Column B where you want the value from the reference table to appear.
2. Enter the following formula: =VLOOKUP(A1, 'ReferenceSheet'!A:E, 2, FALSE)

Here’s what this formula does:

• VLOOKUP(A1, 'ReferenceSheet'!A:E, 2, FALSE) looks for the value in cell A1 of Sheet 2 within column A of the reference sheet. It returns the value in the second column (column B) of the reference sheet for the row where it found a match.