Forum Discussion
If then formula help
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?
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:
- First place the names into a range;
- Then name the range, 'arrayConstant' (say).
- Using any scratch space, type the formula =arrayConstant
- Select the name within the formula bar and use F9 to evaluate it.
- This gives the correct syntax for the array. Copy it from the formula bar.
- 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.
