Help with formula statament with a MATCH.
I am having a problem with the following formula...
=IF(ISERROR(MATCH(0,OFFSET(INDIRECT("'Data sheet'!A" & MATCH(B5,'Data
sheet'!A$1:A$10000, 0)), 2, 1, 1, 40), 0)),
(IF(ISERROR(MATCH(0,OFFSET(INDIRECT("'Data sheet'!A" & MATCH(B5,'Data
sheet'!A$1:A$10000, 0)), 2, 1, 1, 40), 0)), "", TEXT(INDIRECT("'Data sheet'!"
& ADDRESS(2, MATCH(0,OFFSET(INDIRECT("'Data sheet'!A" & MATCH(B5,'Data
sheet'!A$1:A$10000, 0)), 2, 1, 1, 40), 0) + 1,1)), "dd-mmm"))),
TEXT(INDIRECT("'Data sheet'!" & ADDRESS(2, MATCH(0,OFFSET(INDIRECT("'Data
sheet'!A" & MATCH(B5,'Data sheet'!A$1:A$10000, 0)), 2, 1, 1, 40), 0) + 1,1)),
"dd-mmm"))
Once I hit try to accept the formula Excel is highlighting the 5th MATCH
function, but I do not know why.
Essentially this formula is 1 formula repeated twice. The 1st IF(ISERROR is
then followed by the same IF(ISERROR formula on the True part. Here is the
formula...
=IF(ISERROR(MATCH(0,OFFSET(INDIRECT("'Data sheet'!A" & MATCH(B5,'Data
sheet'!A$1:A$10000, 0)), 2, 1, 1, 40), 0)), "", TEXT(INDIRECT("'Data sheet'!"
& ADDRESS(2, MATCH(0,OFFSET(INDIRECT("'Data sheet'!A" & MATCH(B5,'Data
sheet'!A$1:A$10000, 0)), 2, 1, 1, 40), 0) + 1,1)), "dd-mmm"))
Now this works fine, but the larger one doesn't.
Any help would be appreciated.
TIA.
Dave.
|