OFFSET & MATCH - help with syntax

[tbablue]

=OFFSET(ECWP!$D$6,MATCH('CHART DATA'!$B$2,ECWP!$E$6:$E$887,0),9,COUNTIF(ECWP!$E$6:$E$887,'CHART DATA'!$B$2))

Any help appreciated! I think I've just been staring at this for too long and can't see the wood for the trees.

I'm trying to use OFFSET & MATCH in tandem. Obviously, something is in error; this formula only seems to work when it's been 'dragged' through several rows of the same column - even though all the references are absolute!

Am I just missing something really fundamental? Any ideas? Maybe I just need someone to read the syntax of this argument.

Cheers.

 

[barry houdini]

Can you describe what you're trying to do?

Is this a standadlone formula or part of a bigger formula. As it stands it will return a range of values from column M....is that the right column? To see all the values the formula would need to be array entered in a range, not array entered and copied......

It would make more sense to me if the D6 was E5, then you'd be retrieving values from column N.....

 

[Sal Paradise]

You're returning an array -- not a single value. If you added COUNT() around it, you'd probably get an answer every time.

 

[tbablue]

Thanks for your attention.

'CHART DATA'!$B$2, - is an input cell (in this case a job number)

MATCH('CHART DATA'!$B$2,ECWP!$E$6:$E$887,0) - I'm checking for the first matching instance of the applicable job number (within column E ECWP!) to establish the row

,9, - Yes, column M of ECWP! is my target column from which I want my returns

COUNTIF(ECWP!$E$6:$E$887,'CHART DATA'!$B$2) - to tell me how many applicable returns there are within column M.


I thought the OFFSET & MATCH function in tandem would work just fine. Maybe an array is what I need true enough. Can you help?


I'm searching for value - always from 'CHART DATA'!$B$2

checking that against values from ECWP!$E$6:$E$887

and where a match is found return the corresponding value from ECWP!$M$6:$M$887

Any help offered really would be most welcomed

Cheers

 

[Sal Paradise]

I think what you want is:

=INDEX(ECWP!M:M,MATCH('CHART DATA'!$B$2,ECWP!$E$6:$E$887,0))

Find B2 in E6:E887, and return the corresponding value in column M

 

[barry houdini]

Sounds like you want to return several values from column M where column E matches 'CHART DATA'!$B$2, in which case, assuming you want the first value in A5 put this formula in that cell

=IF(ROWS(A$5:A5)>COUNTIF(ECWP!E$6:E$887,'CHART DATA'!B$2),"",INDEX(ECWP!M$6:M$887,SMALL(IF(ECWP!E$6:E$887='CHART DATA'!B$2,ROW(ECWP!E$6:E$887)-ROW(ECWP!E$6)+1),ROWS(A$5:A5))))

confirm with CTRL+SHIFT+ENTER and copy down

 

[tbablue]

Thanks again guys

Help appreciated. Barry - your understanding of my requirement was correct; I've amended the array formula to suit. Sal - appreciation to you too.

Cheers

 

[tbablue]

as an amendment....

=IF(ROWS(A$5:A5)>COUNTIF(ECWP!E$6:E$887,'CHART DATA'!B$2),"",INDEX(ECWP!M$6:M$887,SMALL(IF(ECWP!E$6:E$887='CHART DATA'!B$2,ROW(ECWP!E$6:E$887)-ROW(ECWP!E$6)+1),ROWS(A$5:A5))))

works perfectly for my initial purpose. However, i'd like to now be able to drag this across columns as well as down rows.

Simply, the array formula, as is, searches for values in 'CHART DATA'!$B$2, checks that value against values from ECWP!$E$6:$E$887 & returns the corresponding value from ECWP!$M$6:$M$887 when a match is found. Using the example detailed above as an instance, I'd like to now drag this array formula into the adjacent column and have it return corresponding values from ECWP!N$6:N$887 instead of ECWP!M$6:M$887, and so on.....(my target column changing as I drag the array formula across columns). I thought it would be as simple as making the column references 'relative' as oppposed to 'absolute' - but they already appear to be absolute so I've kinda run out of knowledge.

Any assistance welcomed and gratefully received.