SOLVED

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

Copper Contributor

# 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?

6 Replies

# Re: Excel INDEX and Match function_gave N/A value once for mismatch but didn't do the same for the o

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.

# Re: Excel INDEX and Match function_gave N/A value once for mismatch but didn't do the same for the o

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

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

# Re: Excel INDEX and Match function_gave N/A value once for mismatch but didn't do the same for the o

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?

# Re: Excel INDEX and Match function_gave N/A value once for mismatch but didn't do the same for the o

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

# Re: Excel INDEX and Match function_gave N/A value once for mismatch but didn't do the same for the o

@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

# Re: Excel INDEX and Match function_gave N/A value once for mismatch but didn't do the same for the o

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