Formula syntax Confirmation (chandoo Forums)

[vijay.vizzu]

Dear All,


In one of the post in Chandoo's Blog. i have just watched one blog, and it has formula like =Index(return,Match(1,(1stRange=criteria1)*(2ndRange=criteria2)*(3rdRange=criteria3),0)), but when i am trying this formula i am getting N/A error. Kindly tell me is this formula is correct.


http://chandoo.org/wp/2009/08/24/excel-formulas-29-tips/

 

[xld]

You need to array-enter, that is hit Ctrl-Shift-Enetre after typing in the formula, not just Enter.

 

[vijay.vizzu]

Dear Xld,


I already entered with Ctrl+Shift+Enter to make it as array formula. But same error appears

 

[vijay.vizzu]

my data table

NameDeptt	[code]RegionSales

[code]VijayCDC	[code]SJP3378

[code]KumarPE	[code]FBD3455

[code]SureshPE	[code]FBD3199

[code]Naveen

ME SJP[/code]1722

Vijay[/code]PE SJP[/code]3233

Vijay[/code]CDC SJP[/code]4531


required

Name[/code]Deptt Region[/code]Sales

Vijay[/code]CDC `SJP #N/A

 

[Luke M]

If I enter this as an array it works.

=INDEX(D2:D10,MATCH(1,(A2:A10="Vijay")*(B2:B10="CDC")*(C2:C10="SJP"),0))


It's only the non-array version that seems to give a N/A error.

 

[vijay.vizzu]

Dear LukeM,


In your formula = =INDEX(D2:D10,MATCH(1,(A2:A10="Vijay")*(B2:B10="CDC")*(C2:C10="SJP"),0))

you have used text values as Vijay, CDC,SJP and in my formula it is in cell reference (relative). So i think this was the problem in my case.


Thanks

Vijay