INDEX/MATCH to enter a number based off a month name.

I'm trying to get an index match formula to enter the number of a quarter based of the month completed name between my working sheet and a helper sheet:

=IFERROR(INDEX({SourceMonthName}, MATCH(MONTH[Month Complete]@row), {SourceQuarterNUmber}, 0), "")

I keep getting #unparseable

Find more posts tagged with

Accepted answers

William Gray

Thank you for your help. I swapped the sheet references around and it worked.

=INDEX({SourceQuarterNumber}, MATCH([Month Complete]@row, {SourceMonthName}, 0))

All comments

KPH

Hi @William Gray

Try moving the parenthesis that closes the MATCH function

From

=IFERROR(INDEX({SourceMonthName}, MATCH(MONTH[Month Complete]@row), {SourceQuarterNUmber}, 0), "")

=IFERROR(INDEX({SourceMonthName}, MATCH(MONTH[Month Complete]@row, {SourceQuarterNUmber}, 0)), "")

You might also need IFERROR around the MONTH function to return 0 if the cell is blank.

William Gray

Still getting #unparseable

KPH

Are your Month Complete and SourceMonthName columns date format? If doesn't sound like they are. If these are not dates, remove the MONTH function from the formula. You can match the text in the Month Complete with the text in SourceMonthName

=IFERROR(INDEX({SourceMonthName}, MATCH([Month Complete]@row, {SourceQuarterNUmber}, 0)), "")

William Gray

Thank you for your help. I swapped the sheet references around and it worked.

=INDEX({SourceQuarterNumber}, MATCH([Month Complete]@row, {SourceMonthName}, 0))

KPH

There we go! Glad I could help (a little).

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.