On 2002-04-04 18:42, dantb wrote:
I Think I just answered my own question. Long day.
=IF(C2>1,VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$D$22216,2,FALSE),0)
it's unclear exactly *which* 12 columns you need returning..... but assuming they are columns "B" through to "M" from your example above, then try this :
=IF(C2>1,VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,2,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,3,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,4,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,5,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,6,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,7,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,8,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,9,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,10,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,11,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,12,FALSE)&" "&VLOOKUP(B1,[Subject.xls]Sheet1!$A$2:$M$22216,13,FALSE),0)
adjust accordingly...
(I'm open to any suggestions as to how this can be shortened..... it seems silly repeating the whole VLOOKUP 12 times, but I couldn't figure out an array solution without any errors messages......
thanks
Chris)
Anyhow,