copying a formula down wards
Mike,
many thanks for your help.
UKMAN1
"Mike H" wrote:
Hi,
Try this ARRAY formula. ARRAY enter it and it will return the first match,
drag down for the second etc. It will return an error if there isn't a second
match so you could wrap the whole thing =isserror(formula etc
=OFFSET(IF(ROWS(B$7:B7)=COUNTIF(Proj_code,$E$1),I NDEX($F$7:$F$198,SMALL(IF(Proj_code=$AQ$6,ROW(Proj _code)-ROW($E$1)+1),ROWS(B$7:B7))),""),-6,0)
This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"UKMAN" wrote:
=INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0))
the above formula works in that it shows the first match.
As there could be mulitpule records what do I need to do to so when I copy
it down it will bring the next match or 0 if no further matches in the table
it is searching?
Many thanks
UKMAN1
|