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 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.
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.
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?
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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?
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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!
