Index Match formula returning #NOMATCH, can search type be a formula?

I am attempting to create an Index(Match( formula to pull job names based on a job number, but I am getting a #NO MATCH error. I have used this formula hundreds of times, so I am wondering if this is because my Match search type pulls from a cell that is also a formula?? Any workarounds?

This is my sheet, I want to use the job number to reference another sheet and pull the job name:

In example 1 for 19407, the job is not found under In Progress, but is in Closed.

If it matters, the formula in the Primary (JOBS) column is:

=IFERROR(LEFT(String@row, FIND(",", String@row) - 1), "")

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @Natalia Kataoka

It looks like you formula may be turning the numerical values into a text string, which would then not be seen as a match in your source sheet if the Job No. is numerical.

Try wrapping the VALUE function around that referenced cell, like so:

=INDEX({Master - Closed Jobs - name}, MATCH(VALUE([Primary Column]@row), {Master - Closed Jobs - Job no}, 0))

Let me know if this worked!

Cheers,

Genevieve

All comments

Joseph Nabors

You can Index/Match a cell with a formula. What are you trying to return in the cells? It looks like you haven't specified the column number for the Index function which could be your issue.

Natalia Kataoka

@Joseph Nabors My Index function is referencing an entire column in another sheet.

What I am attempting to do is use the Job No in the main sheet, to pull its respective Job Name from another sheet.

Genevieve P.

Hi @Natalia Kataoka

It looks like you formula may be turning the numerical values into a text string, which would then not be seen as a match in your source sheet if the Job No. is numerical.

Try wrapping the VALUE function around that referenced cell, like so:

=INDEX({Master - Closed Jobs - name}, MATCH(VALUE([Primary Column]@row), {Master - Closed Jobs - Job no}, 0))

Let me know if this worked!

Cheers,

Genevieve

Natalia Kataoka

Thank you, @Genevieve P. !! That worked.

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.