How to correct a #NA or #VALUE! error in INDEXMATCH functions

This topic explains the common scenarios where you encounter the #VALUE! error when using INDEX and MATCH functions together in a formula. One of the most common reasons to use the INDEX and MATCH combination is when you want to look up a value in a scenario where VLOOKUP won’t work for you, like if your lookup value is over 255 characters.

Problem: The formula has not been entered as an array
If you are using INDEX as an array formula along with MATCH in order to be able to retrieve a value, you will need to convert your formula into an array formula, otherwise you will see a #VALUE! error. #excel