Why doesnt this formula work?

[erniepoe]

Hi All,

The following formula returns a 0 value, but the second formula works. The only difference is that the first index column has been changed only to column D, and the same with the second match function.

The answer I am looking for is in column D, but i would like to drag the formula over (and the first match (C1) would change, and have the formula find the value from that particular column, without too much manipulation.

Why doesnt the first formula work, and is there anything I can do to make it so?

Thanks!


=INDEX('[Copy of Deferred COGS Leased Assets 1217.xlsx]1651_SCF I Leased'!$D$11:$Z$107,MATCH(B2,'[Copy of Deferred COGS Leased Assets 1217.xlsx]1651_SCF I Leased'!$C$11:$C$107,0),MATCH(C1,'[Copy of Deferred COGS Leased Assets 1217.xlsx]1651_SCF I Leased'!$D$9:$Z$9))



=INDEX('[Copy of Deferred COGS Leased Assets 1217.xlsx]1651_SCF I Leased'!$D$11:$D$107,MATCH(B2,'[Copy of Deferred COGS Leased Assets 1217.xlsx]1651_SCF I Leased'!$C$11:$C$107,0),MATCH(C1,'[Copy of Deferred COGS Leased Assets 1217.xlsx]1651_SCF I Leased'!$D$9:$D$9))

 

[sheetspread]

You can only match to a single row or column

 

[erniepoe]

but I am, arent I? matching (trying to, anyway) a single column for hte first criteria and a single row for the 2nd.

 

[sheetspread]

Sorry I misread:

MATCH(C1,'[Copy of Deferred COGS Leased Assets 1217.xlsx]1651_SCF I Leased'!$D$9:$D$9) is a single cell, should be a range

MATCH(C1,'[Copy of Deferred COGS Leased Assets 1217.xlsx]1651_SCF I Leased'!$D$9:$Z$9) is a 1 dimensional range, don't know why I said otherwise.

Also you've omitted the match type, so it assumes 1, might need a 0 instead.

 

[erniepoe]

MATCH(C1,'[Copy of Deferred COGS Leased Assets 1217.xlsx]1651_SCF I Leased'!$D$9:$Z$9)

is 2 dimensional. Match() only accepts a row or column range. Is there a header you can refer to?

the headers are in row 9, which would match to what I have in cell C1 in the other sheet.

 

[Dryver14]

D9:Z9 is one dimentional, do you need to match exactly ie $D$9:$Z$9,0)

 

[erniepoe]

D9:Z9 is one dimentional, do you need to match exactly ie $D$9:$Z$9,0)

success! thank you very much Dryver!

 

[Dryver14]

No Problem, Sheetspread did point this out in post 4

 

[erniepoe]

also, the formula is tripping up because one of the columns has an & sign in it, if i change the header to read "and" the formula works just fine. is there a way to alter the formula so that the ampersand (sp?) wont kill it?

 

[sheetspread]

Is & a reserved character? If so, it would require a ~ in front (which you can add with the & symbol i think)


But this works so I'm not sure:

Excel 2010
	A	B	C	D	E	F	G	H	I
1
2					&
3
4
5		5
Sheet4

Cell Formulas
Range	Formula
B5	=MATCH("&",A2:I2,0)