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.

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.


"Saxman" wrote:

On 29/07/2007 19:24:14, "Saxman" wrote:

The function at the start of this post does not appear to function
properly. The data (although returned) is incorrect and appears in one
cell, which makes copying difficult.

Sorry a correction. The function below is nearly there.

=IF(ISNA(VLOOKUP(A2,B$2:I$1501,2,0)),"",IF(ISERR(L EFT(A2,FIND("
",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1)) & " " & VLOOKUP(A2,B$2:I$1501,2,0) & ""
& VLOOKUP(A2,B$2:I$1501,3,0) & " " & VLOOKUP(A2,B$2:I$1501,4,0) & " "
&VLOOKUP(A2,B$2:I$1501,5,0) & " " & VLOOKUP(A2,B$2:I$1501,6,0)& " "
&VLOOKUP(A2,B$2:I$1501,7,0)& " " & VLOOKUP(A2,B$2:I$1501,8,0))

As an example I have the following data in these cells:-
A2 Solent
B236 SOLENT
C236 SOLENT (helper, parsed)
D236 22
E236 4
F236 3
G236 2
H236 35127
I236 69401
J236 D

K2 with your function returns (all in K2):-
Solent SOLENT22 4 3 2 35127 69401
The data in J236 is missing. The function needs tweaking to include this.

For some reason, from 14 runners, only three were returned in the K column
and yet all the horses match in both columns A and C exactly?