How is that determined?eg: Senior Vice President in tab 1 - should auto pick up from Tab 2 as CL4
Hi Joe4Welcome to the Board!
How is that determined?
The thing to understand about Excel is that like other programs, it cannot "think" on its own - it can only behave according to the rules that you tell it.
Excel cannot determine on its own that a "Vice President" would fall under "Director and above". You would need to develop a reference/key for it to refer to in order to determine that.
I would recommend setting up a two column lookup table with all your possible Designations, and what CL level that would fall under, and then use a VLOOKUP formula to look those up for each listing you have.
See: 10 VLOOKUP Examples For Beginner & Advanced Users
thank you
how about if the designations column is like that can it use xlookup to fine
any text that find into eg if it's Assistant director (Admin) it will pick up the position under Assistant Director to CL2
View attachment 54297
Do advise if possible and how to?
=XLOOKUP("*"&cellreference&"*",table position, level column,"",2)
to find part of the text to match position text within to pick up back the level. not sure if it's can be done under position there are a range within one cell?
Thank you
I try to use
I don't think that will work, trying to do partial matches on that. For example, if you are looking for Director, you would find it on 4 different lines in your lookup table (since you are only doing partial matches, and not complete matches).thank you
how about if the designations column is like that can it use xlookup to fine
any text that find into eg if it's Assistant director (Admin) it will pick up the position under Assistant Director to CL2
View attachment 54297
Do advise if possible and how to?
=XLOOKUP("*"&cellreference&"*",table position, level column,"",2)
Thank you
I try to use
Thank Joe.I don't think that will work, trying to do partial matches on that. For example, if you are looking for Director, you would find it on 4 different lines in your lookup table (since you are only doing partial matches, and not complete matches).
I would set up the lookup table like this:
View attachment 54317
and use VLOOKUP.
=VLOOKUP(LEFT(A2,FIND("(",A2)-2),...)
=VLOOKUP(LEFT(B2,FIND("(",B2)-2),worksheetb$A$2:$C$13,2,TRUE)