# INDEX MATCH ERROR WHEN CONVERTED TO COLUMN FORMULA

Options
edited 12/01/23

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:

• 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

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

Options

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

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

Options

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?

• 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!