Forum Discussion

farhanabithi's avatar
farhanabithi
Copper Contributor
Jan 10, 2024

Excel INDEX and Match function_gave N/A value once for mismatch but didn't do the same for the other

I want to match two columns and when one cell matches within another column, I want to return the value of that column to a different column.

For this I used, =INDEX($L$2:$L$13,MATCH(A2,$K$2:$K$13)) this formula. It gave #N/A because this cell name was not mentioned in the reference column K. But, it didn't give the same in other cells which was also not found in the reference cell B13, 14, 17. It duplicates the previous cell result. Why is this? And how to solve this problem? 

 

 

  • djclements's avatar
    djclements
    Jan 10, 2024

    farhanabithi Differences in font or other cell formatting shouldn't matter. Also, MATCH is not case-sensitive (ie: "Chandina" = "chandina"). If ALL results are returning #NA, it's likely because one of the lists contains leading or trailing spaces (ie: "Barura" <> "Barura "). You could try using the TRIM function to accommodate this as follows:

     

    =INDEX($L$2:$L$13, MATCH(TRIM(A2), TRIM($K$2:$K$13), 0))

     

    Also, if the data was copied and pasted from another source, it's possible there may be leading or trailing characters that are not visible (ie: carriage return or line feed), in which case the data would need to be cleaned to remove such characters.

     

    Having said that, spelling mistakes need to be corrected in order for this formula to work correctly (ie: change "Debiddar" to "Debidwar"). To prevent spelling mistakes altogether, consider using Data Validation to choose the correct names from a list. For more information, see: https://support.microsoft.com/en-au/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b 

  • djclements's avatar
    djclements
    Bronze Contributor

    farhanabithi Try setting the optional [match_type] parameter to 0 (Exact match):

     

    =INDEX($L$2:$L$13, MATCH(A2, $K$2:$K$13, 0))
    • farhanabithi's avatar
      farhanabithi
      Copper Contributor

      Thank you so much djclements . But the problem is, sometimes we do spelling mistakes. Isn't it? Like before without exact match, it could avoid spelling mistakes and was able to relate data. Now, it shows all N/A. And now I realize, even if the font is not matched, it shows N/A. Is there any other functions to do this task? 

      • djclements's avatar
        djclements
        Bronze Contributor

        farhanabithi Differences in font or other cell formatting shouldn't matter. Also, MATCH is not case-sensitive (ie: "Chandina" = "chandina"). If ALL results are returning #NA, it's likely because one of the lists contains leading or trailing spaces (ie: "Barura" <> "Barura "). You could try using the TRIM function to accommodate this as follows:

         

        =INDEX($L$2:$L$13, MATCH(TRIM(A2), TRIM($K$2:$K$13), 0))

         

        Also, if the data was copied and pasted from another source, it's possible there may be leading or trailing characters that are not visible (ie: carriage return or line feed), in which case the data would need to be cleaned to remove such characters.

         

        Having said that, spelling mistakes need to be corrected in order for this formula to work correctly (ie: change "Debiddar" to "Debidwar"). To prevent spelling mistakes altogether, consider using Data Validation to choose the correct names from a list. For more information, see: https://support.microsoft.com/en-au/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b 

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    The MATCH function has three arguments, the third one being the one you need to study. Set it to 0 to get exact matches and this will solve your issue.
    • farhanabithi's avatar
      farhanabithi
      Copper Contributor

      Thank you Jan. But, I am facing another problem with that. For my spelling mistake, it shows all N/A except one value. Then I realize, it is not spelling mistakes, it is about font!!!  Is there any other option except exact match?

Resources