Applying OFFSET to a formula

Youngi

New Member
Joined
Sep 12, 2019
Messages
3
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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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:
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
If that works just use E4 instead of D4 in the index. You dont need the offset then.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top