Forum Discussion
INDEX MATCH #VALUE Error
I am using the INDEX, MATCH combination to find a value, but it returns #VALUE even when I press CTRL+SHIFT+ENTER. I can even open a workbook where the INDEX,MATCH formula has worked in the past, copy it to another cell and make sure the formulae are the same and I get the same #VALUE. Please help.
4 Replies
Can you please provide a non-sensitive example file that demonstrates your situation. Typically it's not necessary to use CTRL + SHIFT + ENTER in order to enter this type of formula. It may help to better understand the data format / data structure in your file. If possible a before and after would be helpful as well. i.e. the result you expect to get when the formula is entered properly.
It appears that Control Shift Enter does not work for any array function. I try a simple one such as
={1,2,3}, Control Shift Enter. One expects the number 1 2 and 3 to be inserted in the columns to the right. This does not happen. Instead a number 1 appears in the selected cell. The formula bar shows
{={1,2,3}}. I am uncertain as to why the CSE combination does not work on any array formula. Any ideas?
Pedro-
Try using this method (See example file for reference). Not sure if this will help with your index/match issue though. It may be necessary to describe your situation in more detail or provide a non-sensitive data file that better shows your data representation and specific scenario:
I've also added the hard coded 1, 2, 3 example to the file:
