SOLVED

Flash Fill VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-2916909%22%20slang%3D%22en-US%22%3EFlash%20Fill%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2916909%22%20slang%3D%22en-US%22%3E%3CP%3EI%20enter%20a%203%20letter%20reference%20in%20Column%20E...%3CBR%20%2F%3EColumn%20F%20performs%20a%20VLOOKUP%20and%20reports.%3CBR%20%2F%3EColumn%20P%20has%20a%20basic%20%22%3D%22%20rule%20applied%20from%20E.%3CBR%20%2F%3E%3CBR%20%2F%3EAfter%20a%20few%20rows%20of%20doing%20this%2C%20my%20worksheet%20has%20been%20automatically%20populating%20column%20F%20and%20P%20correctly%20whenever%20I%20enter%20data%20into%20column%20E.%26nbsp%3B%3CBR%20%2F%3EBoth%20Column%20Column%20F%20and%20P%20are%20blank%2C%20(with%20no%20formula%20entered)%20until%20Column%20E%20is%20populated.%3CBR%20%2F%3EI%20assume%20this%20is%20a%20Flash%20Fill%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ERecently%2C%20this%20has%20stopped%20working%20for%20Column%20F.%26nbsp%3B%20Column%20P%20is%20still%20working.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20seem%20to%20find%20a%20solution%20to%20get%20it%20working%20again.%3CBR%20%2F%3EI%20have%20checked%20the%20advanced%20options%20to%20ensure%20tick%20boxes%20are%20enabled.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20provide%20any%20suggestions%20or%20solutions%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20aware%20of%20the%20work-around%20to%20drag-fill%20the%20columnetc%2C%20however%20I%20would%20like%20to%20know%20why%20it%20has%20stopped%20working%20and%20if%20I%20can%20activate%20it%20again!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(I%20am%20using%20Version%202109%20within%20MS%20Office%20Professional%20Plus%202016)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2916909%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2920954%22%20slang%3D%22en-US%22%3ERe%3A%20Flash%20Fill%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2920954%22%20slang%3D%22en-US%22%3EThis%20is%20an%20autocomplete%20option.%20If%20you%20convert%20your%20range%20(including%20columns%20F%20and%20P)%20to%20a%20table%20and%20make%20sure%20all%20rows%20of%20the%20table%20for%20columns%20F%20and%20P%20contain%20the%20formula%2C%20it%20will%20continue%20to%20work%20as%20you%20add%20new%20data%20to%20the%20table.%3CBR%20%2F%3E%3CBR%20%2F%3ETip%3A%20don't%20make%20your%20table%20larger%20than%20the%20data.%20As%20soon%20as%20you%20type%20new%20data%20immediately%20beneath%20or%20to%20the%20right%20of%20the%20table%2C%20the%20table%20expands%20automatically.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2921551%22%20slang%3D%22en-US%22%3ERe%3A%20Flash%20Fill%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2921551%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%3CBR%20%2F%3EThanks%20so%20much%20for%20your%20reponse.%20I%20have%20taken%20those%20steps%20and%20unfortunately%20it%20still%20will%20not%20continue%20downwards.%20Even%20the%20table%20does%20not%20expand%20when%20I%20go%20to%20the%20next%20row%20down.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20did%20try%20your%20method%20on%20a%20brand%20new%20workbook%20and%20it%20works%20fine%2C%20so%20it%20must%20be%20something%20wrong%20with%20my%20current%20workbook%2C%20rather%20than%20a%20general%20Excel%20error...%20or%20my%20error%20lol%3C%2FLINGO-BODY%3E
New Contributor

I enter a 3 letter reference in Column E...
Column F performs a VLOOKUP and reports.
Column P has a basic "=" rule applied from E.

After a few rows of doing this, my worksheet has been automatically populating column F and P correctly whenever I enter data into column E. 
Both Column Column F and P are blank, (with no formula entered) until Column E is populated.
I assume this is a Flash Fill?

 

Recently, this has stopped working for Column F.  Column P is still working.

 

I cannot seem to find a solution to get it working again.
I have checked the advanced options to ensure tick boxes are enabled.

 

Can anyone provide any suggestions or solutions please?

 

I am aware of the work-around to drag-fill the columnetc, however I would like to know why it has stopped working and if I can activate it again!

 

(I am using Version 2109 within MS Office Professional Plus 2016)

4 Replies
This is an autocomplete option. If you convert your range (including columns F and P) to a table and make sure all rows of the table for columns F and P contain the formula, it will continue to work as you add new data to the table.

Tip: don't make your table larger than the data. As soon as you type new data immediately beneath or to the right of the table, the table expands automatically.
@Jan Karel Pieterse
Thanks so much for your reponse. I have taken those steps and unfortunately it still will not continue downwards. Even the table does not expand when I go to the next row down.

I did try your method on a brand new workbook and it works fine, so it must be something wrong with my current workbook, rather than a general Excel error... or my error lol
best response confirmed by CharlieM145 (New Contributor)
Solution
Try this: Select a cell in either non-functioning column that contains the correct formula. Press control+spacebar. Then hit F2 followed by control+Enter. That should convince the table that that formula "belongs" in that column. Repeat for any other column with a formula.
Oh my gosh, you nailed it!!
Thank you so much!
All working perfectly, so happy, thank you x