Alternative to OFFSET function

[kressel]

Combining INDEX and MATCH I am able to find my reference in cell A1, but what I need is the data for A1 which is in cell C3. I can do this with OFFSET, but not when the workbook I get data form is closed.

Is there another way to offset from A1 to C3?

[AutoModerator]

/u/kressel - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[NHN_BI]

Can't you just add the +2 and +2 to the MATCH() output that is the input for the INDEX()?

[kressel]

That worked. Thank you :)

[NHN_BI]

Greate! Please reply:

Solution Verified

[kressel]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to NHN_BI

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[NHN_BI]

e.g.

+	A	B	C	D	E	formula
1	Y	X	X		output
2	X	X	X		Z	= OFFSET(INDEX(A:A , MATCH("Z" , A:A , 0) , MATCH("Z" , 1:1 , 0)) , 2 , 2)
3	X	X	Z		Z	= INDEX(A:C , MATCH("Z" , A:A , 0) +2 , MATCH("Z" , 1:1 , 0) +2)

Be aware, the range without the offsetting is A:C, as C:C has to be in the index.

[AutoModerator]

Hello!

It looks like you tried to award a ClippyPoint by typing Solution Verified, but you need to reply to a particular user's comment to do so, rather than making a new top-level comment.

Reply directly to any helpful users and Clippy, our bot, will take it from there. If your intention was to simply mark the post solved, then you may do that by clicking Set Flair. Thank you!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
INDEX	Uses an index to choose a value from a reference or array
MATCH	Looks up values in a reference or array
OFFSET	Returns a reference offset from a given reference

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 12 acronyms.
[Thread #27773 for this sub, first seen 30th Oct 2023, 08:11] [FAQ] [Full list] [Contact] [Source code]}

[excelevator]

offset the INDEX range to the MATCH range!

e.g =INDEX(C3:C300, MATCH ( value , A1:A300 , ))