Array

{1,4;2,5}

#NAME? Example Provided Courtesy of: XL-Logic.com

By Peter Eichenberger on Sunday, November 7, 1999 - 09:28 am: Hi Cell A1 in my spreadsheet contains the array {1,4;2,5} If I used the following formula, the value 5 would be returned. =VLOOKUP(2,{1,4;2,5},2,FALSE) However, the formula VLOOKUP(2,a1,2,FALSE) returns #N/A since the array data in cell A1 is regarded as text (I think!). How can I convert the data in cell A1 so that the VLOOKUP command works properly. Regards Peter Eichenberger -------------------------------------------------------------------------------By Anonymous on Sunday, November 7, 1999 - 11:33 am: Isn't your array really in A1:B2? Then, I suspect you want =LOOKUP(2,A1:B2,2,FALSE) -------------------------------------------------------------------------------By Peter Eichenberger on Monday, November 8, 1999 - 02:20 am: No, I would like to type my array data into a single cell. I do not want it to extend across a range of cells. -------------------------------------------------------------------------------By Anonymous on Monday, November 8, 1999 - 10:43 am: The "array" data in A1 isn't array data. Arrays by their nature take up several cells, each component of the array being in a cell. That's why you're getting the error and treatment of data as text. -------------------------------------------------------------------------------By Aaron Blood on Monday, November 8, 1999 - 01:17 pm: Peter, There is a way to accomplish this very easily. Assuming cell A1 contains the text "{1,4;2,5}" Create a range name called "MyArray" (or whatever floats your boat) and in the "Refers To:" box use this expression... =EVALUATE(Sheet1!$A$1) Then change your VLOOKUP function as follows... =VLOOKUP(2,MyArray,2,FALSE) The Evaluate command will convert your cell text to array values for proper treatment in the VLOOKUP function. Aaron :)

Then change your VLOOKUP function as follows... =VLOOKUP(2,MyArray,2,FALSE) The Evaluate command will convert your cell text to array values for proper treatment in the VLOOKUP function. Aaron :)