I'm trying to enter a full name (& additional text) in cell based on a formula that tests for the names initials listed in another cell (G4) - number of names in use is somewhat limited so I'm not worried about multiple matches.
Although I searched for something more compact, (e.g. =LOOKUP(A1, {"Cat","Dog","Mouse"}, {"Purrs","Barks","Squeaks"}) but nothing seems to work.
I could use list of names to select from on another worksheet if that would work better then typing them all inside the formula.
The formula below is the closest I came to working. It only works if G4 contains "bs", if it contains any of the other options (jb, mh) I get a #VALUE! error. When I show calculation steps it create the #VALUE! during the SEARCH for the 1st missing item (bs).
=IFS(SEARCH("bs",G4),"Bob Seger",SEARCH("jb",G4),"Jackson Brown",SEARCH("mh",G4),"Music Hall")&" - Phone Consult"
My understanding of the IFS statement is that if it's FALSE it would just move on to the next logical test until one is satisfied...what am I missing?
Bookmarks