Applying OFFSET to a formula

Youngi

New Member
Hi,

I havent been able to find an answer to my query, and I have tried so many variations to make OFFSET (left by one cell) work but I am not having any luck.

My original formula is:
=INDEX(General2!$D$4:$AAA$400,MATCH($I5,General2!$B$4:$B$400,0),MATCH($E$7,General2!$D$2:$AAA$2,0))

I have tried to apply the OFFSET part of the formula and the closest I am getting is:
=INDEX(OFFSET(General2!$D$4:$AAA$400,MATCH($I5,General2!$B$4:$B$400,0),MATCH($E$7,General2!$D$2:$AAA$2,0)),,1)

However, the issue with this is that it is offsetting both to the left and down by one cell each. I am trying to just OFFSET by one cell to the left, and not down.

Any help would be greatly appreciated!
 

Fluff

MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Not sure if I've understood correctly, but does this do what you need?
=INDEX(General2!$D$4:$AAA$400,MATCH($I5,General2!$B$4:$B$400,0),MATCH($E$7,General2!$D$2:$AAA$2,0)-1)
 
Last edited:

Youngi

New Member
Unfortunately that doesnt work and I get an error message saying too few arguments.

Let me give an example. I have two sheets, one called 'General1' and another called 'General2'.

On 'General2' I have a table and I just need to find the data to the side of the data that is found by using this formula:
=INDEX(General2!$D$4:$AAA$400,MATCH($I5,General2!$B$4:$B$400,0),MATCH($E$7,General2!$D$2:$AAA$2,0))

This finds the data inside the table, but how do I offset this result by 1 either to the left or to the right?? Like I said, I have the following formula:
=INDEX(OFFSET(General2!$D$4:$AAA$400,MATCH($I5,General2!$B$4:$B$400,0),MATCH($E$7,General2!$D$2:$AAA$2,0)),,1)

But the OFFSET part that I have added not only OFFSETS to the right* (*correction from original post) but it also OFFSETS it down by 1 cell as well. For example, the first formula has found data in cell F6, but the OFFSET part isn't finding cell G6, it is finding cell G7.

Hope this helps.
 

Youngi

New Member
I have been playing around with it more today and I have figured it out. I thought I would leave the answer here for other people.

=INDEX(OFFSET(General2!$D$4:$AAA$400,,1),MATCH($I5,General2!$B$4:$B$400,0),MATCH($E$7,General2!$D$2:$AAA$2,0))

Depending where you position the OFFSET value and the ',,1)' all depends on how the OFFSET affects the rows and/or columns.

Thanks for your help!
 

Some videos you may like

This Week's Hot Topics

Top