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
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]
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
Please send me your e-mail address
So I can send you working example.