Why doesnt this formula work?

erniepoe

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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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