Excel named range into separate cells

Trying to determine the best way to accomplish the following in an excel 2010 worksheet

I have a employeeid and employee name combination for about 20 Employees

I would like to have the employee name in a data validation

Is  there a way to pick the name and have it go in to say cell A2 and have the id go into cell a3????


I'm open to any option
johnnyg123Asked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
try something like this
=IFERROR( INDEX(Sheet2!$A:$A, MATCH(B2,Sheet2!$B:$B,0)),"")

Open in new window

see attached sample for more info.
28981593.xlsx
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
I read that as you have the ID and name combined in one cell but you want to be able to split them into two cells so that you can use the list of Names as Data Validation source.

What format is the combined data? Id then Name or name then ID? Is there a consistent length to the ID so that we know where to split the string in two?

You can use various text functions to split combined strings into component parts:

=LEFT(String, n)   returns n characters from the left of the string.
If the string is ID then Name and you know the ID is 8 characters, LEFT(String,8) would give the ID only

=RIGHT(String, n)   returns n characters from the right of the string.
If the string is Name then ID and you know the ID is 8 characters, RIGHT(String,8) would give the ID only

=FIND(text value, within search text, start)   returns the position value of the text value within search text using the start point as point zero.
If the string is ID and then Name but the ID is not consistent length but is followed by a space, =FIND(" ",Text,1) would give the position of the space so a combination with LEFT would then give   =LEFT(String,FIND(" ",String,1)-1)   ie give everything up to the first space  (the -1 ensures you don't get the space as well).
0
 
johnnyg123Author Commented:
Thanks for the Posts

The combination of your suggestions led me to my final solution

I ended up separating out name and ID into 2 separate ranges.

One called EmployeeName   and one called EmployeeID

Then created another range that combined the 2 called EmployeeInfo

Used the EmployeeName for the datavalidation to select name

Used the following in the EmployeeID column to get the id to populate

=IFERROR(VLOOKUP(A6,EmployeeInfo,2,FALSE),0)

Thanks Again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.