Index Match plus 1 row

Charlotte Patey

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

Find more posts tagged with

Accepted answers

Charlotte Patey

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

All comments

L_123

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.

Charlotte Patey

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)

=VLOOKUP([1st quarter of project deliverable]1, {Next quarter}, 7, false)

Simple but effective

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.