On 29/07/2007 21:13:58, JLatham wrote:
Look at Excel's Help on VLOOKUP, I think that will explain a lot to you.
For the first part of your problem (missing information from column J) -
Change all references from B$2:I$1501 to B$2:J$1501 so that column J is
included as part of the lookup table. Then add one more
& " " & VLOOKUP(A2,B$2:J$1501,9,0)
right between the last two )) in the existing formula.

Now, we're confusing apples and oranges here or I'm getting confused. You
mention earlier that both column A and C have the =LEFT() formula in
them?? The formula that I posted at the top of this sequence assumes that
the new C helper column DOES NOT EXIST, and that column A has the complete
text from its source in it! The cut-down to the first word/one word only
takes place within the long multiple-VLOOKUP() formula. I suspect that the
3 returns out of 14 you got were from matches to single word entries in
column B: so columns A, B and C all have the same one word in them.

Column A is copied from another worksheet which has this function in A2.
=IF(ISERR(LEFT(A2,FIND(" ",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1))

Column C has this in C2 (which it gets from B2.
=IF(ISERR(LEFT(B2,FIND(" ",B2)-1)),B2,LEFT(B2,FIND(" ",B2)-1))



BASED ON YOUR RECAP, this is what you need at this point:
IF you are still using the one-word entries in A and are still using the
helper column in C, then DO not add another VLOOKUP() to the long
multi-VLOOKUP() formula, simply change all instances of B$2:I$1501 to
become C$2:J$1501 and it should work.

if you want to put the various values from D:J into K:R, then start with
this formula in K2 (assumes helper column C)
=IF(ISNA(VLOOKUP($A2,$C$2:$J$1501,2,0)),"",VLOOKUP ($A2,$C$2:$J$1501,2,0))
then fill it to the right into columns L:R on row 2.
Then starting in L2, change the ,2,0) portion of the formula to ,3,0)
in M2, change ,2,0) to ,4,0)
in N2, change ,2,0) to ,5,0)
see the pattern emerging? If not, ...
in O2, change ,2,0) to ,6,0)
in P2, change ,2,0) to ,7,0)
in Q2, change ,2,0) to ,8,0)
and finally in R2, change ,2,0) to ,9,0)
now you can fill the formulas down as far as you need to.

I really appreciate your help and patience.

I will give this a go in the morning, as it's bedtime in the UK!

This looks like it will work. Can't wait! I'll keep you posted.