Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup / len function combined
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 |
#2
|
|||
|
|||
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 "jamesg-fid" wrote: 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 |
#3
|
|||
|
|||
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 on 1, or 4 words ? "Simon Shaw" wrote: 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 "jamesg-fid" wrote: 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 |
#4
|
|||
|
|||
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. "jamesg-fid" wrote: 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 on 1, or 4 words ? "Simon Shaw" wrote: 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 "jamesg-fid" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
Regarding IF function or vLOOKUP function | Excel Worksheet Functions |