Why doesnt this formula work?

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
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))
 

Some videos you may like

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

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
but I am, arent I? matching (trying to, anyway) a single column for hte first criteria and a single row for the 2nd.
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
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.
 
Last edited:

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330

ADVERTISEMENT

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
Joined
Mar 22, 2010
Messages
2,396
D9:Z9 is one dimentional, do you need to match exactly ie $D$9:$Z$9,0)
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">&</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=MATCH(<font color="Blue">"&",A2:I2,0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,230
Members
409,857
Latest member
KailuaTown
Top