Aug 27 2019 07:59 AM
Im trying to build a formula for a very long list of employees in my company.
An example....
The list of their names are on the column H and I want a new column, Colum R, to automatically add their employee number based on a list
Their names repeat very often in column H, so every time their name is added in column H, there should automatically be their employee number in the same row in column R.
Thanks for the help.
Aug 27 2019 08:32 AM
Without knowing about the layout of your sheets and data its hard to suggest a pin point solution but to give you an idea, you may try something like this and tweak it as per your requirement.
Assuming you have a Sheet called List with Emp# and EmpName, try this on Data sheet...
In R2
=IFERROR(INDEX(List!A:A,MATCH(H2,List!B:B,0)),"")
Aug 27 2019 08:44 AM - edited Aug 27 2019 08:47 AM
Thank you, let me be more specific I did not specify properly.
Colum "H" has about 800 rows, but only about 20 different names of employees that repeat often, some more than others.
Colum "R" has nothing.
I want to add a formula to colum R
For example, if "H4" has "JOHN" then "R4" has "2525",
if "H5" has "MARY" then "R5" should have "5151"
So, If I add a new value, in "H891" that is "JOHN" then automatically "R891" Should fill up with "2525"
Hope that explains it better, thank you for the help.
Aug 27 2019 09:09 AM
@ocasiomd wrote:
Thank you, let me be more specific I did not specify properly.
Colum "H" has about 800 rows, but only about 20 different names of employees that repeat often, some more than others.
Colum "R" has nothing.
I want to add a formula to colum R
For example, if "H4" has "JOHN" then "R4" has "2525",
if "H5" has "MARY" then "R5" should have "5151"
So, If I add a new value, in "H891" that is "JOHN" then automatically "R891" Should fill up with "2525"
Hope that explains it better, thank you for the help.
That's not how you should do this. You must be referring to a list somewhere in the file to know what Emp# is to be populated in column R based on EmpName in column H.
Having 20 nested IFs in the formula doesn't make any sense.
Why not upload a truncated version of your file after removing any sensitive info in it so that we can figure out a better way to accomplish what you are trying to achieve?
Aug 27 2019 09:39 AM - edited Aug 27 2019 09:40 AM
How simple the solution is depends very much on the effort you put in to structuring the problem. Here, I have started with @Subodh_Tiwari_sktneer's workbook and inserted some arbitrary names. I converted the lookup range to a Table 'array' and sorted it alphabetically by the first column of employee name.
The lookup formula can then be reduced to
= IFERROR( LOOKUP(EmpName, Array ), "" )
Being a bisection search it is very fast over large datasets.
Aug 27 2019 09:59 AM - edited Aug 27 2019 10:01 AM
If you don't want to see a lookup table, the data can be packaged into an array constant
= {"Berry","E004";"Buckland","E0012";"Butler","E0028";"Campbell","E005";
"Davies","E0013";"Duncan","E0018";"Ellison","E009";
"Gray","E006";"Hemmings","E0025";"Hodges","E0029";
"Ince","E0017";"Kelly","E003";"Kerr","E0024";"King"}
and the formula would still work. This would need to be a pretty short list though.
Aug 27 2019 10:07 AM
Nested If sounds like an excellent solution, but I don't know how to do it. Care to give an example?
lets say
john = 2525
Grace = 3737
Mary = 9898
Names are H column, numbers are the empty column that should fill automatically.
Sorry for not giving a good explanation, the list is confidential I cannot release anything at all about it.
Aug 27 2019 10:44 AM - edited Aug 27 2019 10:46 AM
@ocasiomd wrote:Nested If sounds like an excellent solution, but I don't know how to do it. Care to give an example?
lets say
john = 2525
Grace = 3737
Mary = 9898
Are you serious?
Do you really want to provide a hard coded custom list of 20 names and their corresponding numbers in the formula?
What stops you from making a lookup table as I showed on the List Tab in the file I uploaded?
If you setup a lookup table on a Sheet in your workbook, you can easily refer that list in the formula and the advantage is you can tweak the list at any time and can also hide that tab so that it is not visible to other users.
Then you can have the following formula...
In H4
=IF(H4="","",IFERROR(INDEX(List!A:A,MATCH(H4,List!B:B,0)),""))
and copy it down the rows.
The above formula will return a blank (null string) in the formula cells if column H is empty or the name in column H is not found in the lookup table.
Does that make sense?
Aug 27 2019 11:25 AM - edited Aug 27 2019 11:27 AM
Aug 27 2019 11:25 AM - edited Aug 27 2019 11:27 AM
Like you I have grave reservations about hard-coding a 20 row table (or the equivalent two single column arrays). I would use it for small arrays such as {"distinction";"merit";"pass";"fail"} that are unlikely to change for the lifetime of the workbook. Using a named constant means that there is one point of maintenance despite it being used many times, as would a table.
To built the named constant:
The named array constant can now be used in place of the range in formulas that accept arrays (this excludes SUMIFS, for example, that only works with range references.
Aug 27 2019 11:31 AM
I am not challenging your solution but does this really make any sense to propose this solution to a person who doesn't even know how to construct a nested IF syntax?