Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Copper Contributor

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? 

 

farhanabithi_0-1704871922956.png

 

6 Replies
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 Try setting the optional [match_type] parameter to 0 (Exact match):

 

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

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? 

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?

best response confirmed by farhanabithi (Copper Contributor)
Solution

@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-c5402dce4... 

1 best response

Accepted Solutions
best response confirmed by farhanabithi (Copper Contributor)
Solution

@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-c5402dce4... 

View solution in original post