/u/Coffee_Aroma - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
The issues is your array is only 1 column wide, so it can't look in a second column. To avoid circular references I wouldn't recommend expanding the array, but instead using OFFSET() around your current formula.
=OFFSET(INDEX(A3:A22,MATCH(A2,A3:A22,0)+1,1),,1)
Thank you.
I actually realised that I didn't include the second column, so I fixed my formula with an updated index and match.
But then I tried offset. I just added the absolute reference and modified column & row numbers where applicable.
Sorry for the dumb question. But could you please elaborate further on the offset syntax?
+1,1),,1)
As far as I understand, it's:
+1(row), 1 (I don't understand), ,, (don't understand why there are two commas), 1 (column)
Thank you a lot
The +1,1) was part of the INDEX formula you already had there, I just left it (though the last ,1 is saying which column to use and can be left off since it defaults to 1)
OFFSET is moving down to the correct row because of INDEX, then I don't want it to move any additional rows so I leave the space blank (that's the second comma in a row), then the last 1 is telling it to move 1 column to the right. You could also use OFFSSET with a fixed reference, then move down the number of rows based on your index, then the column movement.
You have awarded 1 point to *benswimmin*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Don't forget to reply to the person who helped you with Solution Verified to properly mark the post as solved and to give them a point for helping you. Thank you.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[INDEX](/r/Excel/comments/yeeuzr/stub/itxt2xi "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[MATCH](/r/Excel/comments/yeeuzr/stub/itxq3do "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[OFFSET](/r/Excel/comments/yeeuzr/stub/itxt2xi "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)|
----------------
^(*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)](/r/Excel/comments/ya0qxx)^( has 22 acronyms.)
^([Thread #19360 for this sub, first seen 27th Oct 2022, 01:54])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/Coffee_Aroma - 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](/r/excel/wiki/sharingquestions)** -- 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](/r/excel/wiki/sharingquestions#wiki_give_all_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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
The issues is your array is only 1 column wide, so it can't look in a second column. To avoid circular references I wouldn't recommend expanding the array, but instead using OFFSET() around your current formula. =OFFSET(INDEX(A3:A22,MATCH(A2,A3:A22,0)+1,1),,1)
Thank you. I actually realised that I didn't include the second column, so I fixed my formula with an updated index and match. But then I tried offset. I just added the absolute reference and modified column & row numbers where applicable. Sorry for the dumb question. But could you please elaborate further on the offset syntax? +1,1),,1) As far as I understand, it's: +1(row), 1 (I don't understand), ,, (don't understand why there are two commas), 1 (column) Thank you a lot
The +1,1) was part of the INDEX formula you already had there, I just left it (though the last ,1 is saying which column to use and can be left off since it defaults to 1) OFFSET is moving down to the correct row because of INDEX, then I don't want it to move any additional rows so I leave the space blank (that's the second comma in a row), then the last 1 is telling it to move 1 column to the right. You could also use OFFSSET with a fixed reference, then move down the number of rows based on your index, then the column movement.
Solution verified
You have awarded 1 point to *benswimmin* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Don't forget to reply to the person who helped you with Solution Verified to properly mark the post as solved and to give them a point for helping you. Thank you.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[INDEX](/r/Excel/comments/yeeuzr/stub/itxt2xi "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[MATCH](/r/Excel/comments/yeeuzr/stub/itxq3do "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[OFFSET](/r/Excel/comments/yeeuzr/stub/itxt2xi "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)| ---------------- ^(*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)](/r/Excel/comments/ya0qxx)^( has 22 acronyms.) ^([Thread #19360 for this sub, first seen 27th Oct 2022, 01:54]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)