Why doesnt this formula work?

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
but I am, arent I? matching (trying to, anyway) a single column for hte first criteria and a single row for the 2nd.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
ABCDEFGHI
1
2&
3
4
55
Sheet4
Cell Formulas
RangeFormula
B5=MATCH("&",A2:I2,0)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top