Hi Tom -
Sorry for the two posts, the first one gave me an error that it did not
post...

I just tried the formula and it worked for cells with more than one line,
but now does not pick up cells with single lines.


"Tom Ogilvy" wrote:

Try:

=if(iserror(INDEX('[spreadsheet
A.xls]Sheet1'!$A:$B,MATCH("*"&N18&"*",'[spreadsheet
A.xls]Sheet1'!$A:$A,0),2)),INDEX('[spreadsheet
A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet
A.xls]Sheet1'!$A:$A,0),2),INDEX('[spreadsheet
A.xls]Sheet1'!$A:$B,MATCH("*"&N18&"*",'[spreadsheet
A.xls]Sheet1'!$A:$A,0),2))

Although, since you looking up on the left column, you could try vlookup
instead.

--
Regards,
Tom Ogilvy




"NewBike" wrote in message
...
Hi all -
I have two spreadsheets that index data from one spreadsheet into another.
Works fine, no problem... HOWEVER now it has become necessary that the
data
in the source file has multiple lines entered in the one cell (using
Alt+Enter)being indexed. This means my formula in the destination file
needs
to "see" each line in the source cell, along with all cells in the column
being indexed.

Example:
forumla in other spreadsheet: =INDEX('[spreadsheet
A.xls]Sheet1'!$A:$B,MATCH(N18,'[spreadsheet A.xls]Sheet1'!$A:$A,0),2)

A B
1 957 20
2 254 30
256
321
3 500 19

If N18 in spreadsheet B equals 256, I should get a result of 30.

Any ideas how I can get this formula to work with the Alt+Enter??