# OFFSET & MATCH - help with syntax

#### tbablue

##### Active Member
=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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### barry houdini

##### MrExcel MVP
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.....

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

#### tbablue

##### Active Member

'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

##### Well-known Member
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

##### MrExcel MVP
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

##### Active Member
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

##### Active Member
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.

Replies
12
Views
394
Replies
5
Views
168
Replies
13
Views
403
Replies
2
Views
205
Replies
1
Views
238

1,191,373
Messages
5,986,271
Members
440,015
Latest member
knijgh

### 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.

### Which adblocker are you using?

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

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