Hi, I am working with a large Excel table and the data is downloaded from an MS Query. There is a column which brings in employee ID numbers. I have set up an Index/Match formula & associated lookup table which is working perfectly in returning the actual employee names in a different column on the main worksheet; however, when the data filters in, there are cases where there are two Employee ID #s in the same cell separated by a semicolon (i.e., 1, 2, 3, 4;7, 2;3, etc.). This is supposed to be like that.
The array table is set up like this, and the last row shows how I would like the numbers with the semicolon to be returned :
ID# Employee Name
1 EXAMPLE NAME 1
2 EXAMPLE NAME 2
3 EXAMPLE NAME 3
2;3 EXAMPLE NAME 2/
EXAMPLE NAME 3
My formula looks like this:
=INDEX(EMP_NAME,MATCH(LOOKUP_NUMBER,EMP_ID_NUMBER,0)) – this returns the correct results, but of course the cells referencing the items with semicolons returns the N/A error which I expected.
How do I modify the formula so that it recognizes the two numbers and returns the two different names in the same cell? I’ve researched extensively, but can’t seem to grasp it.
Thanks so much.
The array table is set up like this, and the last row shows how I would like the numbers with the semicolon to be returned :
ID# Employee Name
1 EXAMPLE NAME 1
2 EXAMPLE NAME 2
3 EXAMPLE NAME 3
2;3 EXAMPLE NAME 2/
EXAMPLE NAME 3
My formula looks like this:
=INDEX(EMP_NAME,MATCH(LOOKUP_NUMBER,EMP_ID_NUMBER,0)) – this returns the correct results, but of course the cells referencing the items with semicolons returns the N/A error which I expected.
How do I modify the formula so that it recognizes the two numbers and returns the two different names in the same cell? I’ve researched extensively, but can’t seem to grasp it.
Thanks so much.
