INDEX MATCH ERROR WHEN CONVERTED TO COLUMN FORMULA
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 2Col. B}, MATCH(SHEET1Col. A, {SHEET 2Col. A}, 0))
that works exactly as the following VLOOKUP formula (n col. B):
=VLOOKUP(SHEET1Col. A; SHEET 2Col. AB;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.
Answers

Hi Simone,
I would first look at using @row for your MATCH search value.
For example
=INDEX({SHEET 2Col. B}, MATCH([SHEET1Col. A]@row, {SHEET 2Col. 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

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.

Hey @Simone Scarpone
Is there a formula error in any of the cells you're referencing?

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

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?

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
Categories
Check out the Formula Handbook template!