INDEX MATCH ERROR WHEN CONVERTED TO COLUMN FORMULA

Simone Scarpone · 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.

Matt.Hartfield · 12/01/23

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.

https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula

Hope this helps!

Simone Scarpone · 12/04/23

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. · 12/05/23

Hey @Simone Scarpone

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

Simone Scarpone · 12/05/23

https://community.smartsheet.com/discussion/comment/407944#Comment_407944

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. · 12/07/23

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 · 12/07/23

https://community.smartsheet.com/discussion/comment/408399#Comment_408399

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.

INDEX MATCH ERROR WHEN CONVERTED TO COLUMN FORMULA

Answers

Help Article Resources

Categories