How to compare two columns for mismatch with the reference file

Marina

I have a Reference file where Code Name and Code Number (these are not the real names of the fields but example). User selects Code Name from the form, where Code Number automatically populates the main file. I have few instances where the Code Name doesn't match Code Number and I can't figure out why. There is no one can choose another Code Number in the main file and no information is available under cell history. I would like to create some formula and then conditional formatting that would compare Code Name and Code Number based on the reference file and them highlight if they are mismatch on the main file. Here is an example. Or do you know why VLOOKUP is not working?

Reference File where each Code Name has their Code Number.

In Master file the user only selects Code Name on the form, but Code Number should be populated through VLOOKUP formula and it should look for the correct Code Number (sometimes it populates different Code Number). Here is what it looks like on the main file.

Melissa Boehl

@Marina

I would highly recommend using an index, match formula. Try this:

=INDEX({Code Number},MATCH([Code Name]@row,{Code Name},0))

Marina

If I have formula set up in main file for "Code Number" field

as

=IFERROR(VLOOKUP([Code Name]@row, {reference file}, 2, false), " incorrect selection")

where "2" is a second column in the reference table as it represents "Code Number"

how do I update this formula?

=INDEX({Code Number},MATCH([Code Name]@row,{Code Name},0))

Marina

@Melissa Boehl please see my question above

Melissa Boehl

@Marina I believe you are asking how to incorporate the iferror into the index/match. If so, try this...

=iferror(INDEX({Code Number},MATCH([Code Name]@row,{Code Name},0)),"Incorrect Selection")

Add this formula as a column formula in your master sheet to the code column. Reference the {Code Number} to the code number field in the reference sheet and reference the {Code Name} to the Code Name column in the reference sheet. The [code name]@row is referencing the code name in the master file.

Marina

@Melissa Boehl thank you very much for the quick response. I found the video here that explains how to use reference sheet in the formula. I updated on my end and it worked https://www.google.com/search?q=index+and+match+in+smarthsheet&rlz=1C1GCEB_enUS862US862&oq=index+and+match+in+smarthsheet&aqs=chrome..69i57j0i13j0i390l4.4669j0j4&sourceid=chrome&ie=UTF-8#kpvalbx=_TZn6Yr-YCf-kptQPlIaKWA16

=IFERROR(INDEX({reference sheet to Code Number}, MATCH([Code Name]@row, {reference sheet to Code Name}, 0)), "Incorrect Selection")