Forum Discussion
Solved
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))...
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
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?
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