Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    INDEX MATCH ERROR WHEN CONVERTED TO COLUMN FORMULA

    ✭✭
    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:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭

      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.

      Create a cell or column reference in a formula | Smartsheet Learning Center

      https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula
      You can create a reference to an individual cell, a range of cells, or an entire column.


      Hope this helps!

      #SmartsheetSuperstar

      · Share on FacebookShare on Twitter
    • ✭✭

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

      My SOURCE SHEET below

      image.png


      and my DESTINATION SHEET below

      image.png

      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.

      · Share on FacebookShare on Twitter
    • Employee

      Hey @Simone Scarpone

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

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭

      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).

      · Share on FacebookShare on Twitter
    • Employee

      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?

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭
      Genevieve P.

      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.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions