Formula autofilling values present on another worksheet

Occasional Contributor

Hello, need your kind help please with a formula that I'm trying to create linking cells in two worksheets and involving typing ticker symbols for stocks


Sheet 1:






Sheet 2:


So, basically what I'm trying to achieve is when I type the ticker symbol in Sheet 1 in the cell opposite to "stock" ( call it K5) the Current price (K11) autofills using the prices in Sheet 2.


Thank You!

10 Replies


In cell K11 of sheet 1 you can try

=VLOOKUP('Sheet 1'!$K$5,'Sheet 2'!$A$400:$B$403,2,FALSE)


=INDEX('Sheet 2'!$B$400:$B$403,MATCH('Sheet 1'!$K$5,'Sheet 2'!$A$400:$A$403,0))


@Quadruple_Pawn  Thanks for your reply! unfortunately both are not working : both of them are popping up a window thats asking me to upload some file 




@Quadruple_Pawn also for completion i made a slight update to sheet2 where it pulls this data automatically from a google sheet 






Perhaps the names of your sheets are "Sheet1" and "Sheet2" instead of "Sheet 1" and "Sheet 2". According to your first post i assumed the names are "Sheet 1" and "Sheet 2".


These formulas should work. Otherwise you can adjust the sheetnames according to the actual names of your sheets.

My bad! Thanks alot first 2 formulas worked but only with one ticker: ENPH , when i look up any other ticker it doesn't work


Does it work in the attached sample file? It works as intendend on my computer.

interesting... it does work on this file
quick update, when i add a new ticker and price to your sample file say on row 396 on sheet 2, it doesn't reflect on sheet 1 (this value doesn't match data validations restrictions...



Hey i know where the problem is, but cant fix it! so it allows stocks only from rows 400 to 403, stocks like ENPH, ENVX but not other rows. i can even see it in the code however when i change it to 1 - 2000 for example it doesn't work?

best response confirmed by radwansam_ (Occasional Contributor)


You can adjust the source list of the data validation to:



Row 396 isn't included in the provided formulas. The original example only included rows 400 to 403. However it's easy to adjust the formulas to a range that includes row 396.