Does INDEX(MATCH) work on a column with a formula?

Caroline Henne

I am attempting to use the INDEX(MATCH) formula with the range being a column that is calculated with a formula. I keep getting a #NOMATCH message - can MATCH only work on a column that has actual values (i.e., not values calculated from a formula)?

I am trying to use this formula:

=INDEX({Billing Code}, MATCH([Identifier CALCULATED]@row, {List 1 Identifier}))

where the [Identifier CALCULATED] column is generating an Identifier by pulling the first 6 characters from a longer code with this column formula:

=LEFT([Project Code]@row, 6)

Kelly Moore

Hey @Caroline Henne

There can be NoMatches sometimes if one term within the MATCH is a number and the other is a text string.

Try forcing your calculated term to a number by wrapping it in a VALUE function. Also we will add a zero to the end of the MATCH function to indicated unsorted data. In the countless Index/Matches I've done, I always add the zero.

=INDEX({Billing Code}, MATCH(VALUE([Identifier CALCULATED]@row), {List 1 Identifier},0))

Does this work for you?

Kelly

Kelly Moore

Hey @Caroline Henne

There can be NoMatches sometimes if one term within the MATCH is a number and the other is a text string.

Try forcing your calculated term to a number by wrapping it in a VALUE function. Also we will add a zero to the end of the MATCH function to indicated unsorted data. In the countless Index/Matches I've done, I always add the zero.

=INDEX({Billing Code}, MATCH(VALUE([Identifier CALCULATED]@row), {List 1 Identifier},0))

Does this work for you?

Kelly

Caroline Henne

Thank you! Yes, this worked. Really appreciate it.