Find based on partial text to another tab to get answer?

[sininv]

Hi


Need help I am trying to find the CL in tab 1 to pull out from the table in Tab 2

eg: Senior Vice President in tab 1 - should auto pick up from Tab 2 as CL4

Can please advise the formula. Thank you

 

[Joe4]

Welcome to the Board!

eg: Senior Vice President in tab 1 - should auto pick up from Tab 2 as CL4

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

 

[sininv]

Welcome 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

Hi Joe4

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

Do advise if possible and how to?
=XLOOKUP("*"&cellreference&"*",table position, level column,"",2)


Thank you


I try to use

 

[sininv]

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

 

[Joe4]

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

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:

and use VLOOKUP.

 

[sininv]

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.

Thank Joe.

 

[sininv]

hi

Is there any way to use vlookup to look for approximate match to the text
eg:

title is Senior Associate Director (XXX) in sheet a, how to match first 40 character to pickup from another sheet B with position and level.

As long as there Senior Associate Director match in Sheet B, it will pickup the level ? the three example with Senior Associate Director in the title should pick up as CL2. I have try but cannot pick, it pick the wrong CL.

Sheet a
eg: Title Senior Associate Director (abc)
Senior Associate Director (ZYY)
Senior Associate Director (ppt)

Sheet b

 

[Joe4]

If the extra part always start with "(", we can use a formula to cut off that part of the entry for the sake of the match.

For example, if you have "Senior Associate Director (abc)" in cell A2, then:

=VLOOKUP(LEFT(A2,FIND("(",A2)-2),...)

 

[sininv]

hi Joe,

I not sure how to use the code you provided.
=VLOOKUP(LEFT(A2,FIND("(",A2)-2),...)

can you kindly help. Thank you


I try these method and it didn't give the right answers for most
1) VLOOKUP($E2&"*",worksheetb$A$2:$C$13,2,TRUE) and the answer is not right

2) VLOOKUP(LEFT(B2,FIND("("B2)???

 

[Joe4]

The VLOOKUP formula has 4 arguments (parameters):
=VLOOKUP(lookup value, table array, column index number, approximate match)

Just use the formula I provided in the first argument (lookup value) part of the formula, i.e.

=VLOOKUP(LEFT(B2,FIND("(",B2)-2),worksheetb$A$2:$C$13,2,TRUE)

(I am guessing that "worksheetb" is not the real name of your worksheet, so make sure that you adjust that accordingly.

However, in looking at the sample data you provided in your last post, I don't think that this will work for you in all cases, as you do have entries that do NOT have "(".
And you have some entries that don't look like they have any match at all, no matter what you do, i.e.
- Head, HR
- Head, Marketing

There seems to be no consistency in the data, which makes this task very difficult.
It is hard to program when there are no rules to you can follow.