excelavon

Excel Formula Explainer

Paste any Excel formula to see each function, argument and reference in plain English.

2 functions, 2 levels deep

This formula uses 2 functions (IFERROR, VLOOKUP), nested 2 levels deep, and reads A2, Prices!$A$2:$C$50.

  1. VLOOKUP(A2,Prices!$A$2:$C$50,3,FALSE)

    VLOOKUP finds a value in the first column of a table and returns a value from the same row.

    Expects: value, table, column number, match type (FALSE means exact)

    • Argument 1: cell A2
    • Argument 2: the range $A$2:$C$50 on sheet Prices (the $ keeps it fixed when copied)
    • Argument 3: the number 3
    • Argument 4: FALSE
  2. IFERROR(VLOOKUP(A2,Prices!$A$2:$C$50,3,FALSE),"Not found")

    IFERROR returns a fallback when the first argument gives any error.

    Expects: value, fallback

    • Argument 1: the result of VLOOKUP
    • Argument 2: the text "Not found"

Read from the innermost function outward: Excel works out the inner results first.

How to use it

Copy a formula from the formula bar in Excel (click the cell, then select the text after the equals sign in the formula bar) and paste it into the box. The explainer lists every function, innermost first, which is the order Excel works them out, and says what each argument is: a cell, a range, a fixed value or the result of another function.

What it checks

  • Brackets. A missing or extra closing bracket is the most common reason Excel refuses a formula. The explainer says which function is not closed.
  • Quotation marks. Text in a formula must sit inside straight double quotes. A missing closing quote is flagged.
  • Function names. A name it does not recognise is listed. In Excel, a misspelled function returns the #NAME? error.
  • Fixed references. A dollar sign, as in $A$2, keeps a reference from shifting when the formula is copied. The explainer points these out.

Example

=IFERROR(VLOOKUP(A2,Prices!$A$2:$C$50,3,FALSE),"Not found") reads as: look for the value in A2 in the first column of A2:C50 on the Prices sheet, return the matching value from the third column, use an exact match, and if that produces any error, show “Not found” instead.

Limits

The explainer reads the structure of a formula. It does not calculate the result, because it cannot see your workbook. It accepts both comma and semicolon separators (some regional settings use semicolons).

Sources: Function behaviour: Microsoft Support function reference. Results checked against worked examples, and generated formulas evaluated in the open-source HyperFormula engine. Method & sources