FIELDVALUE function

You can use the FIELDVALUE function to retrieve field data from linked data types like the Stocks or Geography data types. There are easier methods for writing formulas that reference data types, so the FIELDVALUE function should be used mainly for creating conditional calculations based on linked data types.

Note: June 12, 2018: These data types are currently beta features, and only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

Technical details

Syntax

=FIELDVALUE(value, field_name)

The FIELDVALUE function syntax has the following arguments:

  • value – The cell address, table column, or named range that contains a linked data type. 

  • field_name – The name or names of the fields you would like to extract from the linked data type

Description

Examples

In the following basic example, the formula =FIELDVALUE(A2,"Price") extracts the Price field from the stock data type for JM Smucker Co.

Retrieve a company stock price with =FIELDVALUE(A2,"Price")

The next example is a more typical example for the FIELDVALUE function. Here we're using the IFERROR function to check for errors. If there isn't a company name in cell A2, the FIELDVALUE formula returns an error, and in that case we want to display nothing (""). But if there is a company name, we want to retrieve the Price from the data type in A2 with =IFERROR(FIELDVALUE($A2,B$1),"").

Retrieve a company stock price, and ignore errors with =IFERROR(FIELDVALUE($A2,B$1),"")

Note that the FIELDVALUE function allows you to reference worksheet cells for the field_name argument, so the above formula references cell B1 for Price instead of manually entering "Price" in the formula. 

Remarks

If you try to retrieve data from a non-existent data type field, the FIELDVALUE function will return the #FIELD! error. For instance, you might have entered "Prices", when the actual data type field is named "Price". Double-check your formula to make sure you're using a valid field name. If you want to display a list of field names for a record, select the cell for the record, and press Ctrl+Shift+F2.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See also

Stocks and geographic data types

How to write formulas that reference data types

#FIELD! error

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×