Explanation

The FORMULATEXT is fully automatic. When given the reference of a cell that contains a formula, it will return the entire formula as text. In the example as shown, the formula:

=FORMULATEXT(C5)

returns the text “=IF(B5>=70,“Pass”,“Fail”)”.

Dealing with errors

The FORMULATEXT function will return the #N/A error when a cell does not contain a formula. To trap this error and display nothing when a cell does not contain a formula, you can use the IFERROR function like this:

=IFERROR(FORMULATEXT(A1),"")

Alternately, you can use ISFORMULA and IF like this:

=IF(ISFORMULA(A1),FORMULATEXT(A1),"")

Checking for specific text

To check a formula for a specific text, you can use the ISNUMBER and SEARCH functions. In the formula below, we are checking a formula in A1 to see if it contains “apple”:

=ISNUMBER(SEARCH("apple",FORMULATEXT(A1)))

The result is either TRUE or FALSE. See this page for a full explanation.

Explanation

The formula in this example converts amounts in USD to other currencies using currency codes and a simple lookup table. The available currencies and exact conversion rates can be adjusted by editing the values in the table on the right. The core of this formula is the VLOOKUP function, configured like this:

=VLOOKUP(D5,xtable,2,0)

The inputs to VLOOKUP are given as follows:

  • lookup_value - the currency code in D5
  • table_array - the currency conversion table in G5:H10, given as the named range xtable
  • col_index_num - 2, because we want to return values from column H
  • range_lookup - 0, because we want VLOOKUP to perform an exact match

For more information on VLOOKUP, see this detailed overview .

In this configuration, VLOOKUP finds the currency in the table and retrieves the conversion rate from column H. The result from VLOOKUP is then multiplied by the value in B5, which is the number of US dollars to convert.

Note: If a currency code does not exist in the table, VLOOKUP will return a #N/A error.

Nested IF equivalent

Note that it is possible to build a formula to do a currency conversion by nesting multiple IF functions together and hardcoding the currency rates directly into the formula like this:

=IF(D5="usd",1,
IF(D5="eur",0.84,
IF(D5="yen",112.35,
IF(D5="can",1.23,
IF(D5="gpb",0.74,
IF(D5="cny",6.59))))))*B5

Note: in the formula above, line breaks have been added for better readability .

I present this option here to illustrate how the VLOOKUP option is superior. It’s a much simpler formula, and the exchange rate values are not stored redundantly in many different formulas. In addition, keeping the lookup table on the worksheet makes it easy to see and edit the exchange rates (in one place) at any time.