VLOOKUP

Hi excel experts

Please help with vlookup:

In colA which is my vlookup value, I have a name and surname, there is no space or character seperating them e.g JohnSMITH [so cant do text to columns].
Then I have the table, with peoples names, only this time the format of the names are different. The name has the initials and the surname with spaces eg J Smith.

How can I design a vlookup or work around this problem

Many Thanks

Almir's picture

It is a little bit complicated, but try this:

Supposing:
- your lookup value (JohnSMITH) is in cell A2, and
- all characters in cell are in range of 65-90 in ASCII table
(if there are some other characters in column A, then change range 65:90 in formula accordingly):

=VLOOKUP(

PROPER(LEFT(A2;1)&" "&RIGHT(A2;SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2;CHAR(ROW(INDIRECT("65:90")));"")))-1))

;Lookup_table;column_index;FALSE

Formula is looking for number of uppercase characters in the cell from the right side, and subtract 1 (the first character), than extracts the first character add a single space and adds number of uppercase characters from right.

If you need lookup in other direction (vice versa), it is also possible, but needs a little modified formula.

Not quite there

Hi Almir

Thanks for your reply but it's not working, maybe if it just looked up the surname and I could remove the Initials from the table values [this would be possible]

Almir's picture

No, formula refers to initial, space and surname

No, formula refers to initial, space and surname.
You don't need to remove initials. Formula works fine with me.
Once again: you lookup value in ColA ("JohnSmith") in lookup table with value "J Smith", correct?
I can send you an example file with correct result.
What would you like?

Example file

Yes, please send me file with correct result. Thanks

Almir's picture

Please send me your e-mail address

So I can send you working example.