# Why doesnt this formula work?

#### erniepoe

##### Active Member
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))

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

##### Well-known Member
You can only match to a single row or column

#### erniepoe

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

##### Well-known Member

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.

Last edited:

#### erniepoe

##### Active Member

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

##### Well-known Member
D9:Z9 is one dimentional, do you need to match exactly ie \$D\$9:\$Z\$9,0)

#### erniepoe

##### Active Member

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

success! thank you very much Dryver!

#### Dryver14

##### Well-known Member
No Problem, Sheetspread did point this out in post 4

#### erniepoe

##### Active Member
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?

##### Well-known Member
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:

Last edited:

Replies
2
Views
115
Replies
0
Views
175
Replies
0
Views
33
Replies
4
Views
155
Replies
2
Views
220