vlookup / len function combined

[Guest]

I have many vlookups referencing cell A1/2/3.....with the text: "firstname".
I now need to add to these cells: "last name".

If I add this text, the vlookups search other data tables for "firstname
lastname" and return errors. How can I amend the referenced cells
(A1/2/3...) so that the vlookups only use "firstname" and disregard the added
text ?

Tks

 

[Guest]

change the vlookup(A1,myTable,Column,range_lookup) to

vlookup(left(A1,find(" ",A1)-1),myTable,...)

assuming you have all cells as

"firstname lastname"

Simon

 

[Guest]

thanks! this works for peoples names.....


Is this adaptable to cells that contain text rather than a persons first and
last names ? For instance, would I be able to tailor this to a cell that
contained a company name (rather than a persons name). So the cell could
already be populated with, say, 3 words and I needed to add on a further 2
words ? or perhaps the company name was 2, or 4 words and I needed to add

 

[Guest]

Yes, this can work for any cell where you want to take the text upto the
first space, however, if you are trying to go for first two or three words it
gets messy, but it is possible.

As a rule, I try to keep individual data fields in separate columns, i.e.
dedicate one columns to firstname, then another column for lastname... this
also helps for various issues like sorting, subtotaling, filtering, etc.