Excel foemula

Question

I want to show up to date stock value on existing spreadsheet both sheets have, part number ?

Answer 1

Use Excel’s Stocks data type so the stock value stays up to date, then pull the latest price with a formula.

1. In one sheet, put the stock identifier (ticker or company name) in a cell, for example A2.
2. Select that cell, then on the Data tab convert it to a Stocks data type.
3. Once converted, you can:
   • Click the stock icon in the cell to see all available fields (like Price, Change, etc.), or
   • Select the cell and press Ctrl+Shift+F5 to open the data card.
4. To show the current stock value in a cell (for example, B2), enter a formula that references the field, such as:
   • =A2.Price This returns the latest price for that stock.
5. If the stock records are in a table, you can use structured references like:
   • =[@Company].Price
6. On the second sheet (that shares the same part number or ticker), reference the first sheet’s price cell, for example:
   • =Sheet1!B2

Notes:

• Stock information is delayed and provided as-is; it updates when Excel refreshes linked data.
• If Excel can’t match the text to a stock, correct spelling or use the selector pane that appears to choose the right company.

---

References:

• Get a stock quote
• How to write formulas that reference data types
• Overview of formulas in Excel

Answer 2

Hi, Lesley Rettie

If both sheets have the same part number, you do not need to retype the stock value manually. You can let Excel match the part number on one sheet and bring back the stock value from the other sheet.

If Sheet1 has the master list, for example part numbers in column A and stock values in column D, and Sheet2 also has part numbers in column A, enter this in Sheet2 where you want the stock value to appear:

=XLOOKUP(A2,Sheet1!A:A,Sheet1!D:D,"Not found")

Then copy the formula down. Excel will look at the part number in A2, find the same part number on Sheet1, and return the stock value from column D.

If your Excel does not support XLOOKUP, use this instead:

=IFERROR(INDEX(Sheet1!D:D,MATCH(A2,Sheet1!A:A,0)),"Not found")

It is recommended that the part numbers be formatted the same way on both sheets, for example all as text or all as numbers. Also check for extra spaces, because “ABC123” and “ABC123 ” may look the same but will not match correctly. If the stock values come from another file or database, use Data > Refresh All after updating the source.

Thank you for your patience in reading, I hope this information has been helpful to you. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.