Forum Discussion
If then formula help
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.
9 Replies
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.
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.
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?
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.
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)),"")
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.
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?
