INDEX MATCH #VALUE Error

[Pedro Escorcio]

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.

[#2]

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.

[#3]

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?

[#4]

Pedro,

that is different from you first question.

{1,2,3} is not an array formula but an array constant.

If your goal is to get the three numbers in different cells you have to select the three cells in advance, then type ={1,2,3} and press CSE.

[#5]

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: