#NOMATCH Error

System

This discussion was created from comments split from: Does INDEX(MATCH) work on a column with a formula?.

BB2791

I'm also having a "NO MATCH" issue when I know the value exists. I tried using the suggestion provided by @Kelly Moore and wrapped one of my reference columns "Full Name" in a VALUE function however now the error went from "NO MATCH" to "INVALID VALUE." Is there anything blantantly wrong with my formula?

=INDEX({Interview Date/Time}, MATCH(VALUE([Full Name]@row), {Full Name - Interview Sheet}, 0))

Kelly Moore

Hey @BB2791

The VALUE function is used when the result is a number. I'm assuming that Full Name is not a numeric field.

Is it possible to share a screenshot of both your source sheet and your target sheet? It would be helpful to see how text in both the Full Name columns are listed. Also, in your source sheet (interview sheet). Could you double click in Full Name cell where it should have produced a match and make sure that the name isn't proceeded by an apostrophe? The apostrophe will only show up when you double click within that cell

Kelly

BB2791

Hi @Kelly Moore

Thanks for getting back so quickly. You're correct that "Full Name" isn't a numeric field. It is a formula column:

=[First Name]@row + " " + [Middle Initial]@row + " " + [Last Name]@row

Here's a screenshot of the Target Sheet (I'm trying to pull in the Interview Date/Time using the "Full Name" column as my common data point)

Screenshot below of the source sheet:

Confirming there's no apostrophe in the source sheet "Full Name" column:

This is the formula I've been trying to use:

=INDEX({Interview Date/Time}, MATCH(VALUE([Full Name]@row), {Full Name - Interview Sheet}, 0))

Without the VALUE function I get a "NO MATCH" error. I have been able to get the formula to work if I create a new column and type in the Full Name in plain text with no formula combining the cells. However, because of the way the information is collected (First Name, Middle Initial, Last Name), I'm not sure of how I can get rid of a formula column since I have to string the values together.

Kelly Moore

Hey @BB2791

Thanks for the screenshots, they are helpful. You can go ahead and remove the VALUE function from your Match - that's what is giving you the INVALID Data error since your data isn't not numeric. I'd like to see the screenshot of the NOMATCH errors once the VALUE is removed

On your target sheet, how are the Full Names being entered?

BB2791

Hi @Kelly Moore

Here's the screenshot of the "NO MATCH" errors now that the VALUE function was removed

The names in the target sheet "Fellowship Application" are entered via form in 3 fields (first name, middle initial, last name):

The names in the source sheet "Fellowship Candidate Interview Availablity" are entered via a different form with only one "Full Name" field

Kelly Moore

Hey @BB2791

The two forms, asking for the free text info in different ways, will probably continue to be a challenge for you. Does your Candidate Availability form instruct the user to enter the Fullname as First Middle Initial Last in the Helper text on the form? Even that isn't full proof

To better mitigate the risk of someone typing different responses, you could

(1) Add the First, Middle Initial, and Last Name fields to your Target sheet and build the same concatenated formula on your Target sheet. This should provide an exact match - assuming they typed correctly.

or

(2) If your smartsheet license gives you access to Datashuttle, build an OFFLOAD workflow for your source sheet then an UPLOAD workflow to populate dropdown lists and change the Full Name column to a dropdown where they select their name. That would guarantee an exact match. This may sound complicated but it is very straight forward to do.

However, in the meantime, substitute this formula on your SOURCE SHEET for your Full Name Formula. This will get rid of the extra space that is currently causing you grief.

=IF([Middle Initial]@row = "", [First Name]@row + " " + [Last Name]@row, [First Name]@row + " " + [Middle Initial]@row + " " + [Last Name]@row)

Kelly

BB2791

Hi @Kelly Moore

Just updating that formula in the target sheet worked! I didn't realize it was the concatenated string that was causing the issue. I also have updated the form fields on the source sheet so it's no longer "Full Name" but mirrors the 3 fields on the target sheet.

Thank you so much for your help with this!