Error in formula vlookup

[bencosmea]

Hi, all
I have a problem with this formula
=VLOOKUP(Q84,LEFT('[book1.xls]sheet1'!$A$12:$Q$36,12),17,FALSE)
The problem is in the left function part, itself gives an error but in
the formula display (where you input the data) when you pres the equal
sign, it recognice the value of the array and also display the result
of the formula but in the cell displays #VALUE!, Is it a bug or
something, how can I get the correct value displayed on the cell.
Please help, thanks.
Audie

 

[Tom Ogilvy]

did you enter it as an array formula (ctrl+Shift+Enter rather than just
Enter)?

 

[Dave Peterson]

I'm wondering if you really want:

=INDEX([book1.xls]Sheet1!$Q$12:$Q$36,
MATCH(Q84,LEFT([book1.xls]Sheet1!$A$12:$A$36,12),0))

(all one cell, and array entered)

To just match the first 12 characters of column A, but return all the characters
in column Q.

Hi, all
I have a problem with this formula
=VLOOKUP(Q84,LEFT('[book1.xls]sheet1'!$A$12:$Q$36,12),17,FALSE)
The problem is in the left function part, itself gives an error but in
the formula display (where you input the data) when you pres the equal
sign, it recognice the value of the array and also display the result
of the formula but in the cell displays #VALUE!, Is it a bug or
something, how can I get the correct value displayed on the cell.
Please help, thanks.
Audie