Excel array formula not working

[lc]

Here's a simplified version:

In first workbook "FIRST", A1:A5=1,2,3,4,5
In second workbook "SECOND", A1:A5= 1,2,3,4,5, B1:B5=A,B,C,D,E

Array formula (located in FIRST) is {=IF(A1='[SECOND.XLS]Sheet
1'!$A$1:$A$5,'[SECOND.XLS]Sheet 1'!$B$1:$B$5)}

Goal: If the value in cell A1 on FIRST matches the value in the range
A1:A5 on SECOND, return the corresponding value in Column B in the
range B1:B5. Repeat for cells A2 through A5 on FIRST.

Problem: The array formula returns the following, only returning one
of the five matches:

FALSE,FALSE,C, FALSE,FALSE

I've verified that each of the values in Column A for FIRST and SECOND
are equal. Why the FALSE non-matches?

 

[Aladin Akyurek]

What is wrong with an ordinary VLOOKUP formula...

=VLOOKUP(A1,'[SECOND.XLS]Sheet 1'!$A$1:$B$5,2,0)