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

Natalia Kataoka

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), "")

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

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.