Forum Discussion

Pedro Escorcio's avatar
Pedro Escorcio
Copper Contributor
May 22, 2018

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, cop...
Formulas & Functions
tips and tricks
Pedro Escorcio's avatar
Pedro Escorcio
Copper Contributor
May 23, 2018

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?

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 23, 2018

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:

 

Resources