Partial word lookup_value in VLOOKUP

[BillShut]

I have two sheets, both have a column of names. The second sheet may
not match totally as it has some info added, but both start with the
last name. I want to do a vlookup that uses the first 6 characters in
the name in sheet one against the name list in sheet two and returns a
value two columns over.

Format of names in Sheet1
"Lastname,Firstname"

Format of names in Sheet2
"Lastname, Firstname TopUp" note space between Last and First

I tried
=VLOOKUP(left(a1,6),'Sheet2'!F5:H44,2) #N/A is the result.

What should I use? Thanks very much

Bill

 

[A.W.J. Ales]

Bill,

=INDEX(F5:H44,MATCH(LEFT(A1,6),LEFT((F5:F44),6),0),3)

Formula should be "array enterd" = enter the formula and confirm with
<Cntr> + <Shift> + <Enter>
This places { } around the formula indicating it is an array.
Placing those bracket yourself by typing isn't the same !!

NB In your posting you mentioned a column 2 over but in your formula you
define an area F5:H44 and "pick up" the value from the second column ( thus
column G). I did assume that you actually wanted the value from the third
column (H).
If not replace the 3 in the formula with 2. (You could also change the area
then from F5:H44 in F5:G44).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *