SOLVED

# 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 2 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.