Index Match plus 1 row
Hi,
I am trying to get an excel index match formula working and going round in circles.
I have two problems  the first is I am getting a 'no match' when I know the data should match:
=INDEX({Sheet  Date List Range 3}, MATCH([1st quarter of project deliverable]1, {Sheet  Date List Range 3}, 1), 1)
The second is I want the formula to move to the next quarter
So I have a sheet with a row of quarters:
Q 1 2020
Q 2 2020 etc
and my main sheet with columns that look like:
1st Quarter 2nd Quarter
What I am trying to do is say if the quarter in column 1 is Q 1 2020 then the 2nd column needs to be Q 2 2020.
The reason I am doing this is because the quarter in column 1 is a variable.
If anyone can point me in the right direction, I'd really appreciate it.
Many thanks,
Charlotte
Best Answer

Thank you for answering above,
In case it helps anyone else, a colleague helped me with the following solutions which in the end did not need a complicated match query;
Formula 1  user enters a date and and the system automatically search the quarter & year it is in (there is no need to reference another sheet).
="Q " + (INT((MONTH([Start Date]1) + 2) / 3)) + " " + YEAR([Start Date]1)
Formula 2 – the formula recognises the previous quarter and looks up the next quarter (I was able to do this by adding an additional column to the ‘Sheet  Date List’ called “following Q & Y”. I just added the next quarter and with the vlookup formula it worked fine.
=VLOOKUP([1st quarter of project deliverable]1, {Next quarter}, 7, false)
Simple but effective
Answers

Match returns an integer based on the array you are analyzing.index takes that integer and uses it as a location finder to return a value. All you need to do is add a 1 to your match to get the next value beyond. I'd need to see the dataset in order to tell you more about why your match is returning an error.

Thank you for answering above,
In case it helps anyone else, a colleague helped me with the following solutions which in the end did not need a complicated match query;
Formula 1  user enters a date and and the system automatically search the quarter & year it is in (there is no need to reference another sheet).
="Q " + (INT((MONTH([Start Date]1) + 2) / 3)) + " " + YEAR([Start Date]1)
Formula 2 – the formula recognises the previous quarter and looks up the next quarter (I was able to do this by adding an additional column to the ‘Sheet  Date List’ called “following Q & Y”. I just added the next quarter and with the vlookup formula it worked fine.
=VLOOKUP([1st quarter of project deliverable]1, {Next quarter}, 7, false)
Simple but effective
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!