An effective way to write the formula

[shlomi2020]

Hello,

I'm looking for a more effective way for the formula that i wrote :

=IF(B2= "","No Value",IF(ISNUMBER(SEARCH("*Simon Buskilla*",B2)),VLOOKUP("*Simon Buskilla*",$F$1:$G$4,2,0),IF(ISNUMBER(SEARCH("*Sapir Zur*",B2)),VLOOKUP("*Sapir Zur*",$F$1:$G$4,2,0),IF(ISNUMBER(SEARCH("*Shlomi Ohana*",B2)),VLOOKUP("*Shlomi Ohana*",$F$1:$G$4,2,0)))))

See the attached image.

Now I manually enter the names into the formula, I would like it to be automatic and I will only need to maintain the table = "worker name, Department".

I could only use a Vlookup but I used the formula because when appearing in the cell more than one name, Vlookup not working properly as in the "Business Names" column.

I would appreciate the help, Thanks.

 

[Fluff]

Cross posted Effective way to write the formula

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[shlomi2020]

Sorry I didn't know, thanks

 

[taurean]

If you have Office 365 or Excel 2019 then you can use TEXTJOIN function like below.

=TEXTJOIN(",",TRUE,IF($G$2:$G$4=A2,$F$2:$F$4,""))

 

[pjmorris]

If I've understood what your trying to achieve an easier way, perhaps, would be to use a pivottable. Ensure your data has column headings, create a pivot table and put both the Department and Worker Name as row entries. Set the layout of the Pivottable to Tabular, turn off grand totals and sub totals.