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

[georgiadavis]

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 · Accepted solution]

@georgiadavis 

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.

Please replace 'ReferenceSheet' with the actual name of your reference sheet.

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.

[#3]

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

[#4]

@Detlef_Lewin 

Seems like a problem using table references on another sheet.