INDEX/MATCH showing #NOMATCH

JSpears · 02/19/25

I have 2 sheets:

Source Sheet: KI Tasks Report

Target Sheet: MLE Access Management

All three columns in each sheet are text

Formula in MLE Access ProjCode column:

=INDEX({CE+RxE From KI_ProjectCode}, MATCH(ClientID@row, {CE+RxE From KI_ClientID}, 0))

Formula in Project Manager String column:

=INDEX({CE+RxE From KI_PMName}, MATCH(ClientID@row, {CE+RxE From KI_ClientID}, 0))

WHY is it showing #NOMATCH??!!!

Thank you in advance!

Jennifer

Jason Tarpinian · 02/19/25

Double check that your ClientID is a number/text on both, as they are technically two separate values that won't match. A text value will have an apostrophe at the beginning like below and cause the no match error you are talking about. A number won't have an apostrophe.

If for some reason you can't control the text/number format, make a helper column to convert all numbers/text into a text. Something like ="a"+[Number]@row will turn a number into text "a500", and the text to "a500", now you can match two texts. I've found that results in better matches than trying to convert the texts to numbers (using the VALUE function).

Darren Mullen · 02/19/25

@JSpears My firs guess is that in one sheet the ClientID is interpreted as text and in the other it is interpreted as a number.

You can use the value() function to take a number stored as text and operate on it as a number. or, you can take a number stored as a number and use ""+ [Number Column]@row to concatenate a blank space represented by "" to the number and it will become a number stored as text :)

Jason Tarpinian · 02/19/25

Double check that your ClientID is a number/text on both, as they are technically two separate values that won't match. A text value will have an apostrophe at the beginning like below and cause the no match error you are talking about. A number won't have an apostrophe.

If for some reason you can't control the text/number format, make a helper column to convert all numbers/text into a text. Something like ="a"+[Number]@row will turn a number into text "a500", and the text to "a500", now you can match two texts. I've found that results in better matches than trying to convert the texts to numbers (using the VALUE function).

Darren Mullen · 02/19/25

@JSpears My firs guess is that in one sheet the ClientID is interpreted as text and in the other it is interpreted as a number.

You can use the value() function to take a number stored as text and operate on it as a number. or, you can take a number stored as a number and use ""+ [Number Column]@row to concatenate a blank space represented by "" to the number and it will become a number stored as text :)