INDEX MATCH ERROR WHEN CONVERTED TO COLUMN FORMULA

Options
Simone Scarpone
edited 12/01/23 in Formulas and Functions

Hi fellows,

I've a Destination sheet (SHEET 1) and a Source sheet (SHEET 2).

In SHEET 1 i've set the following INDEX/MATCH formula for all the cells in col. B:

=INDEX({SHEET 2-Col. B}, MATCH(SHEET1-Col. A, {SHEET 2-Col. A}, 0))

that works exactly as the following VLOOKUP formula (n col. B):

=VLOOKUP(SHEET1-Col. A; SHEET 2-Col. A-B;2;FALSE)

Both INDEX/MATCH and VLOOKUP formulas are row formula.

The problem is that when i convert the INDEX/MATCH formula in COLUMN FORMULA results is error (#INVALID REF). This not happens when VLOOKUP formula is converted in COLUMN FORMULA, results are mantained correctly.

Tags:

Answers

  • Matt.Hartfield
    Options

    Hi Simone,

    I would first look at using @row for your MATCH search value.

    For example

    =INDEX({SHEET 2-Col. B}, MATCH([SHEET1-Col. A]@row, {SHEET 2-Col. A}, 0))

    Unless you intend to have the search value be a fixed item, in which case, you can use an absolute reference.


    Hope this helps!

    #SmartsheetSuperstar

  • Simone Scarpone
    Options

    Hi Matt, thanks for the quick advice. I try to give more details.

    My SOURCE SHEET below


    and my DESTINATION SHEET below

    in the second column of the DESTINATION SHEET i've inserted the following formula:

    =VLOOKUP([Fig_Prof_Seniority]@row; {Costi figure professionali}; 2; false)

    Extending the VLOOKUP formula to the entire column (drag) or convert it into a column formula i obtain the correct result.

    If i insert in the same column the following formula:

    =INDEX({Costi fig. prof. Costo giornaliero}; MATCH([Fig_Prof_Seniority]@row; {Costi fig. prof. Fig Prof}; 0))

    Extending the INDEX/MATCH formula to the entire column (drag) i obtain the correct result, but if i convert it into a column formula results is #INVALID REF.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Simone Scarpone

    Is there a formula error in any of the cells you're referencing?

  • Simone Scarpone
    Options

    Hi Genevieve,

    cells i'm referring to (SOURCE SHEET) are declared values.

    Referenced cell (DESTINATION SHEET) are calculated by formula (moreover no one contains an IFERROR funcion).

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Simone Scarpone

    If the INDEX(MATCH works when you're using it as a cell formula, and none of the referenced cells have errors, the references should be correct and you should be able to create a column formula without an issue.

    For a next step, I would double check these references:

    • {Costi fig. prof. Costo giornaliero} - single column reference to return
    • {Costi fig. prof. Fig Prof} - single column reference to look for a Match in

    Can you confirm they're looking at the right columns?

  • Simone Scarpone
    Options

    I confirm.

    I've fix the problem but i really don't understand the root cause, following these steps:

    I wrote the following cell formula in the DESTINATION SHEET:

    =INDEX({Costi fig. prof. Costo giornaliero}; MATCH([Fig_Prof_Seniority]@row; {Costi fig. prof. Fig Prof}; 0))

    I extended the formula to the entire column (drag) obtaining the correct result, then i converted it into a column formula obtaining as results #INVALID REF.

    I modified directly the column formula referring exactly to the same {Column to return} and {Column with value to match} of the cell formula (and by using the same name for the reference source sheet): values are correct.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!