If then formula help

Copper Contributor

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

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)),"")

 

 

@Subodh_Tiwari_sktneer 

 

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 


@ocasiomd wrote:

@Subodh_Tiwari_sktneer 

 

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?

 

 

 

@ocasiomd 

 

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.

@ocasiomd 

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.

@Peter Bartholomew

@Subodh_Tiwari_sktneer

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 


@ocasiomd wrote:

@Peter Bartholomew

@Subodh_Tiwari_sktneer

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?

@Subodh_Tiwari_sktneer 

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.

@ocasiomd 

To built the named constant:

  1. First place the names into a range; 
  2. Then name the range, 'arrayConstant' (say).
  3. Using any scratch space, type the formula =arrayConstant
  4. Select the name within the formula bar and use F9 to evaluate it.
  5. This gives the correct syntax for the array.  Copy it from the formula bar.
  6. Open Name Manager and edit the Name by pasting the array to the 'Refers to' in place of the range reference.

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.

@Peter Bartholomew 

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?